[SQL튜닝] 옵티마이저

[SQL튜닝] 옵티마이저

---

주요내용

• 옵티마이저의 개념
• 옵티마이저의 종류
• 옵티마이저의 레벨별 설정
• RBO와 CBO의 실행계획 비교

학습목표

• 옵티마이저 종류에 따라 달라지는 데이터베이스 성능을 모니터링할 수 있다.
• CBO, RBO의 실행계획 확인을 통해 SQL 문장에 적합한 옵티마이저를 선택할 수 있다.

---

옵티마이저의 개념

• 옵티마이저 : 사용자가 실행한 SQL을 해석하고, 데이터 추출을 위한 실행계획을 수립하는 프로세스

---

옵티마이저의 종류

RBO(Rule Based Optimizer) : 초창기 버전부터 제공함

• 기본적으로 15개의 순위가 매겨진 규칙이 있음 -> 이를 기초로 해서 실행계획을 수립함
• SQL에 대한 실행계획이 하나 이상일 경우, 순위가 높은 규칙을 이용하게 됨
• 수립될 실행계획이 예측 가능하기 때문에 개발자가 원하는 처리 경로로 유도하기 쉬움
• 1순위 : Rowid에 의한 1 row
• 2순위 : 클러스터 조인에 의한 1 row
• 3순위 : Unique나 PK를 사용한 해시 클러스터 키에 의한 1 row
• 4순위 : Unique나 PK에 의한 1 row
• 5순위 : 클러스터 조인
• 6순위 : 해시 클러스터 키
• 7순위 : 클러스터 키
• 8순위 : 결합 칼럼(Composite) 인덱스
• 9순위 : 단일 칼럼(Single) 인덱스
• 10순위 : 인덱스에 의한 유한 영역 검색(Bounded range search on indexed columns)
• 11순위 : 인덱스에 의한 무한 영역 검색(Unbounded range search on indexed columns)
• 12순위 : 소트 머지(Sort Merge) 조인
• 13순위 : 인덱스로 구성된 칼럼의 최대 또는 최소(MAX or MIN of indexed columns)
• 14순위 : 인덱스로 구성된 칼럼으로 ORDER BY(ORDER BY on indexed columns)
• 15순위 : 인덱스 없이 전체 테이블 스캔(FTS:Full Table Scan)

CBO(Cost Based Optimizer) : v10g부터 기본적인 설정으로 적용되고 있음

• 대상 row들을 처리하는데 필요한 자원 사용을 최소화해서, 궁극적으로 데이터를 빨리 처리하는 데 목적이 있음
• CBO에 영향을 미치는 비용 산정 요소 : 각종 통계정보, Hint, 연산자, Index, Cluster, DBMS버전, CPU/Memory용량, Disk I/O 등과 같이 매우 다양함
• CBO의 통계정보
  • 통계정보 : CBO의 성능을 최적의 상태로 유지시키기 위해서 테이블, 인덱스, 클러스터 등을 대상으로 통계 정보를 생성함 -> 정기적으로 analyze 작업을 하는 것이 가장 중요함
  • 가장 효율적인 실행계획을 수립하기 위해 최소비용을 계산할 때 중요하게 사용됨
• ANALYZE 실행

  ANALYZE TABLE emp COMPUTE STATISTICS;
  ANALYZE TABLE emp ESTIMATE STATISTICS
                 SAMPLE 10 PERCENT;
  ANALYZE TABLE emp ESTIMATE STATISTICS
                 SAMPLE 5 ROWS;

• ANALYZE 실행 여부 확인

  SELECT table_name, num_rows, last_analyzed
  FROM user_tables
  WHERE table_name IN ('EMP', 'DEPT');

• DBMS_STATS Package

  DBMS_STATS.GATHER_TABLE_STATS('hr', 'EMP', NULL, 20, FALSE, 'FOR ALL COLUMNS', 4)
  DBMS_STATS.GATHER_TABLE_STATS('hr', 'EMP', NULL, NULL, FALSE, 'FOR ALL COLUMNS', 4)
  DBMS_STATS.GATHER_SCHEMA_STATS('hr')
  DBMS_STATS.GATHER_DATABASE_STATS

---

옵티마이저의 레벨별 설정

• Instance Level : initSID.ora를 이용해서 지정함

  OPTIMIZER_MODE = [RULE/CHOOSE/FIRST_ROWS/ALL_ROWS]

• Session Level

  SQL> ALTER SESSION SET OPTIMIZER_MODE = [RULE/CHOOSE/FIRST_ROWS/ALL_ROWS]

• Statement Level

  SQL> SELECT ename FROM emp;

---

RBO와 CBO의 실행계획 비교

• 동일 SQL에 대해서 각 옵티마이저가 수립한 실행계획은 서로 다를 수 있음
• 이는 SQL의 퍼포먼스가 옵티마이저에 따라 다르다는 의미임
• 옵티마이저의 종류에 따라 달라지는 DB성능의 차이점을 이해하는 것이 중요함

  SELECT e.ename, d.dname
  FROM emp e, dept d
  WHERE e.deptno = d.deptno
  AND e.deptno = 10;

  

---

예제

-- 인덱스
EC_TASK_PK : COURSE_CODE+TASK_NO
EC_TASK_TERM_IDX00 : COURSE_CODE+TASK_NO+YEAR+COURSE_SQ_NO

SELECT A.COURSE_CODE, A.TASK_NO, A.BBS_YN, A.UPDATE_DATE, B.YEAR, B.S_DATE, B.E_DATE
FROM EC_TASK A, EC_TASK_TERM B
WHERE A.COURSE_CODE = B.COURSE_CODE
  AND A.TASK_NO = B.TASK_NO
  AND B.COURSE_CODE = 36
  AND B.TASK_NO = 1
  AND B.COURSE_SQ_NO = 1;

수정문 : RBO, CBO의 실행계획이 같도록 변경

SELECT A.COURSE_CODE, A.TASK_NO, A.BBS_YN, A.UPDATE_DATE, B.YEAR, B.S_DATE, B.E_DATE
FROM EC_TASK A, EC_TASK_TERM B
WHERE A.COURSE_CODE = B.COURSE_CODE
  AND A.TASK_NO = B.TASK_NO
  AND A.COURSE_CODE = 36
  AND A.TASK_NO = 1
  AND B.COURSE_SQ_NO = 1;