메뉴 건너뛰기

bysql.net

3. 옵티마이저의 한계

2011.04.19 09:28

휘휘 조회 수:6698

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

 

자동 튜닝 옵티마이저

 

===== 실습. 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부터 시스템 통계를 수집하는 기능이 도입

- 동적 실시간 최적화

 

번호 제목 글쓴이 날짜 조회 수
35 1. 옵티마이저 file 휘휘 2011.04.18 6057
34 3. 옵티마이저의 한계 - P 휘휘 2011.04.18 3695
33 2. 옵티마이저 행동에 영향을 미치는 요소 balto 2011.04.18 6158
» 3. 옵티마이저의 한계 휘휘 2011.04.19 6698
31 4. 통계정보 Ⅰ file 토시리 2011.04.25 16002
30 6. 히스토그램 오예스 2011.04.25 17363
29 5. 카디널리티 오라클잭 2011.04.27 12913
28 7. 비용 file balto 2011.05.02 4989
27 8. 통계정보 Ⅱ AskZZang 2011.05.04 5913
26 1. 쿼리 변환이란? 운영자 2011.05.16 6254
25 3. 뷰 Merging 오라클잭 2011.05.17 6080
24 2. 서브쿼리 Unnesting 토시리 2011.05.18 2074
23 5. 조건절 이행 file balto 2011.05.30 5460
22 4. 조건절 Pushing 오예스 2011.05.31 17467
21 6. 조인 제거 AskZZang 2011.06.01 5440
20 7. OR-Expansion AskZZang 2011.06.01 8320
19 12. 기타 쿼리 변환 휘휘 2011.06.06 3118
18 10. 실체화 뷰 쿼리로 재작성 오라클잭 2011.06.08 9949
17 11. 집합 연산을 조인으로 변환 오라클잭 2011.06.08 4954
16 2. 소트를 발생시키는 오퍼레이션 file balto 2011.06.12 4846