앞서 옵티마이저 행동에 영향을 미치는 요소들에 대해 살펴보았는데, 강조하고자 한 핵심은 옵티마이저가 절대 완벽할 수 없다는 것이다.
옵티마이저도 결국 사람이 만든 소프트웨어 엔진에 불과하며, 모든 프로그램이 업그레이드를 통해 조금씩 개선되듯 옵티마이저도 여러 가지 제약과 한계점들을 극복하며 발전해 나가는 과정 속에 있다.
기술적으로 도저히 극복할 수 없는 한계점들이 있는가 하면, 정보를 저장할 공간과 시간 제약(특히 OLTP 환경에서 파싱에 허용되는 시간은 극히 짧음)때문에 아직 적용하지 못하는 최적화 기법들도 있다.
`자동 튜닝 옵티마이저`라 불리는 오프라인 옵티마이저가 이런 사실을 잘 말해 주는데,
`튜닝 모드`에서 작동하는 이 옵티마이저는 시간에 구애받지 않고 충분한 시간 동안 동적 샘플링을 포함한 여러 기법을 활용해 튜닝을 실시하고 사용자에게 권고안을 제시한다.
자동 튜닝 옵티마이저
자동 튜닝 옵티마이저(Automatic Tuning Optimizeer)를 `오프라인 옵티마이저`라고도 한다.
반대로 말해, 우리가 흔히 말하는 옵티마이저는 `온라인 옵티마이저` 또는 `런타임 옵티마이저`인 셈이다.
자동 튜닝 옵티마이저는 통계를 분석하고, SQL 프로파일링을 실시하며, 액세스 경로 및 SQL 구조 분석을 통해 SQL 튜닝을 실시한다.
튜닝 모드에서 작동하는 이 옵티마이저에게는 한 문장을 튜닝하는 데에 런타임 옵티마이저보다 훨씬 긴 시간이 주어진다.
넉넉한 시간 동안 풍부한 정보를 수집, 활용함으로써 데이터 액세스 비용과 카디널리티를 보다 정확하게 계산할 수 있다.
예를 들어, 동적 샘플링을 통해 부가적인 정보를 수집하고, 심지어 부분적인 실행을 통해 예측치(조인 카디널리티 등)를 검증함으로써 잘못된 정보를 조정하는 테크닉을 사용한다.
그렇게 각 SQL 단위로 수집된 프로파일(상관관계 있는 컬럼 간 결합 분포, 조인에 의한 테이블 간 상관관계 등 해당 SQL만을 위한 보조적인 통계정보)을 데이터 딕셔너리에 영구 저장해 런타임 옵티마이저가 참조할 수 있도록 하는 기능도 제공한다.(sqltune_category 파라미터 참조)
자동 튜닝 옵티마이저 기능을 활용하려면 `SQL Tuning Advisor`라 불리는 서버 유틸리티를 이용하면 된다.
SQL Tuning Advisor에 SQL문을 입력하면 내부적으로 자동 튜닝 옵티마이저를 호출해 SQL 분석을 실시한다.
분석이 완료되면 SQL 성능을 높이기 위해 사용자가 취해야 할 조치사항들을 보고서 형태로 출력해 준다.
SYS@ora10g>grant advisor to scott ;
Grant succeeded.
SYS@ora10g>conn scott/tiger
Connected.
SCOTT@ora10g>CREATE TABLE test_advisor (id CONSTRAINT t_pk PRIMARY KEY, pad) AS
SELECT rownum, lpad('*',4000,'*')
FROM all_objects
WHERE rownum <= 10000 ;
Table created.
SCOTT@ora10g>VARIABLE tn VARCHAR2(30)
SCOTT@ora10g>DECLARE
l_sqltext CLOB := 'SELECT COUNT(*) FROM test_advisor WHERE id+42 = 126' ;
BEGIN
:tn := dbms_sqltune.create_tuning_task(sql_text=>l_sqltext) ;
dbms_sqltune.execute_tuning_task(:tn) ;
END;
/
PL/SQL procedure successfully completed.
SCOTT@ora10g>print TN
TN
--------------------------------
TASK_1366
SCOTT@ora10g>set long 20000
SCOTT@ora10g>SELECT dbms_sqltune.report_tuning_task(:tn) FROM dual ;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_1366
Tuning Task Owner : SCOTT
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 09/25/2010 21:10:54
Completed at : 09/25/2010 21:10:55
Number of Statistic Findings : 1
Number of Index Findings : 1
Number of SQL Restructure Findings: 1
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 5pha323u5skht
SQL Text : SELECT COUNT(*) FROM test_advisor WHERE id+42 = 126
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."TEST_ADVISOR" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'TEST_ADVISOR', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
create index SCOTT.IDX$$_05560001 on SCOTT.TEST_ADVISOR('ID'+42);
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate "TEST_ADVISOR"."ID"+42=126 used at line ID 2 of the execution
plan contains an expression on indexed column "ID". This expression prevents
the optimizer from selecting indices on table "SCOTT"."TEST_ADVISOR".
Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.
Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 454320086
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| T_PK | 85 | 1105 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"+42=126)
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
2- Using New Indices
--------------------
Plan hash value: 2428417073
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
|* 2 | INDEX RANGE SCAN| IDX$$_05560001 | 10 | 130 | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TEST_ADVISOR".???)
-------------------------------------------------------------------------------
옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이징 팩터를 제공하는 것은 결국 사람의 몫이다.
현실적인 제약 때문에 샘플링 방식으로 통계를 수집하다 보니 실제 데이터와 불일치가 발생하기 마련이다.
부정확한 통계의 연장선으로 볼 수 있는데, 히스토그램 버킷 개수로 254개까지만 허용된다는 점도 옵티마이저에겐 중요한 제약사항이다.
조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을 계산한다.
조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어렵다.
select * from 사원 where 직급 = '부장' and 연봉 >= 5000;
I/O 비용 모델 하에서의 비용은 단순히 I/O Call 횟수를 의미한다. 그런데 I/O 비용 모델이 사용하는 기본 가정에 따르면 Single Block Read와 Multiblock Read의 비용은 같다.
옵티마이저는 이 둘을 구분하지 않고 동일한 하나의 시스템 Call로 간주하는 것이다.
또한 옵티마이저는 다른 세션이나 다른 쿼리문에 의해 데이터 블록들이 이미 버퍼 캐시에 캐싱돼 있을 가능성을 배제한다.
아무리 비용기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다.
예를 들어, 원격 테이블이나 External 테이블에 대해서는 카디널리티, 평균 행 길이, 블록 수, 그리고 각종 인덱스 관련 통계항목들에 대해 고정된 상수 값을 사용
옵티마이저 모드를 first_rows로 설정했을 때 order by 소트를 대체할 인덱스가 있으면 무조건 인덱스를 사용
9i까지 옵티마이저는 뷰 또는 서브쿼리를 만나면 무조건 쿼리 블록을 풀어 메인 쿼리와 Merging하려고 시도
CBO가 사용하는 규칙과 관련해 꼭 기억해야할 사항이 있는데, 두 대안 인덱스의 예상 비용이 같을 때 알파벳 순에서 앞선 것을 선택한다는 사실이다.(인덱스 명명 규칙의 중요성 시사)
SCOTT@ora10g>create table t_3
as
2 3 select rownum a, rownum b from dual
4 connect by level <= 10000 ;
Table created.
SCOTT@ora10g>create index t_x01 on t_3(a) ;
Index created.
SCOTT@ora10g>create index t_x02 on t_3(b) ;
Index created.
SCOTT@ora10g>exec dbms_stats.gather_table_stats(user, 't_3') ;
PL/SQL procedure successfully completed.
-- a, b 두 컬럼을 가진 테이블을 생성하고 100% 같은 값을 입력했다.
-- 그리고 각 컬럼에 단일 컬럼 인덱스를 생성해 두었다.
SCOTT@ora10g>set autotrace traceonly exp
SCOTT@ora10g>select * from t_3 where a = 1 and b = 1 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| T_3 | 1 | 7 | 2 (0)|
|* 2 | INDEX RANGE SCAN | T_X01 | 1 | | 1 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1)
2 - access("A"=1)
위와 같은 조건절을 만났을 때 t_x01, t_x02 둘 중 어느 것을 선택하든 쿼리 수행 비용은 같다.
이때 옵티마이저가 t_x01 인덱스를 선택한 판단 근거는, 허무하게도 인덱스명의 알파벳 순이다.
t_x01 인덱스명을 t_x03으로 바꾸고 나서 실행계획을 다시 확인하면, t_x02 인덱스가 사용되는 것을 볼수 있다.
SCOTT@ora10g>alter index t_x01 rename to t_x03 ;
Index altered.
SCOTT@ora10g>select * from t_3 where a = 1 and b = 1 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| T_3 | 1 | 7 | 2 (0)|
|* 2 | INDEX RANGE SCAN | T_X02 | 1 | | 1 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1)
2 - access("B"=1)
CBO가 이런 단순한 규칙을 사용함으로 인해 가끔 실행계획에 문제가 생기는 것을 보게 된다.
예를 들어, 테이블명_PK, 테이블명_N01, 테이블명_N02와 같은 식으로 인덱스 명명 규칙을 정했다고 하자.
ORDER_PK : 고객번호 + 주문일자
ORDER_N01 : 고객번호 + 배송일자
인덱스가 위와 같은 상황에서 대부분 고객이 평균적으로 두서너 달에 한 번씩 주문한다면,
아래 쿼리에 대한 PK, N01 인덱스의 예상 비용이 똑같을 수 있다. 두 인덱스 모두 카디널리티가 1미만이기 때문이다.
where 고객번호 = :cost_no
and 주문일자 = :ord_dt
비용이 어떻든 간에 PK 구성 컬럼이 모두 등치(=) 조건으로 조건절에 사용되고 인덱스 높이도 같다면, 상식적으로 PK 인덱스를 사용하는 것이 최선의 선택이다.
그런데도 옵티마이저는 알파벳 순에 따라 N01 인덱스를 선택한다.
이때, 거의 매일 주문을 일으키는 우수 고객이 입력되면 N01 인덱스는 주문일자를 필터링하려고 엄청나게 많은 테이블 Random 액세스를 일으킬 것이다.
이처럼 두 인덱스의 예상 비용이 같아 비합리적인 인덱스 선택이 이루어지는 것은 매우 흔한 일이다.
그런 현상을 발견했을 때 인덱스명을 바꿔줄 수 있으면 좋겠지만 실제 운영 중인 시스템에서 그러기는 쉽지 않다.
적어도 Unique한 조건으로 PK 인덱스를 액세스해야 하는 상황에서 이런 일이 발생하지 않게 하려면 인덱스 명명 규칙을 PK, X01, X02와 같은 식으로 정하는 것이 좋다. 참고로, 예상 비용이 같으면 오브젝트ID가 큰 것을 우선적으로 선택하던 시절도 있었다.
옵티마이저는 기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰져 있다.
따라서 실제 운영 시스템의 하드웨어 사양이 그것과 다를 때 옵티마이저가 잘못된 실행계획을 수립할 가능성이 높아진다.
또한 애플리케이션 특성(I/O 패턴, 부하 정도 등)에 의해서도 하드웨어 성능은 달라진다.
동적 실시간 최적화(Dynamic Runtime Optimizations)
모든 데이터베이스의 작업 부하는 매우 가변적이다.
따라서 하드웨어 성능 특성을 반영한 실행계획을 수립하더라도 쿼리 수행 당시 시스템 부하 정도에 따라 최적이 아닐 수 있다.
정적인 통계정보와 옵티마이저 모델로는 이런 한계점을 극복하기 어려우므로 시스템 부하에 따라 실행전략을 동적으로 조정하는 최적화 기법이 도입되고 있다.
이 기능의 핵심은 쿼리가 수행되는 시점의 시스템 상태에 따라 하드웨어 리소스(CPU와 메모리)를 적절히 배분해 주는 데 있다.
대표적으로, 시스템 부하 정도에 따라 병렬 쿼리의 프로세스 개수를 오라클이 동적으로 조절해 주는 기능을 들 수 있다.
또한 9i부터 PGA 메모리 크기를 자동으로 조절해 주기 시작했고, 10g부터는 SGA를 구성하는 서브 메모리 영역을 자동으로 조절해 주는 기능도 소개되었다.
이런 기능을 통해 고정된 하드웨어 리소스와 정해진 시간 동안, 개별 SQL이 아닌 전체 SQL의 처리량을 극대화할 수 있다.
쿼리 최적화가 단일 SQL문 성능을 최적화하는 데 초점을 맞추는 반면, 동적 실시간 최적화는 수많은 SQL이 동시에 수행되는 환경에서 시스템 전체 최적화를 이루는 데 초점을 맞춘다.