6장 소트 튜닝

6장 소트 튜닝

6.1 기본 DML 튜닝

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

인덱스

인덱스가 늘어나면 Insert / Delete / Update 시 부하가 늘어난다. 특히 Update 시에는 변경과 인덱스 수조 내에서 정렬을 다시해야 하는 이중적인 부하가 발생한다.

무결성 제약

무결성 제약 중 PK/FK 제약은 Check / Not Null 보다 성능에 더 큰 영향이 있다. 실제 데이터를 조회하기 때문이다.

조건절

2/3장에서 학습한 인덱스 튜닝 원리 참고.

서브쿼리

4장에서 학습한 조인 튜닝 원리 참고.

Redo 로깅

DML 수행 시마다 Redo 로그 생성하기 때문에 DML에 성능에 영향을 끼친다.

Undo 로깅

DML 수행 시마다 Undo 로그 생성하기 때문에 DML에 성능에 영향을 끼친다.

Lock

Lock은 DML 성능에 직접적인 영향을 끼친다. 성능과 데이터품질은 트레이드오프 관계.

커밋

• DB 버퍼캐시
  • 서버 프로세스는 버퍼캐시를 통해 데이터를 일괄 처리한다.
• Redo 로그버퍼
  • Redo 성능을 위해 Redo 파일 중간에 버퍼 위치.
• 트랜잭션 데이터 저장 과정
  • DML 문을 실행하면 Redo 로그버퍼에 변경사항 기록
  • 버퍼블록에서 데이터 변경
  • 커밋
  • Redo 로그버퍼 파일에 일괄 저장
  • 버퍼블록을 데이터파일에 일괄 저장
• 커밋
  • 커밋을 오래동안 안하는 것도 안좋지만 너무 자주하는 것도 IO를 발생시키기 때문에 좋지 않다

데이터베이스 Call과 성능

• SQL Call 세 단계

  • Parse call: SQL 파싱과 최적화 수행
  • Execute call: SQL 실행
  • Fetch call: 데이터를 읽어서 사용자에게 전송

• 발생 시점에 따른 Call 분류

  • User call: DBMS 외부로부터 인입되는 call
  • Recursive call: DBMS 내부에서 발생하는 call

• 절차적 루프 처리

  • 절차적으로 반복되는 call일 경우 그나마 recursive 콜일 경우는 낫다. user call은 느려질 수밖에 없다.

• One SQL의 중요성

  • 로직이 복잡하지 않으면 되도록 하나의 SQL로 끝나는 게 성능에 좋다.

Array Processing 활용

복잡한 업무 로직을 One SQL로 구현하는 것은 어렵다. 이럴 때 Array Processing을 활용한다.

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

동시 트랜잭션이 없는 배치 환경에서는 PK 및 인덱스 제약 헤제를 통하여 성능을 향상 시킬 수 있다.

수정가능 조인 뷰

인라인 뷰가 포함된 update 쿼리문은 튜닝을 통하여 속도 향상이 가능하다.

Merge 문 활용

데이터웨어하우스에서는 두 시스템 간 데이터 동기화가 가장 흔히 발생하는 작업이다.
이때 오라클9i에서 도입된 MERGE 문을 활용할 수 있다.

6.2 Direct Path I/O

정보계 시스템(DW/OLAP)이나 배치작업에서는 버퍼캐시 경유하지 않는 것이 더 이득이다. 버퍼캐시를 경유하지 않고 바로 데이터 블록을 읽고 쓰는 것을 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 작업 시 Direct Path Insert 를 사용하면 아래와 같은 이점이 있다

• Freelist를 참조하지 않는다
• 블록을 버퍼캐시에서 탐색하지 않는다
• 버퍼캐시를 사용하지 않는다.
• Undo 로깅을 안한다
• Redo 로깅을 안하게 할 수 있다.

병렬 DML

UPDATE / DELETE 는 기본적으로 Direct Path Write가 불가능하기 때문에 병렬 DML 로 유도한다.