태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

Bind Peeking과 SQL*Trace의 조합

오라클 2009. 12. 7. 14:24
바인드 피킹(Bind Peeking)이 일으키는 가장 큰 혼란 중의 하나는 바인드 변수를 사용하는 완벽하게 동일한 SQL 문장의 실행 계획이 바뀐다는 것입니다. 그 중 재미있는 사례 한가지를 간단한 테스트 케이스를 통해 소개합니다.

아래와 같이 오브젝트를 만듭니다.

UKJA@ukja1106> create table t1(c1 int, c2 varchar2(10));

Table created.

UKJA@ukja1106> insert into t1
  2  select 1, level from dual connect by level <= 10000;

10000 rows created.

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

10000 rows created.

UKJA@ukja1106> 
UKJA@ukja1106> create index t1_n1 on t1(c1);

Index created.

UKJA@ukja1106> 
UKJA@ukja1106> exec dbms_stats.gather_table_stats(user, 't1', -
> 		method_opt=>'for columns c1 size skewonly');

PL/SQL procedure successfully completed.
컬럼 C1은 Skew되어 있고 히스토그램이 존재합니다. 통계 정보는 다음과 같습니다. Height-Balanced Histogram이 생성된 것을 알 수 있습니다.
UKJA@ukja1106> @tab_stat t1
UKJA@ukja1106> set echo off
01. table stats
old   9: 	table_name = upper(''&T_NAME'')
new   9: 	table_name = upper(''t1'')
TABLE_NAME                    : T1
PARTITION_NAME                :
NUM_ROWS                      : 20000
BLOCKS                        : 42
SAMPLE_SIZE                   : 20000
LAST_ANAL                     : 2009/12/07 13:41:08
-----------------

PL/SQL procedure successfully completed.

02. column stats
old   9: 	s.table_name = upper(''&T_NAME'')
new   9: 	s.table_name = upper(''t1'')
TABLE_NAME                    : T1
COLUMN_NAME                   : C1
NUM_DISTINCT                  : 10000
NUM_NULLS                     : 0
DENSITY                       : .00005
LOW_VALUE                     : C102
HIGH_VALUE                    : C302
HISTOGRAM                     : HEIGHT BALANCED
-----------------
TABLE_NAME                    : T1
COLUMN_NAME                   : C2
NUM_DISTINCT                  :
NUM_NULLS                     :
DENSITY                       :
LOW_VALUE                     :
HIGH_VALUE                    :
HISTOGRAM                     : NONE
-----------------

PL/SQL procedure successfully completed.

03. histogram stats
old   7: 	table_name = upper('&T_NAME')
new   7: 	table_name = upper('t1')

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------------
T1                   C1                               126 1()
T1                   C1                               127 33()
T1                   C1                               128 112()
...
T1                   C1                               254 10000()

129 rows selected.
컬럼 C1에서 "1"을 대입하면 바인드 피킹에 의해 풀 테이블 스캔을 선택하게 됩니다.
UKJA@ukja1106> var b1 number;
UKJA@ukja1106> exec :b1 := 1;
UKJA@ukja1106> var b2 varchar2(10);
UKJA@ukja1106> exec :b2 := '1';

UKJA@ukja1106> 
UKJA@ukja1106> select /*+ gather_plan_statistics */
  2  	count(*) from t1
  3  where c1 = :b1 and c2 = :b2;

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

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |     99 |      2 |
--------------------------------------------------------------

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

   2 - filter("C2"=:B2)
   3 - access("C1"=:B1)

이제 SQL*Trace를 활성화합니다. 그리고 컬럼 C1에 1이 아닌 2를 대입합니다.
UKJA@ukja1106> alter session set sql_trace = true;

Session altered.

UKJA@ukja1106> var b1 number;
UKJA@ukja1106> exec :b1 := 2;

PL/SQL procedure successfully completed.

UKJA@ukja1106> var b2 varchar2(10);
UKJA@ukja1106> exec :b2 := '1';

PL/SQL procedure successfully completed.

UKJA@ukja1106> 
UKJA@ukja1106> select /*+ gather_plan_statistics */
  2  	count(*) from t1
  3  where c1 = :b1 and c2 = :b2;

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

