[친절한 SQL 튜닝] 6. DML 튜닝 [1]

기본 DML 튜닝

DML 성능에 영향을 미치는 요소

• 인덱스
• 무결성 제약
• 조건절
• 서브쿼리
• Redo 로깅
• Undo 로깅
• Lock
• 커밋

 

데이터베이스 Call과 성능

• Parse Call : SQL 파싱과 최적화를 수행하는 단계
• Execute Call : SQL을 실행하는 단계
• Fetch Call : 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정으로 SELECT문에서만 나타난다.
• User Call : 네트워크를 경유해 DBMS 외부로부터 인입되는 Call
• Recursive Call : DBMS 내부에서 발생하는 Call. 사용자 정의 함수/프로시저/트리거에 내장된 SQL을 실행할 때 발생한다.

 

데이터 Call이 많으면 성능은 느릴 수밖에 없다. 특히, 네트워크를 경유하는 User Call이 성능에 미치는 영향은 매우 크다.

 

 

인덱스 및 제약 해제를 통한 대량 DML 튜닝

인덱스와 무결성 제약 조건은 DML 성능에 큰 영향을 끼친다. OLTP 시스템에서 이 기능들을 해제할 순 없다. 반면, 동시 트랜잭션 없이 대량 데이터를 적재하는 배치 프로그램에서는 이들 기능을 해제함으로써 큰 성능개선 효과를 얻을 수 있다.

 

 

Direct Path I/O 활용

오라클은 버퍼캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다.

 

• 기능이 작동하는 경우
  1. 병렬 쿼리로 Full Scan을 수행할 때
  2. 병렬 DML을 수행할 때
  3. Direct Path Insert를 수행할 때
  4. Temp 세그먼트 블록들을 읽고 쓸 때
  5. direct 옵션을 지정하고 export를 수행할 때
  6. nocache 옵션을 지정한 LOB 컬럼을 읽을 때

Direct Path Insert

• 일반적인 INSERT가 느린 이유
  1. 데이터를 입력할 수 있는 블록을 Freelist에서 찾는다. 테이블 HWM(High-Water-Mark) 아래쪽에 있는 블록 중 데이터 입력이 가능한(여유 공간이 있는) 블록을 목록으로 관리하는데 이를 'Freelist'라고 한다.
  2. Freelist에서 할당받은 블록을 버퍼캐시에서 찾는다.
  3. 버퍼캐시에 없으면, 데이터파일에서 읽어 버퍼캐시에 적재한다.
  4. INSERT 내용을 Undo 세그먼트에 기록한다.
  5. INSERT  내용을 Redo 로그에 기록한다.

* Direct Path Insert 방식을 사용하면, 대량 데이터를 일반적인 INSERT보다 훨씬 더 빠르게 입력할 수 있다.

• INSERT .... SELECT문에 append 힌트 사용
• parallel 힌트를 이용해 병렬 모드로 INSERT
• direct 옵션을 지정하고 SQL*Loader로 데이터 적재
• CTAS(Create Table ... as Select)문 수행

 

• Direct Path Insert 방식이 빠른 이유
  1. Freelist를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력한다.
  2. 블록을 버퍼캐시에서 탐색하지 않는다.
  3. 버퍼캐시에 적재하지 않고, 데이터파일에 직접 기록한다.
  4. Undo 로깅을 안 한다.
  5. Redo 로깅을 안 하게 할 수 있다.(Alter Table t NOLOGGING;)

 

 

파티션을 활용한 DML 튜닝

파티션을 이용하면 대량 추가/변경/삭제 작업을 빠르게 처리할 수 있다.

 

테이블 파티션

파티셔닝(Partitioning)은 테이블 또는 인덱스 데이터를 특정 컬럼(파티션 키) 값에 따라 별로 세그먼트에 나눠서 저장하는 것을 말한다.

 

• 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 > 가용성 향상
• 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산

 

인덱스 파티션

• 로컬 파티션 인덱스(Local Patitioned Index) : 각 테이블 파티션과 인덱스 파티션이 서로 1:1 대응 관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스
• 글로벌 파티션 인덱스(Global Partitioned Index) : 파티션을 테이블과 다르게 구성한 인덱스(파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른 경우)
• 비파티션 인덱스(Non-Partitioned Index) : 파티셔닝하지 않은 인덱스

 

• Prefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치
• NonPrefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치하지 않는다. 파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 속한다.