태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

DYNAMIC_SAMPLING 힌트의 정체

오라클 2008.03.06 17:25
오라클이 통계 정보가 없는 테이블에 대해 실행 계획을 수립할 때 Dynamic Sampling을 수행한다는 것은 널리 알려진 사실이다. 그렇다면 통계 정보가 항상 최신 정보를 잘 유지한다면 Dynamic Sampling은 불필요한 것인가?

예상과 달리 그렇지 않다. 통계 정보가 잘 수집되었다 하더라도 Dynamic Sampling은 여전히 필요하다. 더 정확하게 말하면 Dynamic Sampling은 가장 훌륭한 튜닝 도구중 하나이다.

아래 예를 보자.


UKJA@ukja10> select * from v$version;
BANNER                                                                   ------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production              
CORE    10.2.0.1.0    Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 -  Production

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> -- create objects
UKJA@ukja10> drop table t_dynamic purge;

Table dropped.

Elapsed: 00:00:00.28
UKJA@ukja10>
UKJA@ukja10> create table t_dynamic(id int, c1 varchar2(100));

Table created.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- generate some data
UKJA@ukja10> insert into t_dynamic
  2  select rownum, object_name||'_$@' || lvl
  3  from user_objects,
  4  (select level as lvl, rownum as r from dual
  5           connect by level <= 1000 order by dbms_random.random)
  6  where r <= 100;

8600 rows created.

Elapsed: 00:00:00.04
UKJA@ukja10>
UKJA@ukja10> insert into t_dynamic select * from t_dynamic;

8600 rows created.

Elapsed: 00:00:00.01
UKJA@ukja10> insert into t_dynamic select * from t_dynamic;

17200 rows created.

Elapsed: 00:00:00.03
UKJA@ukja10> insert into t_dynamic select * from t_dynamic;

34400 rows created.

Elapsed: 00:00:00.03
UKJA@ukja10> insert into t_dynamic select * from t_dynamic;

68800 rows created.

Elapsed: 00:00:00.06
UKJA@ukja10>
UKJA@ukja10> -- create index and gather stats
UKJA@ukja10> create index t_dynamic_idx on t_dynamic(id);

Index created.

Elapsed: 00:00:00.23
UKJA@ukja10> -- 통계 정보가 수집되므로 Dynamic Sampling이 작동하지 않는다.
UKJA@ukja10> exec dbms_stats.gather_table_stats(user, 't_dynamic', -
       cascade=>true,-

       no_invalidate=>false);

PL/SQL procedure successfully completed.

위와 같이 통계 정보가 잘 수집된 이후에는 해당 테이블을 액세스하는 모든 쿼리는 Dynamic Sampling 과정이 없이 수행된다.

UKJA@ukja10>
UKJA@ukja10> -- Predicate가 없는 단순 Select
UKJA@ukja10> select /*+ gather_plan_statistics */
  2           count(*)
  3  from
  4           t_dynamic
  5  ;

COUNT(*)                                                                
----------
137600

Elapsed: 00:00:00.03
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                       ------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.02 |     562 |
|   2 |   TABLE ACCESS FULL| T_DYNAMIC |      1 |    136K|    137K|00:00:00.41 |     562 |
------------------------------------------------------------------------------------------
                                                                       
13 rows selected.
Elapsed: 00:00:00.01

위의 예에서는 예측 로우수(136K)와 실제 로우수(137K)가 거의 일치한다. 좋은 상태다.


UKJA@ukja10> -- Like 검색을 추가한다. 
UKJA@ukja10> select /*+ gather_plan_statistics */
  2 count(*)      
  3  from                  
  4           t_dynamic                      
  5  where                          
  6           c1 like '%T%'           
  7  ;                          

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

89600                                                                  
                                                                       
Elapsed: 00:00:00.12                                                                     

UKJA@ukja10>                                                           
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));  

                                                                       
PLAN_TABLE_OUTPUT                                                      
------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.14 |     562 |
|*  2 |   TABLE ACCESS FULL| T_DYNAMIC |      1 |   6840 89600 |00:00:00.45 |     562 |
------------------------------------------------------------------------------------------
                                                                       
