업무에 바로 쓰는 SQL튜닝 입문 - 2교시(옵티마이저)

옵티마이저

옵티마이저란 사용자가 실행한 SQL을 해석하고 데이터 추출을 위한 실행계획을 수립하는 Process로써
성능에 큰 영향을 미친다.

 

옵티마이저는 2가지로 분류됨.

1. RBO (Rule Based Optimizer) 규칙 기반 옵티마이저

-  기 수립된 특정 규칙(15개의 기본 규칙)의 Ranking을 매겨 순서대로 실행계획을 수립한다.

- 과거 비용을 예측하는 기능의 신뢰도가 높지 않았을 때 사용한 기술이라고 할 수 있을 듯.

- 힌트를 사용할 수 없음

- HASH JOIN을 사용할 수 없음.

 

2. CBO (Cost Based Optimizer) 비용 기반 옵티마이저 : 비용 = 시간

- 대상 Row들을 처리하는데 필요한 자원 사용을 최소화 해서 궁극적으로 데이터를 빨리 처리하는데 목적이 있음.

- CBO에 영향을 미치는 비용 산정 요소는 다음과 같다.

(각종 통계 정보, Hint, 연산자, Index, Cluster, DBMS버전, CPU/Memory 용량, Disk I/O 등)

 

통계정보

CBO의 성능을 최적의 상태로 유지시키기 위해서 테이블, 인덱스, 클러스터 등을 대상으로 통계 정보를 생성함.

정기적으로 ANALYZE 작업을 하는 것이 가장 중요함.

ANALYZE 실행 예시/ 실행 여부 확인

ANALYZE TABLE emp COMPUTE STATISTICS;

SELECT table_name, num_rows, last_analyzed
  FROM user_tables
 WHERE table_name IN ();
 
 -- 결과 예시
 -- table_name  num_rows  last_analyzed
 -- DEPT               4  22/09/25

통계정보 패키지 - 통계정보 갱신에 쓰는 오라클 패키지

DBMS_STATS.GATHER_TABLE_STATS();

DBMS_STATS.GATHER_SCHEMA_STATS('hr'); -- hr 유저의 모든 테이블을 다!

DBMS_STATS.GATHER_DATABASE_STATS -- 모든 유저 테이블 다 !

- 병렬처리 가능!

 

옵티마이저의 레벨별 설정

3가지 중 hint가 가장 우선순위가 높음!!

 

RBO와 CBO의 실행계획 비교

SELECT E.ENAME, D.DNAME
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND E.DEPTNO = 10;

-- RBO : WHERE 절에 E.DEPTNO = 10이 있으므로 EMP E 테이블을 먼저 NESTED LOOPS함(먼저 조인함)
-- CBO : DEPT D가 데이터가 적으므로 DEPT D를 먼저 조인함