3. 옵티마이저의 한계

조회 수 5762 추천 수 0 2011.04.21 10:38:32
휘휘 *.10.12.4

** 옵티마이저는 절대 완벽할 수 없다. **

 

자동 튜닝 옵티마이저

 

===== 실습. SQL Tuning advisor =====


-- scott으로 test 테이블 생성

SQL> connect scott/tiger

SQL> create table test (n number );

SQL> declare
begin
for  i  in  1 .. 10000  loop
insert into test values(i);
commit;
end loop;
end;
/

SQL> commit;


-- 인덱스 생성

SQL> create index test_idx on test(n);

-- test 테이블 통계정보 분석

SQL> analyze table test estimate statistics;

-- NO_INDEX 힌트를 주어 풀테이블 스캔으로 수행되는 SQL확인

SQL> set autot traceonly explain

SQL> select /*+ NO_INDEX(test test_idx) */ *  from test  where n = 1 ;

  --------------------------------------------------------------------------
  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |      |     1 |     3 |     5   (0)| 00:00:01 |
  |*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     5   (0)| 00:00:01 |
  --------------------------------------------------------------------------


-- 튜닝 TASK생성 후, SQL Tuning Advisor를 실행

SQL> conn / as sysdba

SQL> declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
begin
my_sqltext := 'select /*+ NO_INDEX(test test_idx) */ *  from test  where n = 1';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_text => my_sqltext,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task_1',
description => 'Task to tune a query on a specified table' );
end;
/

SQL> begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK (
task_name => 'my_sql_tuning_task_1' );
      end;
      /

-- SQL Tuning Advisor을 통해 얻은 결과(튜닝 레포트)를 확인하고 SQL Profile을 적용

SQL> SET LONG 70000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET LINESIZE 100

SQL> select DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;

SQL> DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_1',
name => 'my_sql_profile' );
END;
/

-- 위에서 실행한 악성 SQL의 실행계획을 확인

SQL> conn scott/tiger

SQL> set autot traceonly explain

SQL> select /*+ NO_INDEX(test test_idx) */ * from test where n = 1;

 풀테이블 스캔이 아닌 Index스캔을 하는 것을 알 수 있으며 my_sql_profile 이
 적용되어 실행계획이 나왔다는 것도 확인 가능하다.

 

Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178

-----------------------------------------------------------------------------
| Id  | Operation  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |   1   |   3   |   1   (0)  | 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |   1   |   3   |   1   (0)  | 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("N"=1)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

 

 

 

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

- 옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이징 팩터를 제공하는 것은 결국 사람의 몫이다.

- 이미 존재하는 길을 찾아줄 뿐 옵티마이저가 없는 길까지 만들어 낼 수는 없는 일 아닌가!!!

 

(2) 부정확한 통계

- 현실적인 제약 때문에 샘플링 방식으로 통계를 수집하다 보니 실제 데이터와 불일치가 발생하기 마련이다.

- 분포도가 균등하지 않은 컬럼이라면 수집할 때마다 통계치가 바뀔 수 있어 실행계획을 불안정하게 만든다.

- 통계 수집 주기 매우 중요.

 

(3) 히스토그램의 한계

?- 히스토그램 버킷 개수로 254개까지만 허용된다는 점도 옵티마이저에겐 중요한 제약사항이다.

 

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

- 아무리 정확한 컬럼 히스토그램을 보유하더라도 바인드 변수를 사용한 SQL에는 무용지물이다.

: 조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을 계산하기 때문이다.

- OLTP 환경에서 라이브러리 캐시 부하를 피하기 위해 바인드 변수를 적극 사용하는 것이 필수 권고사항

: 옵티마이저가 가장 극복하기 어려운 난제이다. 

 

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

- 조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어렵다.

- 모든 컬럼 간 상관관계와 결합 선택도를 미리 저장해 두면 좋겠지만 이것은 거의 불가능에 가깝다.

   테이블 컬럼이 많을수록 잠재적인 컬럼 조합의 수는 기하급수적으로 증가하기 때문이다.?

 

(6) 비현실적인 가정

- Single Block I/O와 Multi Block I/O 비용을 같게 평가한다.

- 캐싱 효과를 고려하지 않는다.

- 비현실적인 가정들을 보정할 수 있도록 제공되어지는 파라미터

- optimizer_index_caching

- optimizer_index_cost_adj 

 

(7) 규칙에 의존하는 CBO

??- 아무리 비용기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다. 

- 알파벳순 인덱스 선택 규칙

  

(8) 하드웨어 성능 특성

- 9i부터 시스템 통계를 수집하는 기능이 도입

- 동적 실시간 최적화