Predicate Information (identified by operation id):                     ---------------------------------------------------                       
2 - filter("C1" LIKE '%T%')

                                                                       
19 rows selected.                        


단 하나의 Like 검색 추가만으로 예측 로우수(6840)와 실제 로우수(89600)가 크게 어긋나기 시작한다. 오라클은 Like 조건이 추가되면 예측 로우수를 기존의 5%로 줄여버린다. 이것은 CBO에 의해 고정된 값으로, 통계상 5% 정도만이 조건을 만족하지 않을까라고 가정해버리는 것이다. 오라클의 단점이라기보다는 "예측"이라는 과정에서 오는 필연적인 오류인셈이다.

Like 검색 조건을 늘려가면 사태는 더욱 악화된다.

UKJA@ukja10>
UKJA@ukja10> -- Like 조건이 2개
UKJA@ukja10> select /*+ gather_plan_statistics */
  2           count(*)
  3  from
  4           t_dynamic
  5  where
  6           c1 like '%T%' and
  7           c1 like '%_%'
  8  ;

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

89600
            

Elapsed: 00:00:00.17
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                       ------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.17 |     562 |
|*  2 |   TABLE ACCESS FULL| T_DYNAMIC |      1 |    342 89600 |00:00:00.45 |     562 |
------------------------------------------------------------------------------------------
                                                                       
Predicate Information (identified by operation id):
---------------------------------------------------                       
2 - filter(("C1" LIKE '%T%' AND "C1" LIKE '%_%'))


19 rows selected.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> -- Like 조건이 3개
UKJA@ukja10> select /*+ gather_plan_statistics */
  2           count(*)
  3  from
  4           t_dynamic
  5  where
  6           c1 like '%T%' and
  7           c1 like '%_%' and
  8           c1 like '%$%'
  9  ;

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

89600

Elapsed: 00:00:00.25
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                      
------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.25 |     562 |
|*  2 |   TABLE ACCESS FULL| T_DYNAMIC |      1 |     17 89600 |00:00:00.54 |     562 |
------------------------------------------------------------------------------------------
                                                                       
Predicate Information (identified by operation id): ---------------------------------------------------                        
2 - filter(("C1" LIKE '%T%' AND "C1" LIKE '%_%' AND "C1" LIKE '%$%'))

                                                                       
19 rows selected.

위의 결과를 보면 Like 조건이 세 개 정도가 되면 예측 로우수가 거의 의미가 없게 된다. 이런 결과의 문제점은 아래 쿼리에서 비극적인 결과를 낳게 된다.


UKJA@ukja10>
UKJA@ukja10> -- Like 조건이 3개이면서 Join을 수행
UKJA@ukja10> select /*+ gather_plan_statistics */
  2           count(*)
  3  from
  4           t_dynamic t1, t_dynamic t2
  5  where
  6           t1.id = t2.id and
  7           t1.c1 like '%T%' and
  8           t1.c1 like '%_%' and
  9           t1.c1 like '%$%'
 10  ;

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

1433600

Elapsed: 00:00:02.67
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                       -----------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |               |      1 |      1 |      1 |00:00:02.67 |   93284 |
|   2 |   NESTED LOOPS      |               |      1 |    272 |   1433K|00:00:38.71 |   93284 |
|*  3 |    TABLE ACCESS FULL| T_DYNAMIC     |      1 |     17 |  89600 |00:00:00.54 |     562 |
|*  4 |    INDEX RANGE SCAN | T_DYNAMIC_IDX |  89600 |     16 |   1433K|00:00:04.99 |   92722 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------                       
   3 - filter(("T1"."C1" LIKE '%T%' AND "T1"."C1" LIKE '%_%' AND "T1"."C1" LIKE '%$%')) 

   4 - access("T1"."ID"="T2"."ID")  

22 rows selected.

Elapsed: 00:00:00.01

위의 결과를 보면 t_dynamic 테이블을 풀스캔으로 하는 Nested Loop Join이 수행된다. 왜? t_dynamic에 대한 예측 로우수가 불과 17건에 불과하기 때문에 Nested Loop가 훨씬 유리하다고 판단해버리는 것이다. 결과는 비극적이다. t_dynamic 테이블을 Full Scan하면서 해당건수(
89600)만큼 Index Scan(Random I/O)를 수행해야하기 때문이다.

