SQL튜닝 입문 - 실행계획, 옵티마이저

1. 실행계획 정의

• 사용자가 SQL을 실행하여 추출하려고 할 때, 옵티마이저가 수립하는 작업 절차

- OPTIMZER: SQL 해석 -> 실행계획 수립 -> 실행

 

2. 실행계획 확인 방법

1) EXPLAIN PLAN

- SQL에 대한 실행계획만 확인 가능

- 명령 사용 시 데이터 처리 X -> 이로 인한 부하가 없음, IO 관련 정보를 확인할 수 없음

EXPLAIN PLAN
SET STATEMENT_ID = 'TEMP1' INTO PLAN_TABLE
FOR
/* SELECT 구문*/
SELECT *
FROM EMP
WHERE DEPT_ID LIKE '%시스템'
;

 

- PLAN_TABLE에 저장된 결과를 확인해야 한다.

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'TEMP1', 'ALL'));

 

2) SET AUTOTRACE

- EXPLAIN PLAN은 쿼리 하나에 대해서만 결과를 도출하므로 이를 보완할 수 있음

- 한 번의 명령으로 여러 개의 SQL에 대한 실행계획을 바로 확인 가능

- 다양한 옵션 > 여러 정보를 선택적 확인 가능

SET AUTOTRACE ON;

 

옵션 종류	설명
EXPLAIN	수행, 결과, 실행계획, IO까지 보여주기
STATISTICS	수행, 결과, 통계정보까지 보여주기
TRACEONLY	수행, 실행계획, 통계정보(데이터가 많을 경우 결과를 생략하기 위해)
TRACEONLY EXPLAIN	실행계획만 추출(큰 데이터일 경우 이 옵션을 사용)
TRACEONLY STATISTICS	통계정보만 추출, 실행계획 X
OFF	기능 끄기

 

---

3. 옵티마이저 정의

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

4.  종류

1) RBO(Rule Based Optimizer)

- 기본적으로 15개의 순위가 매겨진 규칙 존재

- SQL에 대한 실행계획이 하나 이상일 경우, 높은 순위 규칙 이용

- 예측가능한 실행계획이므로, 개발자가 원하는 경로로 유도하기 쉬움

순위	Access Path
1	Rowid에 의한 1 row
2	클러스터 조인에 의한 1 row
3	Unique나 Primary Key를 사용한 해시 클러스터 키에 의한 1 row
4	Unique나 Primay Key에 의한 1 row
5	클러스터 조인
6	해시 클러스터 키
7	클러스터 키
8	결합 칼럼 인덱스
9	단일 칼럼 인덱스
10	인덱스에 의한 유한 영역 검색(equal, between 같은)
11	인덱스에 의한 무한 영역 검색
12	소트 머지 조인
13	인덱스로 구성된 칼럼의 최대 or 최소
14	인덱스로 구성된 칼럼으로 order by
15	인덱스 없이 전체 테이블 스캔(full)

 

- 내식대로 정의한 RBO: WHERE절 조건을 먼저 보고 시작하는 옵티마이저

 

2) CBO(Cost Based Optimizer)

- 대상의 row들을 처리하는데 필요한 자원 사용을 최소화해서 빠르게 데이터 처리 목적

- 비용 산정 요소: 각종 통계 정보, hint, 연산자, Index, cluster, dbms버전, cpu/memory용량, disk i/o

- 주기적인 통계 정보 생성

    * 정기적인 ANALYZE 작업 필요

    * 가장 효율적인 실행계획을 수립하기 위해 최소 비용 계산할 때 사용

ANALYZE TABLE emp COMPUTE STATISTICS;

ANALYZE TABLE emp COMPUTE STATISTICS
					SAMPLE 20 PERCENT; -- 표본 크기 제어

ANALYZE TABLE emp COMPUTE STATISTICS
					SAMPLE 10 ROWS; -- 표본 크기 제어

 

- ANALYZE 실행 여부 확인

SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TABLE
WHERE TABLE_NAME IN ('EMP', 'DEPT');

 

- DBMS_STATS PACKAGE 내의 프로시저

--DBMS_STATS.GATHER_TABLE_STATS(TABLE OWNER, TABLE NAME, 파티션, 샘플 크기, FALSE, 'FOR ALL COLUMNS', 병렬 처리)
DBMS_STATS.GATHER_TABLE_STATS('ADMIN', 'EMP', NULL, 20, FALSE, 'FOR ALL COLUMNS' 4)

-- 유저가 가진 모든 테이블 조회
DBMS_STATS.GATHER_SCHEMA_STATS('ADMIN');

-- 모든 OWNER 대상 조회
DMBS_STATS.GATHER_DATABASE_STATS;

 

- 내식대로 정의한 CBO: 조인이 있을 경우, 더 적은 결과를 도출해서 시작할 수 있는 것으로 적용하는 OPTIMIZER

5. OPTIMZER 레벨별 설정

- Instance level: initSID.ora 이용 설정

OPTIMZER_MODE = [RULE/CHOOSE/FIRST_ROWS/ALL_ROWS]

 

- Session Level

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

 

- Statement Level

SELECT /*+ first_rows */ 칼럼명
FROM TALBE명;

 

출처: 멀티캠퍼스 '업무에 바로 쓰는 SQL 튜닝 입문'