[SQL 성능 분석하기] 옵티마이저

옵티마이저

 

1. 옵티마이저의 개념

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

 

2. 옵티마이저의 종류

 

오라클: RBO/CBO (다른 DBMS는 CBO만 존재)

 

1) RBO(Rule Based Optimizer)

: 15개의 순위가 매겨진 규칙 존재

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

: 수립된 실행계획이 예측 가능하여 개발자가 원하는 처리 경로로 유도하기 쉬움

 

2) CBO(Cost Based Optimizer)

자원사용을 최소화하여 데이터를 빨리 처리하는 데 목적

 

통계정보

: CBO의 성능을 최적의 상태로 유지시키기 위해서 테이블, 인덱스, 클러스터 등을 대상으로 통계정보를 생성함. (정기적으로 ANALYZE작업을 하는 것이 중요)

 

ANALYZE 실행

ANALYZE TABLE emp COMPUTE STATISTICS; 
ANALYZE TABLE emp COMPUTE STATISTICS SAMPLE 10 PERCENT; 
ANALYZE TABLE emp COMPUTE STATISTICS SAMPLE 5 ROW;

 

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 // 병렬처리 프로세스 수)

: ANALYZE로는 힘든 병렬처리가 가능

 

3. 옵티마이저의 레벨별 설정

 

Instance Level

: initSID.ora를 이용해 지정함

OPTIMIZER_MODE = [RULE/CHOOSE/FIRST_ROWS/ALL_ROWS] //v.10부터는 ALL ROWS가 기본

Session Level

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

Statement Level

:SQL문에 힌트로 사용

SQL> SELECT /*+first_rows*/
		ename
     FROM emp;

 

> 우선순위가 높은건 SQL이 갖는 힌트

 

4. RBO와 CBO의 실행계획 비교

CBO

---------------------------------------------------------------------------------------------------
|	id| Operation							| NAME		| ROWS	  |
--------------------------------------------------------------------------------------------------
|	0 | SELECT STATEMENT						|		|	3 |
|	1 |		NESTED LOOPS					|		|	3 |
|	2 | 		TABLE ACCESS BY INDEX ROWID			| DEPT		|	1 |
|*	3 | 			INDEX UNIQUE SCAN			| PK_DEPT	|	1 |
|*	4 | 		TABLE ACCESS FULL				| EMP		|	3 |
---------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
--------------------------------------------------------------------------
|
	3 - access("D"."DEPTNO" = 10)
	4 - filter("E"."DEPTNO" = 10)

 

RBO

---------------------------------------------------------------------------------------------------
|	id| Operation							| NAME		|
--------------------------------------------------------------------------------------------------
|	0 | SELECT STATEMENT						|		|
|	1 |		NESTED LOOPS					|		|
|	2 |			NESTED LOOPS				|		|
|*	3 | 			TABLE ACCESS FULL			| EMP		|
|*	4 | 			INDEX UNIQUE SCAN			| PK_DEPT	|
|	5 | 		TABLE ACCESS BY INDEX ROWID			| DEPT		|


---------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
--------------------------------------------------------------------------
|
	3 - access("E"."DEPTNO" = 10)
	4 - filter("E"."DEPTNO" = "D"."DEPTNO")
    
 NOTE
 -----
 	- rule based optimizer used (consider using cbo)