우리는 경험상 이런 경우에는 Hash Join이 훨씬 유리하다는 것을 알고 있다. 하지만 Optimizer는 통계 정보 해석의 오류때문에 이 사실을 알 수 없다.

이런 경우 해결책은?
이 경우에 USE_HASH 힌트를 사용하는 것은 정답이라고 할 수 없다. 현재의 케이스 하나는 해결이 가능하겠지만, Like 조건 값에 따라 NL Join이 더 유리하다면? 역시 비극적인 결과를 낳고 만다.

Dynamic Sampling이 정답이다. 비록 통계 정보가 있지만, 이미 수집된 통계 정보로는 정확한 정보를 추출할 수 없다면 Query가 Parse되는 시점에 Dynamic Sampling을 수행해서 비교적 정확한 정보를 얻을 수 있다. Dynamic Sampling에 의한 오버헤드를 감수하면서 최적의 실행 계획을 얻을 수 있다.

아래 테스트 결과를 보자.


UKJA@ukja10>
UKJA@ukja10> select /*+ gather_plan_statistics
  2           dynamic_sampling(t1 2) */
  3           count(*)
  4  from
  5           t_dynamic t1, t_dynamic t2
  6  where
  7           t1.id = t2.id and
  8           t1.c1 like '%T%' and
  9           t1.c1 like '%_%' and
 10           t1.c1 like '%$%'
 11  ;

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

1433600      


Elapsed: 00:00:00.71
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                       --------------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |               |      1 |      1 |      1 |00:00:00.68 |     857 |
|*  2 |   HASH JOIN            |               |      1 |   1416K|   1433K|00:00:10.11 |     857 |
|   3 |    INDEX FAST FULL SCAN| T_DYNAMIC_IDX |      1 |    136K|    137K|00:00:00.41 |     295 |
|*  4 |    TABLE ACCESS FULL   | T_DYNAMIC     |      1 |  89073 |  89600 |00:00:00.54 |     562 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------                        
   2 - access("T1"."ID"="T2"."ID")                  

   4 - filter(("T1"."C1" LIKE '%T%' AND "T1"."C1" LIKE '%_%' AND "T1"."C1" LIKE '%$%'))
                                                                                         
Note                                                                   
-----                                                                  

   - dynamic sampling used for this statement            

                                                                       
26 rows selected.


Elapsed: 00:00:00.01

결과는 매우 극적이다. Dynamic Sampling 덕분으로 매우 정확한 로우수를 예측할 수 있고 그로 인해 가장 이상적인 Hash Join이 선택된 것을 알 수 있다.

Dynamic Sampling은 통계 정보가 없는 테이블에 대한 쿼리를 도와주는 보조적이 역할에 그치지 않다는 것을 위의 예는 잘 보여준다. 오히려 통계 정보에 대한 해석의 오류에서 오는 실행 계획 이상 현상을 방지할 수 있는 훌륭한 도구가 될 수 있다.

특히 DW 환경이나 리포트성 Query에서 잘 활용하면 경우에 따라 힌트를 따로 적용하는 등 쿼리를 분기시켜 작성하는 수고로움을 덜 수 있을 것이다.


