h3.03 옵티마이저의 한계
기술적으로 도저히 극복할 수 없는 한계점들이 있는가 하면 정보를 저장할 공간과 시간 제약(특히 OLTP 환경에서 파싱에 허용되는 시간은 극히 짧음) 때문에 아직 적용하지 못하는 최적화 기법들도 있다.
| 한계에 대한 설명 |
|---|
| 예를 들어, 3개의 테이블, T1, T2, T3에 대해 조인을 수행하는 SQL 문이 있다고가정하자. 그럼 이 질의를 수행할 수 있는 가능한 실행 계획은 몇 가지일까? 우리는 앞에서 조인 순서, 조인 방법, 그리고 테이블 액세스 방법에 따라 서로 다른 실행계획이 만들어진다고 했다. 그렇다면, 3개의 테이블 T1, T2, T3에 대한 조인 순서는 3!, 즉 6개의 조인 순서가 있다.(T1§_T2)§_T3, (T1§_T3)§_T2, (T2§_T1)§_T3, (T2§_T3)§_T1, (T3§_T1)§_T2,(T3§_T2)§_T1 그리고, 하나의 조인 순서에는 2개의 조인을 포함하는데, 이용 가능한 조인 방법이Nested Loop, Sort Merge, Hash Join의 세 가지가 있다면, 각 조인 순서에 대해 총 32, 즉 9개의 조합이 가능하다. 그리고, 이 각각의 경우 테이블을 접근하는액세스 방법이 Full Table Scan과 Index Scan의 두 가지가 있다면 23, 즉 8개의 서로 다른 조합이 가능하다. 따라서, 3! x 32 x 23 = 432가지의 실행 계획이 가능하다. |
| 그런데, 옵티마이저가고려해야 할 실행 계획의 개수는 SQL에 포함된 테이블의 개수가 증가함에 따라 기하급수적으로 늘어나게 된다. 만일 from 절의 테이블의 개수가 5개인 경우, 5! x35 x 25 = 933,120개가 가능해진다. 그리고, 여기서 각 실행 계획의 예상 비용을 계산하는 데 걸리는 시간이 0.01초라고가정했을 때, 모든 실행 계획의 예상 비용을 구하는 데 약 9,300초(약 2시간 36분)이 걸린다. 만일 테이블의 개수가 10개라고 가정하면, 아마도 모든 실행 계획의 예상 비용을 계산하는 데만도 몇 년이 걸릴지도 모른다. 21세기 IT 환경에서는 하나의 SQL 문에 5 ~ 10개 정도의 테이블이 포함되는 경우가 일반적이다. 그런데, 옵티마이저가 실행 계획을 선정하는 데 걸리는 시간이 이와 같다면, 옵티마이저는 차라리 없는 것이 더 나을지도 모른다. |
| 따라서, 옵티마이저는 모든 가능한 실행 계획을 다 고려할 수는 없다. 즉, 질의 최적화에 걸리는 시간을 줄이기 위해 어떤 실행 계획들은 아예 비용 계산에서 제외해야 할 필요도 있다. 옵티마이저는 모든 가능한 실행 계획 조합들을 탐색하는 방법 -즉 어떤 실행 계획을 먼저 고려하고, 어떤 순서로 다음 실행 계획을 찾고, 어떤 실행 계획은 제외할 것인가? - 을 갖고 있어야 한다. 테이블의 개수가 많으면 가능한 조인 순서의 조합이 기하급수적으로 늘어나게 된다. 이렇게 되면 옵티마이저 시간이 너무 많이 걸리기 때문에, 옵티마이저는 일정한 수(디폴트로는 최대 80,000)의 조인순서에 대해서만 비용을 계산하고, 이 중에서 가장 최선의 실행 계획을 찾게 된다. 즉, 모든 가능한 조인 순서 조합들 중에서 일부분만 비용을 계산하고, 나머지는 고려하지 않는 것이다. 이를 실행 계획 탐색에 대한'가지치기(pruning)'또는'컷오프(cutoff)'라 부른다. |
| 그런데, 고려되지 않은 조인 순서 중에서 실제로 최선의 실행 계획이 포함되어 있을수 있다. 옵티마이저가 제일 처음 고려하는 조인 순서를 테이블의 레코드 수의 오름차순 순서로 정하는 이유는 경험적으로 이 순서 근처에 실제로 최적의 실행 계획이존재하기 때문이다. 이와 같이 초기 조인 순서를 선택하는 휴리스틱(heuristic)을 사용함으로써 임의로 조인 순서를 시작했을 때 최적의 좋은 실행 계획이 컷오프되는것을 막을 수 있다. 오라클 옵티마이저 실행 계획 생성 모듈(Oracle9i부터 도입된)의 또 다른 특징은,조인 순서를 바꾸어가면서 지금까지 구한 최적의 실행 계획의 예상 비용이 그리 크지 않은 경우, 최적화 단계를 일찍 끝내버린다. 예를 들어, 어떤 질의에 대해 10초 동안 최적화를 수행해서 찾은 최적 실행 계획의예상 수행 시간이 1분이면, 남은 조인 순서가 더 있더라도 옵티마이저 단계를 종료한다. 반면에, 지금까지 구한 최적 예상 수행 시간이 2시간이면, 더 나은 실행 계획을 찾기 위해 새로운 조인 순서에 대해 계속 탐색할 필요가 있다. 이를'적응적 탐색 전략(adaptive search strategy)'이라 부른다. |
개인적인 의견.
OLTP작업이 아니라면, 시간이 그렇게 크게 문제되나?
만약 6시간 짜리 복잡도 있는 쿼리가 있다면 좀더 최적화된 실행계획을 얻기위해 6초 아니 600초도 감수할수 있지 않을까?
그래서 나온것이 자동튜닝 옵티마이저 일듯 하다.
| 자동 튜닝 옵티마이저 |
|---|
| 자동 튜닝 옵티마이저(Automatic Tuning Optimizer)를 '오프라인(offline) 옵티마이저' 라고도 한다. 반대로 말해, 우리가 흔히 말하는 옵티마이저는 '온라인(Online) 옵티마이저' 또는 '런타임 (Runlime) 옵티마이저'인셈이다. 튜닝 모드에서 작동하는 이 옵티마이저에게는 한 문장을 튜닝하는 데에 런타임 용티마이저보다 훨씬 긴 시간이 주어진다. 'SQL Tuning Advisor' => 내부적으로 자동 튜닝 용티마이저를 호출SQL 분석 {code:sql} 예 > select last_analyzed from dba_tables where owner = 'SH' and table_name = 'SALES'; |
exec dbms_stats.delete_table_stats('SH','SALES');
exec dbms_stats.gather_table_stats('SH','SALES');
select promo_id , count(*) from sh.sales group by promo_id order by promo_id;
PROMO_ID COUNT(*)
set autot trace explain statistics
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> alter system flush buffer_cache;
시스템이 변경되었습니다.
select * from sh.sales where promo_id < 999
set autot off
select sql_text,sql_id from v$sql where sql_TEXT like '%999%'
SQL> exec dbms_stats.delete_table_stats('SH','SALES');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> @?\rdbms\admin\sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
Specify the Sql id
~~~~~~~~~~~~~~~~~~
sqlid의 값을 입력하십시오: bgrbpp990wqgt
Sql Id specified: bgrbpp990wqgt
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
1- Statistics Finding
Recommendation
Rationale
2- Index Finding (see explain plans section below)
Recommendation (estimated benefit: 88.09%)
Rationale
3- Alternative Plan Finding
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
--
Information
1- Original
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
| 0 | SELECT STATEMENT | 2500 | 212K | 487 (2) | 00:00:06 | |||
| 1 | PARTITION RANGE ALL | 2500 | 212K | 487 (2) | 00:00:06 | 1 | 28 | |
| TABLE ACCESS FULL | SALES | 2500 | 212K | 487 (2) | 00:00:06 | 1 | 28 |
Predicate Information (identified by operation id):
2 - filter("PROMO_ID"<999)
2- Using New Indices
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
| 0 | SELECT STATEMENT | 12776 | 1085K | 58 (0) | 00:00:01 | |||
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID | SALES | 12776 | 1085K | 58 (0) | 00:00:01 | ROWID | ROWID |
| INDEX RANGE SCAN | IDX$$_00220001 | 12776 | 2 (0) | 00:00:01 |
Predicate Information (identified by operation id):
2 - access("PROMO_ID"<999)
Plan 1
Plan Origin :Cursor Cache
Plan Hash Value :2853669306
Executions :1
Elapsed Time :0.024 sec
CPU Time :0.078 sec
Buffer Gets :2486
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
| 0 | SELECT STATEMENT | 7274 | 618K | 504 (1) | 00:00:07 | |||
| 1 | PARTITION RANGE ALL | 7274 | 618K | 504 (1) | 00:00:07 | 1 | 28 | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 7274 | 618K | 504 (1) | 00:00:07 | 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | |||||||
| BITMAP INDEX RANGE SCAN | SALES_PROMO_BIX | 1 | 28 |
Predicate Information (identified by operation id):
4 - access("PROMO_ID"<999)
filter("PROMO_ID"<999)
|
(1) 부족한 옵티마이징 팩터
옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이징 팩터를 제공하는 것은 결국 사람의 몫이다.
적절한 인덱스도 제공하지 않은 채 옵티마이저가 고성능 실행계획을 수립해 주기를 기대해선 안 된다.
(2) 부정확한통계
100% 정확한 통계를 유지하기는 어렵다. 이런 현실적인 제약 때문에 샘플링 방식으로 통계를 수집하다보니 실제 데이터와
불일치가발생하기 마련
어느 날 갑자기 데이터가 아주 많이 변경되거나 새로 입력됐을 때 (2차 가공을 위한 임시 테이블에 흔히 발생) 곧바로
통계를 재수집해 주지 않는다면 옵티마이저가 잘못된 선택을 하더라도 나무랄 수 없다.
(3) 히스토그램의 한계
히스토그램 버킷 개수로 254개까지만 허용.
높이균형 (height-balanced) 히스토그램을 사용하게 되므로 발생 빈도가 낮은 값을non-popular value)에 대한 정확한 분포를 반영할 수 없다.
(4) 바인드 변수 사용 시 균등분포 가정
OLTP 환경에선 라이브러리 캐시 부하를 피하기 위해 바인드 변수를 적극 사용하는 것이 필수 권고사항
조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을계산
(5) 결합 선택도 산정의 어려움
조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어렵다.
예
(6) 비현실적인 가정
CBO는쿼리 수행 비용을평가할때 여러 가정을사용하는데， 그중 일부는매우비현실적
Single Block 1/0와 Multiblock 1/0 비용을 같게 평가
캐싱 효괴를 고려하지 않는다는 점
optimizerjndex_caching
optimizecindex_cost_adj
(7) 규칙에 의존하는 CBO
원격(remole) 테이블이나 External 테이블에 대해서는 카디널리티， 평균 행 길이， 블록 수， 그리고 각종 인텍스 관련 통계항목들에 대해 고정된 상수 값을 시용
9i까지 옵티마이저는 뷰 또는 서브쿼리를 만나면 무조건 쿼리 블록을 풀어 메인 쿼리와 merging하려고 시도
||알파뱃순 인텍스 선택 규칙 예
{code:sql}
SQL> create table t
2 as
3 select rownum a, rownum b from dual
4 connect by level <= 10000;
테이블이 생성되었습니다.
SQL> create index t_x01 on t(a);
인덱스가 생성되었습니다.
SQL> create index t_x02 on t(b);
인덱스가 생성되었습니다.
SQL> exec dbms_stats.gather_table_stats(user, 't');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> set autotrace traceonly exp;
SQL> select * from t where a = 1 and b = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 481254278
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_X01 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1)
2 - access("A"=1)
SQL> alter index t_x01 rename to t_x03;
인덱스가 변경되었습니다.
SQL> select * from t where a = 1 and b = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 632348571
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_X02 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1)
2 - access("B"=1)
||
(8) 하드웨어 성능 특성
9i는 두말할 것도 없고 CPU 비용 모델이 기본 설정인 1Og에서조차 거의 대부분 오라클 설치 시 기본 값으로 설정된 NoWorkload 시스템 통계를 그대로 사용하고 있는 실정이다. No Workload 시스템 통계를 시용하더라도 기본 값이 아닌 실측치 를 사용해야 시스템 통계로서 의미
| 동적 실시간 최적화 |
|---|
| 데이터베이스의 작업 부하는 매우 가변적. 정적인 통계정보와 옵티마이저 모델로는 이런 한계점을 극복하기 어려우므로 시스템 부하에 따라 실행전략을 통적으로 조정 시스템 상태에 따라 하드웨어 리소스(CPU와 메모리)를 적절히 배분 쿼리 최적화가 단일 SQL문 성능을 최적화하는 데 초점을 맞추는 반면， 동적 실시간 최적화는 수많은 SQL이 동시에 수행되는 환경에서 시스탱 전체 최적화를 이루는 데 초점 |