튜닝기본 - 옵티마이저 2 (optimizer) 요약정리

옵티마이저(Optimizer)

SQL에 대해 해석(parse)을 하고 데이터를 처리하기 위해 작업

방법을 수립하는 process이다.

* Optimizer 종류 (Rule Base Optimizer - RBO

Cost Base Optimizer - CBO)

★★★★★

RBO와 CBO 간에 차이점이 많이 있음!

㈜ Sybase, IBM DB2, MS SQL*Server, My SQL, INFORMIX, TIBERA

~ 이러한 제품들은 Cost base 옵티마이저만 지원함.

1) RBO (Rule Base Optimizer)

Optimizer의 판단은 정해진 규칙(rule)에 근간을 둠

- 가능한 한 주어진 조건에 의해 인덱스를 사용할수만 있다면

무조건 인덱스를 사용하려는 특성이 있다.

○ 각 규칙(Rule) 간에 우선순위가 존재함 - [표 1-2-1]

- Rank 1 vs. Rank 4

SELECT * FROM dept

WHERE rowid='AAAGUJAAJAAABuKAAD'

AND deptno = 40;

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 TABLE ACCESS (BY USER ROWID) OF 'DEPT'

SELECT * FROM dept

WHERE rowid LIKE 'AAAGUJAAJAA%'

AND deptno = 40;

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

2 1 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

SELECT * FROM dept

WHERE rowid LIKE 'AAAGUJAAJAA%'

AND deptno BETWEEN 10 and 30;

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

2 1 INDEX (RANGE SCAN) OF 'PK_DEPT' (UNIQUE)

- Rank 8 vs. Rank 9

ex) create index job_deptno_index on emp(job, deptno);

create index job_index on emp(job);

SELECT * FROM emp

WHERE job = 'CLERK' AND deptno = 30;

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'JOB_DEPTNO_INDEX' (NON-UNIQUE)

SELECT * FROM emp

WHERE job = 'CLERK' AND deptno >= 30;

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'JOB_INDEX' (NON-UNIQUE)

SELECT * FROM emp

WHERE job = 'CLERK' AND deptno between 10 and 30;

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'JOB_INDEX' (NON-UNIQUE)

SELECT * FROM emp

WHERE job LIKE 'CL%' AND deptno >= 30;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'JOB_DEPTNO_INDEX' (NON-UNIQUE)

→ column 개수가 많은 것을 선택

- Rank 10 vs. Rank 11

ex) create index dept_index on emp (deptno);

create index job_index on emp (job);

SELECT * FROM emp

WHERE job LIKE 'C%'

AND deptno >= 30;

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'JOB_INDEX' (NON-UNIQUE)

SELECT * FROM emp

WHERE job LIKE 'C%'

AND deptno BETWEEN 10 AND 40;

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'JOB_INDEX' (NON-UNIQUE)

→ 나중에 생성된 인덱스를 선택

SELECT * FROM emp

WHERE job = 'CLERK'

AND deptno = 30;

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 AND-EQUAL

3 2 INDEX (RANGE SCAN) OF 'JOB_INDEX' (NON-UNIQUE)

4 2 INDEX (RANGE SCAN) OF 'DEPT_INDEX' (NON-UNIQUE)

→ 모두 사용함

2) CBO (Cost Base Optimizer)

Optimizer의 판단에 의해 산정한 비용이 최소인 것을 채택함.

비용 산정시 이용되는 정보 중 Objects에 대한 ANALYZE 정보가

매우 큰 비중을 차지함.

정기적으로 통계정보를 생성하는 것이 매우 중요함

-- 참고

* MySQL의 경우는 오라클의 명령과 동일함.

* MS SQL*Server

UPDATE STATISTICS EC_PROGRESS;

* IBM DB2 :

RUNSTATS ON TABLE EC_PROGRESS

WITH DISTRIBUTION AND DETAILED INDEXES ALL;

* Sybase :

UPDATE STATISTICS EC_PROGRESS:

UPDATE STATISTICSEC_PROGRESS(COURSE_CODE);

UPDATE STATISTICSEC_PROGRESS(COURSE_CODE) WITH SAMPLE 5 PERCENT;

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

ANALYZE TABLE table명 COMPUTE[/ESTIMATE/DELETE] STATISTICS;

ANALYZE INDEX index명 COMPUTE[/ESTIMATE/DELETE] STATISTICS;

ANALYZE CLUSTER cluster명 COMPUTE[/ESTIMATE/DELETE] STATISTICS;

* COMPUTE STATISTICS 옵션

- 테이블이 갖고 있는 전체 데이터를 대상으로 통계정보를

생성하는 옵션임. 작업시 많은 시간이 소요되며, 또한

데이터베이스에 부하를 많이 줄 수 있음

SQL> ANALYZE TABLE EC_APPLY COMPUTE STATISTICS;

* ESTIMATE STATISTICS 옵션 사용시 :

(예) ANALYZE TABLE APPLY ESTIMATE STATISTICS

SAMPLE 10000 ROWS;

ANALYZE TABLE APPLY ESTIMATE STATISTICS

SAMPLE 5 PERCENT;

* ESTIMATE STATISTICS 옵션은 주로 데이터 건수가 많은 테이블에

대해 통계정보 생성시 사용한다.

* 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;

OPTIMIZER 지정

==============

* OPTIMIZER_MODE parameter를 사용해서 지정할 수 있음.

- CHOOSE : ANALYZE에 의한 통계정보의 유무(有無)에 따라

통계정보가 있으면 CBO(Cost Base Optimizer)

통계정보가 없으면 RBO(Rule Base Optimizer)

- RULE : RBO(Rule Base Optimizer)

* ANALYZE에 의한 통계정보 생성이 무의미

- FIRST_ROWS/ALL_ROWS : CBO(Cost Base Optimizer)

* FIRST_ROWS - 주로 OLTP성 시스템에 적용

~ 세분화해서 값을 사용할 수 있게 됨.

FIRST_ROWS_1, FIRST_ROWS_10,

FIRST_ROWS_100, FIRST_ROWS_1000

* ALL_ROWS - 주로 Batch성 시스템에 적용

* 레벨별 설정에서의 우선 순위

데이터베이스 레벨 < 세션 레벨 < 명령문 레벨

- 세션 레벨로 옵티마이저를 변경하려면,

SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

- 명령문 레벨로 옵티마이저를 변경하려면,

SQL> SELECT /*+ ALL_ROWS */

ENAME, JOB, SAL

FROM EMP

WHERE EMPNO > 0;

★★★★★

옵티마이저 모드에 따른 SQL분석의 차이

SELECT A.ENAME, B.DNAME

FROM EMP A, DEPT B

WHERE A.DEPTNO = B.DEPTNO

AND A.DEPTNO = 10;

Execution Plan

---------------------------------------------------------- 0

0 SELECT STATEMENT Optimizer=RULE |

1 0 NESTED LOOPS 1

2 1 TABLE ACCESS (FULL) OF 'EMP' (A) / \

3 2 INDEX (RANGE SCAN) OF 'DEPT_INDEX' (NON-UNIQUE) 2 4

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (B) | |

5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 3 5

Execution Plan

---------------------------------------------------------- 0

0 SELECT STATEMENT Optimizer=FIRST_ROWS |

1 0 NESTED LOOPS 1

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (B) / \

3 2 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 2 4

4 1 TABLE ACCESS (FULL) OF 'EMP' (A) | |

5 4 INDEX (RANGE SCAN) OF 'DEPT_INDEX' (NON-UNIQUE) 3 5

Cost base인 경우, 위의 sql문을 다음과 같이 변경해서 실행하게 된다.

SELECT A.ENAME, B.DNAME

FROM EMP A, DEPT B

WHERE A.DEPTNO = B.DEPTNO

AND A.DEPTNO = 10

AND B.DEPTNO = 10; <------- 자동 인식!!!