Trackback 0 : Comments 8
  1. oracler 2008.03.20 20:19 신고 Modify/Delete Reply

    아래와 같이 기간과 코드값이 바인드 변수로 묶여서 테이블 조인이 일어나는 쿼리가 있습니다.
    prg_date between :start_dt and :end_dt
    and bbs_id = :bid

    바인드변수로 묶인 경우 bid 값이나 기간 범위에 따라 nl, hash 조인이
    "실행시마다 변수값에 따라 동적으로 계속 바뀌어야" 최적의 플랜이라고 할때.

    첫번째 실행시엔 다이나믹 힌트로 나름대로 정확한 플랜이 나옵니다만
    변수값을 바꿔, 두번째 연달아서 실행시엔 SQL 문장 파싱을 다시 하지 않고
    (샘플링을 다시 하지 않고) 직전의 플랜으로 실행되어 비극이 됩니다.
    이런 경우는 어떻게 하나요 ?

    다이나믹 힌트는 바인드 변수값이 바뀌면 매번 샘플링을 다시 해서
    플랜을 다시 수립해야만 하므로 이 힌트의 특성상
    기존 오라클의 SQL 실행과정(하드파싱-소프트파싱-플랜수립의일련의 과정)
    과는 예외로 달라져야만이 사용하는 의미가 있게됩니다.
    그렇지 않고 변수값이 바뀌었는데도 직전 실행계획을 따라버리면
    아무 의미가 없게되는데요..?

  2. oracler 2008.03.20 20:21 신고 Modify/Delete Reply

    앗 이런 실례가,, 질문하느라 인사를 먼저 못드렸습니다.
    가끔씩 오는데 고급정보 올려주셔서 감사드립니다.
    강력한 포스가 느껴지는 블로그네요
    항상 행복하세요..^^

  3. 욱짜 2008.03.20 22:38 신고 Modify/Delete Reply

    말씀하신대로 Dynamic Sampling은 바인드 변수와는 잘 어울리지 않습니다. Dynamic Sampling Hint를 주더라도 SQL 텍스트가 완벽하게 동일하다면 추가적인 하드 파싱(Optimization)이 발생하지 않으므로 바인드 변수를 사용하는 경우에는 Dynamic Sampling의 장점이 무색해질 수 밖에 없을거 같습니다.

    이런 이유 때문에 Dynamic Sampling Hint는 Parse시간이 Execute시간에 비해 상대적으로 매우 저렴한 DW 환경에서만 의미가 있고 대부분 Literal 값을 사용하는 것이 권장됩니다. 거꾸로 바인드 변수 사용이 절대적인 OLTP 환경에서는 Parse시간이 중요하기 때문에 Dynamic Sampling을 사용해서는 안되구요.

  4. oracler 2008.03.21 22:27 신고 Modify/Delete Reply

    욱짜님 답변 대단히 감사합니다.

    역시 추측대로 다이나믹은 바인드변수와는 어울리지 않고
    literal 의 경우에만 권장된다는 말씀이신데요.

    그렇다면
    (통계정보 있는 경우) literal 로 사용했을땐
    다이나믹 힌트 안쓰고도, 최적의(?) 실행계획이 나오게 되는데
    굳이 시간만 까먹는 다이나믹 쓸 이유가 없다고 봅니다.

    실행시 literal 값이 바뀐 쿼리가 들어오면 어차피 하드파싱부터해서
    플랜 다시 만들거니까 다이나믹 없어도 최적화됩니다.
    이 상황이라면 다이나믹 쓰나 안쓰나 차이가 없을거 같은데요 ?
    괸히 시간만 까먹는거죠.

    그렇다면 결론은.
    "(테이블에 통계정보가 없거나 or 통계정보를 믿을수 없을때)
    and
    (literal 을 쓸때에 한해서만) 다이나믹 힌트가 의미가 있다."
    가 아닐까요 ?

    다른 말로 하자면, 통계정보를 주기적으로 착실히 수집하고 있는 곳에서는
    다이나믹을 써서는 안된다.
    (쓸 필요가 없다가 아니라 써서는 안되는것)이 되구요.

    아무때나 써서 그 효능을 기대했다가는 오히려 역효과가 나는것이
    다이나믹이라 생각됩니다.

  5. 욱짜 2008.03.21 23:32 신고 Modify/Delete Reply

    이미 통계정보가 수집된 테이블들에 대한 쿼리에서 dynamic_sampling 힌트가 필요한 경우는 다음과 같이 요약할 수 있습니다.

    - 통계 정보의 한계로 인해 CBO가 제대로 된 예측을 할 수 없을 때

    위의 글에서 제가 만든 예제가 여기에 해당합니다. 통계 정보를 매우 정확하게 수집했음에도 불구하고 CBO가 비효율적인 실행 계획을 만듭니다. 그 이유는 통계 정보가 잘못된 것이 아니라 통계 정보 자체의 한계 때문입니다. 가령 통계 정보가 있다고 하더라도 Like '%XXX%' 류의 계산은 항상 오류의 위험이 있습니다. Oracle이 수집하는 컬러 통계 정보로는 Like '%XXX%'의 Selectivity를 예측하지 못하기 때문입니다.(그래서 항상 5%를 곱해버립니다)

    이 때 사용할 수 있는 것이 Dynamic Sampling입니다. Dynamic Sampling을 사용하면 Sampling 과정에서 실제 Predicate 조건에 들어간 값을 이용해 예측하는 효과가 발생합니다. 이로 인해 비교적 정확한 예측을 할 수 있게 됩니다. 위의 예에서도 Dynamic Sampling으로 인해 Like '%XXX%' 조건에 대한 예측 로우수가 상당히 정확해진 것을 알 수 있습니다.

    즉, 통계 정보를 주기적으로 착실히 수집하고 있는 곳에서도 Dynamic Sampling이 반드시 필요한 때가 있다는게 맞는 표현입니다. 물론 힌트로도 같은 효과를 볼 수 있지만, 힌트는 실행 계획을 고정시켜버리는 반면에 Dynamic Sampling은 상황에 따라 적절한 실행 계획을 생성하는 장점이 있습니다.

    항상 주의할 것은 Dynamic Sampling 과정 자체가 왠만한 쿼리를 수행하는 정도의 부하를 가지고 있기 때문에 DW성의 대용량 Query에서만 사용되어야 한다는 겁니다.

    (참고로) SQL Server 2005는 Like '%XXX%' 류의 조건에 대해서도 통계 정보를 수집한다고 합니다. 특허를 냈다고 하는데... 믿거나 말거나...

  6. oracler 2008.03.22 18:21 신고 Modify/Delete Reply

    그러면 dynamic 은 딱 두가지로 요약되는군요.

    1. 바인드 변수 사용시 다이나믹 힌트 사용 금지.
    (예상못한 역효과 발생가능)

    2. literal 변수이고, 조인되는 테이블이 있으며,
    LIKE %AA% 검색등 통계정보로 예측이 힘든경우일때 사용.
    힌트자체 오버헤드를 감수할수 있는 환경/가능하면 대용량 처리시 사용, OLTP 에서는 자제

    ======
    like %% 과 비슷하게 쓰일수 있는 instr 함수는 어떻게 될지
    테스트는 안해봤네요.

    다이나믹은 최적의 사용조건도 참 까다로운 편이고
    섣불리 쓰다간 오히려 역효과가 날수도 있으니 선무당이 사람잡는다고 정확히 용법을 알고 써야겠습니다.
    대단히 감사합니다.

  7. KT 2008.12.22 12:31 신고 Modify/Delete Reply

    통계정보가 없는 경우.. dynamic sampling을 사용하게 된다면, optimizer mode와 상관없이 모두 그렇다는 건가요? 즉, 시스템 파라미터인 optimizer_dynamic_sampling가 설정되어 있는 경우는 rule base 모드인 경우를 제외하고는 통계정보 없는 경우 무조건 dynamic sampling 수행한다는 의미인지요?
    그렇다면 overhead가 너무 크지 않을까 싶네요.

  8. 욱짜 2008.12.22 13:10 신고 Modify/Delete Reply

    Oracle 9i에서는 Optimizer Mode가 choose죠.
    즉, 통계 정보가 없으면 Rule Based로 동작합니다.

    반면, Oracle 10g에서는 Optimizer Mode가 all_rows죠?
    따라서 통계 정보가 없으면 Dynamic Sampling을 수행하게 됩니다.

    10g에서도 Optimizer Mode를 choose나 rule로 변경하면 자동적인 Dynamic Sampling은 발생하지 않습니다.

    Dynamic Sampling이 발생하더라도 Hard Parse가 이루어지는 시점에만 발생하므로 Overhead가 크게 문제가 되지 않을 수도 있습니다.

    단, /*+ dynamic_sampling(...) */ Hint를 사용하는 경우에는 파라미터 값에 무관하게 Sampling이 이루어집니다.

Write a comment

티스토리 툴바