꿈꾸는 개발자, DBA 커뮤니티 구루비

옵티마이저의 한계

옵티마이저의 한계

자동 튜닝 옵티마이저

자동 튜닝 옵티마이저(Automatic Tuning Optimizeer)를 `오프라인 옵티마이저`라고도 한다.
반대로 말해, 우리가 흔히 말하는 옵티마이저는 `온라인 옵티마이저` 또는 `런타임 옵티마이저`인 셈이다.

자동 튜닝 옵티마이저는 통계를 분석하고, SQL 프로파일링을 실시하며, 액세스 경로 및 SQL 구조 분석을 통해 SQL 튜닝을 실시한다.
튜닝 모드에서 작동하는 이 옵티마이저에게는 한 문장을 튜닝하는 데에 런타임 옵티마이저보다 훨씬 긴 시간이 주어진다.
넉넉한 시간 동안 풍부한 정보를 수집, 활용함으로써 데이터 액세스 비용과 카디널리티를 보다 정확하게 계산할 수 있다.

예를 들어, 동적 샘플링을 통해 부가적인 정보를 수집하고, 심지어 부분적인 실행을 통해 예측치(조인 카디널리티 등)를 검증함으로써 잘못된 정보를 조정하는 테크닉을 사용한다.
그렇게 각 SQL 단위로 수집된 프로파일(상관관계 있는 컬럼 간 결합 분포, 조인에 의한 테이블 간 상관관계 등 해당 SQL만을 위한 보조적인 통계정보)을 데이터 딕셔너리에 영구 저장해 런타임 옵티마이저가 참조할 수 있도록 하는 기능도 제공한다.

자동 튜닝 옵티마이저 기능을 활용하려면 `SQL Tuning Advisor`라 불리는 서버 유틸리티를 이용하면 된다.
SQL Tuning Advisor에 SQL문을 입력하면 내부적으로 자동 튜닝 옵티마이저를 호출해 SQL 분석을 실시한다.
분석이 완료되면 SQL 성능을 높이기 위해 사용자가 취해야 할 조치사항들을 보고서 형태로 출력해 준다.

자동 튜닝 옵티마이저(SQL Tuning Advisor) 활용




grant advisor to bshman

SQL> VARIABLE tn VARCHAR2(30);
SQL> DECLARE
  2    l_sqltext CLOB := 'select count(*) from bshman_test where col1 < 100 and col3 < 100' ;
  3    BEGIN
  4    :tn := dbms_sqltune.create_tuning_task(sql_text=>l_sqltext) ;
  5    dbms_sqltune.execute_tuning_task(:tn) ;
  6    END;
  7    /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:02:03.32
SQL>
SQL>
SQL> print TN

TN
--------------------------------
작업_1291

SQL> set long 20000
SQL> select dbms_sqltune.report_tuning_task(:tn) from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : 작업_1291
Tuning Task Owner                 : BSHMAN
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 11/16/2012 22:14:52
Completed at                      : 11/16/2012 22:16:55
Number of Index Findings          : 1


DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: BSHMAN
SQL ID     : dz3t6d8yccm38
SQL Text   : select count(*) from bshman_test where col1 < 100 and col3 < 100

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)
--------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
이 명령문의 실행 계획은 하나 이상의 인덱스를

  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  -Access Advisor를 실행하여 물리적 스키마 설계를 향상시키거나 권장
    create index BSHMAN.IDX$$_050B0001 on BSHMAN.BSHMAN_TEST('COL1');

  Rationale
  ---------
권장 인덱스를 생성하면 이 명령문의 실행 계획이 크게 향상됩니다. 하지만 단
 "Access Advisor"를 실행하는 것이 더 좋을 수 있습니다. 이렇게 하면 인덱스 유

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
 고려한 포괄적인 인덱스 권장 사항을

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 2702808718

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

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     8 |  8378   (3)| 00:01:41 |
|   1 |  SORT AGGREGATE    |             |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| BSHMAN_TEST |     3 |    24 |  8378   (3)| 00:01:41 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
   2 - filter("COL1"<100 AND "COL3"<100)

2- Using New Indices
--------------------
Plan hash value: 2810742563

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |     8 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BSHMAN_TEST    |     3 |    24 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX$$_050B0001 |     3 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
   2 - filter("COL3"<100)
   3 - access("COL1"<100)

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



경   과: 00:00:00.48

위와 같은 기능은 제공은 하지만 정보부족 등 운영환경에 대한 특성으로 어떠한 제약조건들이 있는지 확인해보자.

(1) 부족한 옵티마이징 팩터

옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이징 팩터를 제공하는건 결국 사람의 몫이다.
적절한 인덱스라든지 기본정보가 존재하지않는다면 옵티마이저는 다른길을 걸을수밖에없다.

(2) 부정확한 통계

정보는 곧 비용이다. 많은 정보를 수집 , 보관한다면 그만큼 좋은 결과를 낼 수 있겠지만 현실적으로 100%
정확한 통계를 유지하기는 어렵다. 그러므로 통계정보가 부정확하다면 좋은 결과를 낼수 없다.

(3) 히스토그램의 한계

부정확한 통계의 연장선으로 볼 수 있다.
히스토그램 버킷 개수는 254개로 허용되기 때문에 좀더 많은 버킷이 존재한다면
더 정확한 카디널리티를 구하는 데 도움이 되겟지만 , 시간과 저장 공간 떄문에 생기는 어쩔 수 없는 제약사항이다.

