태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Dynamic Sampling과 Function Predicate - Oracle Performance In Depth 세미나에서

오라클 2009.02.19 20:02
얼마전 Oracle Performance In Depth 세미나를 3일 동안 열었다. 그 중에서 재미있었던 질문 몇 가지를 몇 차례에 걸쳐서 소개하고자 한다.

Oracle의 Dynamic Sampling은 매우 강력한 기능이지만 본질적인 한계를 지니고 있다. Query Level이 아닌 개별 Table 레벨이며 Predicate에 Unknown 값이 들어오면 Sampling이 동작하지 않거나, 부분 동작한다는것이다. 즉, 다음과 같은 Query들은 Dynamic Sampling의 효과를 누릴 수 없다.

select ... from t1 where c1 = :b1;
select ... from t1 where c1 in (select c2 from t2 where c3 = 1);
select ... from t1, t2 where t1.c1 = t2.c1 and t3 = 1;


이 내용을 소개하자 정말 날카로운 질문이 던져졌다. 다음과 같은 Query또한 Sampling이 안되지 않을까?

select ... from t1 where c1 = function1(...);
select ... from t1 where function2(c1) = ...;


짝짝짝! 놀라운 추리력이다. 과연 그렇다. 아래 테스트를 보자.

UKJA@ukja102> create table t1(c1 int);

Table created.


항상 "1"을 Return하는 함수를 만든다.

UKJA@ukja102> create or replace function func1(v1 int)
  2  return number
  3  deterministic
  4  is
  5  begin
  6    return 1;
  7  end;
  8  /

Function created.

UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> insert into t1
  2  select level from dual connect by level <= 10000
  3  ;

10000 rows created.

UKJA@ukja102>
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);

PL/SQL procedure successfully completed.



Oracle은 Function의 결과를 미리 알 수 없기 때문에 결코 정확한 예측을 할 수 없다.
이런 경우에는 Function Based Index를 만든다면 효과가 있을 것이다.

UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
  2  from t1
  3  where func1(c1) = 1
  4  ;

  COUNT(*)                                                                                         
----------                                                                                         
     10000                                                                                         

예측과 실제는 전혀 틀리다.

UKJA@ukja102> select * from table
  2  (dbms_xplan.display_cursor(null,null,'allstats last'));
                                                                                                  
-------------------------------------------------------------------------------------              
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |              
-------------------------------------------------------------------------------------              
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.12 |      23 |              
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    100 |  10000 |00:00:00.09 |      23 |              
-------------------------------------------------------------------------------------              
 


강제로 Dynamic Sampling을 수행하면 어떨까?

UKJA@ukja102> select /*+ gather_plan_statistics dynamic_sampling(t1 4) */ count(*)
  2  from t1
  3  where func1(c1) = 1
  4  ;

  COUNT(*)                                                                                         
----------                                                                                         
     10000            
                                                                             


전혀 개선되지 않는다.

UKJA@ukja102> select * from table
  2  (dbms_xplan.display_cursor(null,null,'allstats last'));
                                                                               
-------------------------------------------------------------------------------------              
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |              
-------------------------------------------------------------------------------------              
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.12 |      23 |              
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    100 |  10000 |00:00:00.09 |      23 |              
--------------------------------------------------------------------------------
-----              
                                                                                                   

다행히 다음과 같이 Oracle의 Built-in 함수들(아마 이또한 전체는 아닐 것이다)에 대해서는 Dynamic Sampling이 성공할 수도 있음을 알 수 있다.

UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
  2  from t1
  3  where mod(c1,2) = 1
  4  ;

  COUNT(*)                                                                                         
----------                                                                                         
      5000                                                                                         


UKJA@ukja102> select * from table
  2  (dbms_xplan.display_cursor(null,null,'allstats last'));

-------------------------------------------------------------------------------------              
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |              
-------------------------------------------------------------------------------------              
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.04 |      23 |              
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    100 |   5000 |00:00:00.03 |      23 |              
-------------------------------------------------------------------------------------              


UKJA@ukja102>
UKJA@ukja102> select /*+ gather_plan_statistics dynamic_sampling(t1 4) */ count(*)
  2  from t1
  3  where mod(c1,2) = 1
  4  ;

  COUNT(*)                                                                                         
----------                                                                                         
      5000                                                                                         


UKJA@ukja102> select * from table
  2  (dbms_xplan.display_cursor(null,null,'allstats last'));

-------------------------------------------------------------------------------------              
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |              
-------------------------------------------------------------------------------------              
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.03 |      23 |              
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   5000 |   5000 |00:00:00.02 |      23 |              
-------------------------------------------------------------------------------------              
                                                                                                   
Note                                                                                               
-----                                                                                              
   - dynamic sampling used for this statement

                                                                                         

자기가 알고 있다고 생각하는 것에 대해서는 항상 3번 이상의 질문을 던져보라고 하는데, 나 스스로가 그러지 못했음을 알 수 있다.

신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바