-------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |       |      1 |      1 |      1 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |      0 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |      1 |      1 |      1 |
------------------------------------------------------------------------- 
실행 계획이 바뀐 것을 알 수 있습니다. 이상하지 않습니까? 최초의 실행에서 바인드 피킹이 이미 이루어졌기 때문에 다음 번의 동일한 텍스트의 쿼리는 동일한 실행 계획을 보여야 합니다.

좀 더 상세한 분석을 해봅니다. 우선 현재 등록되어 있는 자식 LCO들과 그 실행 계획을 봅니다.

UKJA@ukja1106> select * from table(dbms_xplan.display_cursor('&sql_id', null, 'typical'));
old   1: select * from table(dbms_xplan.display_cursor('&sql_id', null, 'typical'))
new   1: select * from table(dbms_xplan.display_cursor('98721ruagfx5c', null, 'typical'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  98721ruagfx5c, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  count(*) from t1 where c1 = :b1
and c2 = :b2

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    18 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    99 |   792 |    18   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter(("C2"=:B2 AND "C1"=:B1))

SQL_ID  98721ruagfx5c, child number 1
-------------------------------------
select /*+ gather_plan_statistics */  count(*) from t1 where c1 = :b1
and c2 = :b2

Plan hash value: 359681750

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     8 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - filter("C2"=:B2)
   3 - access("C1"=:B1)
확실히 두 개의 실행 계획이 존재합니다. 이것이 의미하는 것은 SQL*Trace가 활성화되면 오라클은 동일한 SQL 문장이라고 하더라도 다른 SQL 문으로 인식한다는 것을 의미합니다. 그 이유를 V$SQL_SHARED_CURSOR 뷰를 통해서 볼 수 있습니다. 제가 작성한 간단한 스크립트로 V$SQL_SHARED_CURSOR 뷰를 조회해 봅니다.
UKJA@ukja1106> @shared_cursor2 &sql_id
UKJA@ukja1106> set echo off
old  14:           and s.sql_id = ''&1''',
new  14:           and s.sql_id = ''98721ruagfx5c''',
SQL_TEXT                       = select /*+ gather_plan_statistics */  count(*) from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 98721ruagfx5c
ADDRESS                        = 29D81B30
CHILD_ADDRESS                  = 2879E2BC
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /*+ gather_plan_statistics */  count(*) from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 98721ruagfx5c
ADDRESS                        = 29D81B30
CHILD_ADDRESS                  = 2F5E79DC
CHILD_NUMBER                   = 1
STATS_ROW_MISMATCH             = Y
--------------------------------------------------
즉, SQL*Trace가 할성화되면 STATS_ROW_MISMATCH에 의해 SQL 문장을 공유하지 못하고 새롭게 Child Cursor를 만들게 됩니다. 그 과정에서 다시 바인드 피킹이 이루어진 것입니다.

SQL*Trace의 결과만을 보고 성급히 튜닝을 해서는 안되겠구나하는 두려움이 들지 않으세요? ^^

Oracle 11g 기준으로 동일한 SQL 문장이 공유되지 못하는데는 무려 60여가지의 이유가 존재합니다.

UKJA@ukja1106> desc v$sql_shared_cursor                       
           Name                                               
           ---------------------------------------------------
    1      SQL_ID                                             
    2      ADDRESS                                            
    3      CHILD_ADDRESS                                      
    4      CHILD_NUMBER                                       
    5      UNBOUND_CURSOR                                     
    6      SQL_TYPE_MISMATCH                                  
    7      OPTIMIZER_MISMATCH                                 
    8      OUTLINE_MISMATCH                                   
    9      STATS_ROW_MISMATCH                                 
   10      LITERAL_MISMATCH                                   
   11      FORCE_HARD_PARSE                                   
   12      EXPLAIN_PLAN_CURSOR                                
   13      BUFFERED_DML_MISMATCH                              
   14      PDML_ENV_MISMATCH                                  
   15      INST_DRTLD_MISMATCH                                
   16      SLAVE_QC_MISMATCH                                  
   17      TYPECHECK_MISMATCH                                 
   18      AUTH_CHECK_MISMATCH                                
   19      BIND_MISMATCH                                      
   20      DESCRIBE_MISMATCH                                  
   21      LANGUAGE_MISMATCH                                  
   22      TRANSLATION_MISMATCH                               
   23      ROW_LEVEL_SEC_MISMATCH                             
   24      INSUFF_PRIVS                                       
   25      INSUFF_PRIVS_REM                                   
   26      REMOTE_TRANS_MISMATCH                              
   27      LOGMINER_SESSION_MISMATCH                          
   28      INCOMP_LTRL_MISMATCH                               
   29      OVERLAP_TIME_MISMATCH                              
   30      EDITION_MISMATCH                                   
   31      MV_QUERY_GEN_MISMATCH                              
   32      USER_BIND_PEEK_MISMATCH                            
   33      TYPCHK_DEP_MISMATCH                                
   34      NO_TRIGGER_MISMATCH                                
   35      FLASHBACK_CURSOR                                   
   36      ANYDATA_TRANSFORMATION                             
   37      INCOMPLETE_CURSOR                                  
   38      TOP_LEVEL_RPI_CURSOR                               
   39      DIFFERENT_LONG_LENGTH                              
   40      LOGICAL_STANDBY_APPLY                              
   41      DIFF_CALL_DURN                                     
   42      BIND_UACS_DIFF                                     
   43      PLSQL_CMP_SWITCHS_DIFF                             
   44      CURSOR_PARTS_MISMATCH                              
   45      STB_OBJECT_MISMATCH                                
   46      CROSSEDITION_TRIGGER_MISMATCH                      
   47      PQ_SLAVE_MISMATCH                                  
   48      TOP_LEVEL_DDL_MISMATCH                             
   49      MULTI_PX_MISMATCH                                  
   50      BIND_PEEKED_PQ_MISMATCH                            
   51      MV_REWRITE_MISMATCH                                
   52      ROLL_INVALID_MISMATCH                              
   53      OPTIMIZER_MODE_MISMATCH                            
   54      PX_MISMATCH                                        
   55      MV_STALEOBJ_MISMATCH                               
   56      FLASHBACK_TABLE_MISMATCH                           
   57      LITREP_COMP_MISMATCH                               
   58      PLSQL_DEBUG                                        
   59      LOAD_OPTIMIZER_STATS                               
   60      ACL_MISMATCH                                       
   61      FLASHBACK_ARCHIVE_MISMATCH                         
   62      LOCK_USER_SCHEMA_FAILED                            
   63      REMOTE_MAPPING_MISMATCH                            
   64      LOAD_RUNTIME_HEAP_FAILED                           
이렇게 많은 이유가 존재하니 동일한 SQL 문장이 복수 개의 실행 계획을 보일 때 이 뷰를 관찰하지 않으면 안되겠죠? 찍어도 맞을 확률은 1/60 = 1.7% 밖에 되지 않습니다.

'오라클' 카테고리의 다른 글

Write Consistency?  (1) 2009.12.14
오라클 성능에 대한 짧은 생각 #18  (1) 2009.12.13
Bind Peeking과 SQL*Trace의 조합  (7) 2009.12.07
오라클 성능에 대한 짧은 생각 #17  (0) 2009.12.06
버그 5364143 - Bind Peeking의 문제  (0) 2009.12.03
Trackbacks 0 : Comments 7
  1. 강정식 2009.12.07 17:39 Modify/Delete Reply

    욱짜님. 이번에도 좋은 내용 감사드립니다 ^^

    요즘 스터디하고 있는 책 '오라클 성능 고도화 원리와 해법I'에 보면 child cursor를 공유하지 못하는 이유를 아래와같이 설명하고 있더라구요.

    1) SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때
    2) 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드파싱돼야 하는데, 특정 세션이 아직 기존 커서를 사용중(pin)일 때
    3) 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
    4) 입력된 바인드 값의 길이가 크게 다를 떄
    5) NLS 파라미터를 다르게 설정했을 때
    6) SQL 트레이스를 활성화했을 때

    이중 4), 6)번에 대해 처음 알게 되었는데 욱짜님이 6)번에 대해 다루신거 같아 또 다른 예제를 보게되어 좋았습니다 ^^

    4)번은 1, 32, 128, 2000 바이트를 넘을 때 child cursor를 생성하는걸 첨 알았는데 그나마 4개밖에 나누어져 있지 않아 다행이라고 생각이 드는 항목이었습니다.

    • 욱짜 2009.12.07 15:36 신고 Modify/Delete

      아마 흔히 보이는 경우를 여섯 개 정도로 정리한 거 같은데요?

      4번)에 대해서는 1,32,128,2000의 네개로 나누어진다는 것이 일반적인 설명인데 실제로는 버전에 따라 조금씩 다른 것 같더라구요. 어떤 경우는 2개로 구분되는 것 같기도 하고...최대 4개다고 이해하시면 좋을 것 같습니다.

  2. 강정식 2009.12.07 17:41 Modify/Delete Reply

    네. 제가 10.2.0.3에서 테스트 해봤을 때는 1, 32, 128, 2000으로 된걸 확인했습니다.
    다만 다른 버전에서는 확인을 못해봤는데 버전별로 약간 틀린거군요.

  3. 유수익 2009.12.08 15:46 Modify/Delete Reply

    여기에 질문하는것이 맞지 않으면 삭제해주세요.
    select /*+ gather_plan_statistics */ count(*) from t1
    where c1 = :b1 and c2 = :b2; 에서 e-rows가 99건이 나왔는데 어떻게 나왔는지 모르겠네요.
    c1 = :b1 and c2 = :b2이면 Selectivity(b1) * Selectivity(b2) = (20000 * 0.0005) X (20000 * 5% rule) = 1000
    인데 통계정보가 없으면 5% rule이 적용되지 않나요? 어떻게 해서 99건이 나왔죠?

    • 욱짜 2009.12.08 16:08 신고 Modify/Delete

      날카로운 지적입니다!

      Plan Statistics를 통해 나온 예측 로우 건수는 Explain Plan이 아닌 Optimizer가 Bind Peeking을 통해 실제로 값을 얻은 후의 예측입니다. 따라서 마치 Literal을 사용한 것과 같이 계산됩니다.

      c1 = 1 조건:
      Height-Balanced Histogram이 있습니다. 1의 값은 254개의 Bucket 중 총 126개의 Bucket을 사용합니다. 따라서
      density(c1=1) = 126/254

      c2 = '1' 조건:
      Column c2에 대해서는 통계 정보가 없습니다. 이런 경우에 한해서 오라클은 1%(0.01)의 Magic Number를 사용합니다.

      따라서 예측 로우건수는

      20000 * 126/254 * 0.01 = 99.21 = 99가 됩니다.

  4. 소드피쉬 2010.02.16 16:55 Modify/Delete Reply

    내용과 다르게 좀 엄한 질문입니다만...

    v$sql_shared_cursor 뷰에서 child_address 가 변경되는경우가 위에서 말씀하신대로 60여가지 이유 혹은
    다른 오너가 쿼리를 호출해도 child_number 와 child_address 가 변경되는것 같은데

    그런 이유와 별개로 첨부터 address 와 child_address 의 값이 다른 이유는 무엇입니까?

    • 욱짜 2010.03.02 13:59 신고 Modify/Delete

      "다른 오너가 쿼리를 호출해도"라는 표현을 좀 더 명확하게 정의할 필요가 있겠습니다. 가령 Schema A가 Table A를 가지고 있고, Schema B가 Table A를 각각 가지고 있는 경우를 의미하신다면, 아마 TRANSLATION_MISMATCH(혹은 AUTH_CHECK_MISMATCH)로 인해 Child Cursor가 생성될 것입니다.

      ADDRESS는 Parent Cursor의 Address이고, CHILD_ADDRESS는 Child Cursor의 Address입니다. 따라서 값이 다를 수 밖에 없습니다.
      http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2124.htm#REFRN30254
      SQL Cursor은 Parent/Child 관계를 가지는데, 설령 Child Cursor가 하나밖에 없다고 하더라도 Parent/Child로 관리가 됩니다.

Write a comment