(4) 바인드 변수 사용 시 균등분포 가정

컬럼 히스토그램이 잇으면 옵티마이저가 그것을 가지고 조건절에 대한 선택도를 구하지만
바인드변수를 사용하면 컬럼 히스토그램에 대해서 무용지물이 되기 때문이다.
http://wiki.gurubee.net/pages/viewpage.action?pageId=25296943 <-- 바인드변수의 부작용

(5) 결합 선택도 산정의 어려움



select * from 사원 where 직급 = '부장' and 연봉 >= 5000;

-- 직급이 [부장, 과장, 대리, 사원] 의 집합이고 각각 25%의 비중을 갖는다.
-- 전체 사원이 1000명이고 히스토그램상 '연봉 >= 5000' 조건에 부합하는 사원 비중이 10%이면, 옵티마이저는
-- 위 쿼리 조건에 해당하는 사원 수를 25(=1000*0.25*0.1)명으로 추정한다.
-- 하지만, 직급과 연봉 간에 상관관계가 매우 높아서 만약 모든 부장의 연봉이 5000만원 이상이라면 실제 위 쿼리 결과는
-- 250(=1000*0.25*1)건이다.

-- 사원 급여와 상여금 간에도 상관관계가 매우 높다.
-- 이러한 이유 때문에 카디널리티가 잘못 계산되면 다른 집합과 여러 번 조인을 거치는 동안 카디널리티는 점점
-- 부정확해지고 궁극적으로 옵티마이저가 잘못된 실행계획을 수립하는 결과를 낳는다.

-- 9i 부터 동적 샘플링으로 해결하려고 하고있지만
-- 동적 샘플링 레벨을 4이상일때만 작동한다.
-- 또한 11g부터는 사용자가 지정한 컬럼들에 대해 결합 선택도를 미리 수집해 두는 기능을 제공하는데
-- 이러한 기능은 6절5항 (429p 결합산정도에서 확인가능)

(6) 비현실적인 가정

CBO는 쿼리 수행 비용을 평가 할 때 여러 가정을 사용하는데,대표적으로 single block I/O, multiblock I/O 비용을 같게 평가한다거나 캐싱효과를 고려하지 않는다는 점을 들 수 있다.
이러한 비현실적인 가정들을 보정할 수 있도록 오라클은 8i에서 아래 두개의 파라미터를 제공한다.
- optimizer_index_caching
- optimizer_index_cost_adj
위 파라미터관련된 내요은 437p(비용)에서 확인가능하다

(7) 규칙에 의존하는 CBO


create table t
as
select rownum a, rownum b from dual
connect by level <= 10000;

create index t_x01 on t(a);

create index t_x02 on t(b);

exec dbms_stats.gather_table_stats(user,'t');

SQL> set autotrace traceonly exp
SQL> select * from t where a = 1 and b =1;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 481254278

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     7 |     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)

-- 인덱스를 t_x01 에서 t_x03으로 변경
alter index t_x01 rename to t_x03

SQL> select * from t where a = 1 and b =1;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 632348571

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     7 |     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)

order_pk  : 고객번호 + 주문일자
order_n01 : 고객번호 + 주문일자

같은 조건이라면 당연 pk를 타는게 맞지만,
알파벳순서로 n01 인덱스를 탄다.

(8) 하드웨어 성능 특성

옵티마이저는 기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰져 있다.
따라서 운영시스템 하드웨어 환경에 따라 옵티마이저는 다른 실행계획을 수립할 가능성이 존재한다.
하지만, 9i에서부터는 시스템 통계를 수집하는 기능이 도입되어서 해결방법은 찾았지만, 실제 운영사이트에서는 많이 사용하지않은게 현실이다.

동적 실시간 최적화(Dynamic Runtime Optimizations)

모든 데이터베이스의 작업 부하는 매우 가변적이다.
따라서 하드웨어 성능 특성을 반영한 실행계획을 수립하더라도 쿼리 수행 당시 시스템 부하 정도에 따라 최적이 아닐 수 있다.
정적인 통계정보와 옵티마이저 모델로는 이런 한계점을 극복하기 어려우므로 시스템 부하에 따라 실행전략을 동적으로 조정하는 최적화 기법이 도입되고 있다.
이 기능의 핵심은 쿼리가 수행되는 시점의 시스템 상태에 따라 하드웨어 리소스(CPU와 메모리)를 적절히 배분해 주는 데 있다.

대표적으로, 시스템 부하 정도에 따라 병렬 쿼리의 프로세스 개수를 오라클이 동적으로 조절해 주는 기능을 들 수 있다.
또한 9i부터 PGA 메모리 크기를 자동으로 조절해 주기 시작했고, 10g부터는 SGA를 구성하는 서브 메모리 영역을 자동으로 조절해 주는 기능도 소개되었다.

이런 기능을 통해 고정된 하드웨어 리소스와 정해진 시간 동안, 개별 SQL이 아닌 전체 SQL의 처리량을 극대화할 수 있다.
쿼리 최적화가 단일 SQL문 성능을 최적화하는 데 초점을 맞추는 반면, 동적 실시간 최적화는 수많은 SQL이 동시에 수행되는 환경에서 시스템 전체 최적화를 이루는 데 초점을 맞춘다.|