|
☞ 개요 |
| |
1. 종류
Rule-based - SQL에 대한 Execution Plan이 여러 개 있다고 할 때, 가장 낮은 순위의 실행계획을
항상 사용
ost-based - SQL에 대한 Execution Plan이 여러 개 있다고 할 때, 가장 cost가 적은 실행계획을
선택 |
|
2. Optimizer Mode
o Rule-Based Optimizer : 하나의 SQL에 대한 여러 개의 Execution Plan 가운데 가장 낮은 순위의
실행계획을 사용Cost-Based Optimizer : 하나의 SQL에 대한 여러 개의
Execution Plan 가운데 가장 cost가 적은 실행계획을 선택
o 실행환경CHOOSE : Optimizer가 CBO와 RBO 두 가지가 모두 선택 가능할 경우에 사용
- ALL_ROWS : CBO 환경경에서 전체적인 처리, 즉 Batch 환경에서 주로 사용
- FIRST_ROWS : CBO 환경에서 빠른 응답시간을 얻고자 할 때, 즉 ON-LINE 환경에서 주로 사용
- RULE : RBO 환경에서 사용 |
|
☞ 설명 |
| |
⊙ Rule-Based Optimizer (규칙기반 최적화)RBO는 규칙에 의해설 실행되므로 예측가능하고 통제
가능하므로 대부분의 DBA, 프로그래머들이 선호
- RBO는 구문 중심으로, SQL 구문을 변경할 경우 성능이 향상될 수 있음(통계정보, 비용계산
비사용). 오직 규칙에 의해서만 실행계획을 결정
- 테이블의 row수가 작어나 query가 일반적으로 row의 대부분을 반활할 때에도 가능하면,
항상 인덱스를 사용함으로써 비효율적이 될 수도 있음. 테이블명에 대한 파싱은 우에서
좌로 진행되기 때문에 최후 기술된 테이블이 먼저 처리
- 순위표(규칙)
1. 문장의 WHERE절에 ROWID나 Oracle Precompiler에 지원되는 확장된 SQL 구문 구조의
CURRENT OF CURSOR에 의해 지정된 ROW들이 식별될 수 있을 경우에만 사용이 가능
2. 문장의 WHERE 젤에 join이 단지 하나의 row를 return하는 것을 보증하는 조건절을 가지고
있어야 함. 이러한 조건절은 unique하거나 primary key인 칼럼의 '=' 조건절과 유사함.
이러한 조건들은 반드시 AND 연산자를 통해 합쳐져야 하고, 이러한 문장을 수행하기 위해
Oracle은 nested loops 연산을 수행
3. WHERE 절이 '='조건절에 HASH CLUSTER KEY의 모든 칼럼을 사용하여야 하며, 합섣된
CLUSTER KEYS인 경우에는 '=' 조건졀이 반드시 AND 연산자에 의해 연결되어야 함.
문장은 칼럼이 HASH CLUSTER KEY를 만들어 내듯이 UNIQUE나 PRIMARY KEY를 만들어내기
때문에 단지 하나의 ROW만을 RETURN해야 함
4. WHERE절이 '=' 조건절에서 칼럼들의 unique나 primary key를 사용할 경우에 사용
5. 이 접근 경로는 두 Join 테이블이 같은 cluster에 저장되고, 문장의 Where절에 한 테이블의
각 칼럼에 대해 대응하는 테이블의 칼럼을 같게 하는 조건절을 포함하는 경우 사용함.
6. WHERE절에 hash cluster key인 칼럼의 '='조건절이 있을 경우에 사용
7. WHERE절에 Index cluster key인 칼럼의 '='조건절이 있을 경우에 사용
8. WHERE절에 결합 INDEX의 결럼이 있을 경우에 사용
9. WHERE절에 single row index의 컬럼이 있을 경우에 사용
10. index column이 bound range 된 것을 Where 조건에서 있을 경우에 사용
11. index column이 unbound range 된 것을 Where조건에서 있을 경우에 사용
12. where조건에 있는 컬럼들이 각각의 index로 구성되었을 경우에 사용
13. MAX와 MIN함수의 argument는 칼럼, 상수, +연산자, ||연산자나 CONCAT함수 등을 포함하는
어떠한 수식도 올 수 있음. 단, select 절에 다른 수식이 오지 않아야 하며 문장은
WHERE절이나 GROUP BY절이 없어야 함.
14. index의 컬럼이 order by절에서 이용할 경우에 사용
15. 마지막으로 테이블 전체를 읽게 됨 |
|
⊙ Cost-Based Optimizer (비용기반 최적화)이론적으로 RBO에 비해 진보됨, 전문지식이 없어도
악성계획의 회피 가능
- 논리적인 한계성이 존재, 원하는 경로로 유도하기 어려운 단점
- 환경 설정 Parameter
1. Optimizer Mode = CHOOSE : 비용 기반 옵티마이저 환경을 의미
2. ALL_ROWS : 비용 기반 옵티마이저 환경을 의미하며, SQL문의 WHERE 조건을
만족하는 모든 행을 가장 빠르게 검색하는 실행 계획을 결정함
3. FIRST_ROWS : SQL문의 WHERE 조건을 만족하는 첫 번째 행을 가장 빠르게 검색하는
실행계획을 결정함
4. FIRST_ROWS_10 : ~~~ 10개 행을
5. FIRST_ROWS_100 : ~~~ 100개 행을
6. FIRST_ROWS_1000 : ~~~ 1000개 행을
- 특징
1. 좌에서 우로 파싱되므로 최초 기술된 테이블이 먼저 처리됨
2. ANALYZE 명령은 과부하를 초래하기 때문에 항상 최선은 아님
3. CHOOSE Mode에서 SQL문에 포함된 테이블 중 어느 하나라도 이미 분석된 상태라면,
그 SQL문은 기본적으로 CBO(비용기반 최적화)에 맞춰지게 됨 |
|
⊙ Optimizer Modes 설정 방법
- Optimizer Mode = ( CHOOSE, ALL_ROWS, FIRST_ROWS, RULE
- 시스템 전체에 지정하기 (instance level)
＊ SQL> SET AUTOTRACE TRACE EXPLAIN
- 세션에서 지정 (Seesion level)
＊ ALTER SESSION SET OPTIMIZE_MODE = ( CHOOSEM ALL_ROWS, FIRST_ROWS, RULE )
- SQL문장에서 지정 (Statement level)
＊ SELECT /*+ (ALL_ROWS, FIRST_ROWS, RULE) * /
＊ Hint를 사용하지 않는 경우 Session 혹은 System 전체에 정해진 Rule로 실행계획을
생성한 상태
- 논리적인 한계성이 존재, 원하는 경로로 유도하기 어려운 단점
- 환경 설정 Parameter
- Optimizer Mode = CHOOSE : 비용 기반 옵티마이저 환경을 의미
- ALL_ROWS : 비용 기반 옵티마이저 환경을 의미하며, SQL문의 WHERE 조건을 만족하는 모든 행을 가장 빠르게 검색하는 실행 계획을 결정함
- FIRST_ROWS : SQL문의 WHERE 조건을 만족하는 첫 번째 행을 가장 빠르게 검색하는 실행계획을 결정함
- FIRST_ROWS_10 : ~~~ 10개 행을
- FIRST_ROWS_100 : ~~~ 100개 행을
- FIRST_ROWS_1000 : ~~~ 1000개 행을
- 특징
- 좌에서 우로 파싱되므로 최초 기술된 테이블이 먼저 처리됨
- ANALYZE 명령은 과부하를 초래하기 때문에 항상 최선은 아님
- CHOOSE Mode에서 SQL문에 포함된 테이블 중 어느 하나라도 이미 분석된 상태라면, 그 SQL문은 기본적으로 CBO(비용기반 최적화)에 맞춰지게 됨
|
|
☞ 옵티마이저를 이용한 SQL 튜닝 |
| |
⊙ 인덱스 정보 이용법
1. SELECT DISTINCT B.CLASS, COUNT(B.CLASS) //DISTINCT는 아래의 GROUP BY에 의해 의미가 없음
FROM COL A, COM B
WHERE A.YEAR = B.YEAR
AND A.HOUSE = B. HOUSE
A.SERIAL_NO = B.SERIAL_NO
A.AMT >=100
B.DATE < '950501'
B.CLASS IN('C', 'Y', 'I' ,'P')
GROUP BY B.CLASS;
2. CHOOSE Mode와 CBO
＊ 조합된 테이블 중 어느 하나라도 이미 분석된 상태라면, 기본적으로 CBO로 작동함
＊ SELECT /** RULE **/ ROUND (NUV(SUM(C.REQR_QTY Z.MIN_COMP_QTY * ...
3. 비용 기반 옵티마이저를 이용한 sql튜닝
1. 아무런 인덱스가 없는 테이블 EMP의 znalyze 되기 전의 실행계획
2. 테이블 emp만 analyze 되었을 경우의 실행계획
3. 테이블 emp의 칼럼 deptno에 대해서 인덱스 생성시의 실행계획
4. 테이블 emp의 칼럼 empno에 대해서 인덱스 생성시의 실행계획
＊ 아무런 인덱스가 없는 테이블 EMP의 znalyze 되기 전의 실행계획
▶ SELECT ename
FROM em
WHERE deptno = 20
AND empno BETWEEN 100 TO 200
ORDER BY ename;
Execution Plan
-----------------------------------------------------------------------
0 SELECT STATEMENT Optimizer = CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) of 'EMP'
현재는 아무런 인덱스가 없으며 분석되지 않았으며, 비용기반이 아님
＊ 테이블 emp만 analyze 되었을 경우의 실행계획
▶ SQL> ANALYZE TABLE emp COMPUTE STATISTICS;
▶ 분석 이후 Cost, Card, Byte등이 출력됨. (Cost가 큰 수일수록 비효율적임)
＊ 테이블 emp의 칼럼 deptno에 대해서 인덱스 생성시의 실행계획
▶ SQL> CREATE INDEX EMP_DEMPTNO_IDX ON EMP(DEPTNO);
▶ 초기의 COST보다 18이 줄은 값이 나옴
▶ 인덱스 사용이 미사용보다 COST가 높았다면 인덱스를 사용하지 않았을 것임 | |
