'2008/12'에 해당되는 글 2건

  1. 2008.12.18 About DBMS_XPLAN - 1.실행계획 (9)
  2. 2008.12.12 InList / Concatnation / Range Scan Control 하기 (4)

최근의 많은수의 사람들이 DBMS_XPLAN 패키지를 사용하여 튜닝을 하고 있다.
필자는 DBMS_XPLAN 패키지에 대한 너무많은 질문공세 때문에 아예 블로그에 올릴 결심을 하였다.
오늘은 DBMS_XPLAN 패키지에 대한 첫번째 이야기로 가장중요한 실행계획에 대하여 조목조목 따져보려고 한다.

DBMS_XPLAN 패키지는 9i 부터 점점 발전하여 지금은 Trace + tkprof 보고서와 자웅을 겨룰 정도로 발전하고
있다.
DBMS_XPLAN 패키지내의 함수는 10g R2 기준으로 6개 이지만 가장 자주 사용하는 함수는 아래의 3가지 이다.

1.DISPLAY                 --> 예측 실행계획을 보여준다.
2.DISPLAY_CURSOR   --> 실제 실행된 실행계획을 보여준다.
3.DISPLAY_AWR         --> 실제 실행된 실행계획을 보여준다.

오늘의 주제는 실행계획상의 각항목에 대한 설명이므로 3개의 함수에 대한 자세한 설명은 다음에 계속하여
연재할 계획이다.

실행계획은 패키지 내의 3가지 함수(display, display_cursor, display_awr)를 통해 모두 조회가 가능하다.

아래의 스크립트는 display_awr 의 예제이며 sql_id 만 구하면 언제든지 실행될수 있다.

select * from table(dbms_xplan.display_awr(:v_sql_id,null,null,'advanced allstats last'));



사용자 삽입 이미지





위 PLAN 은 DBMS_XPLAN 패키지의 format 항목을 Advanced 로 했을 경우에 나타나는 Plan 의 모습이다.
아래는 위의 Plan 항목 하나하나에 대한 자세한 설명이다.
물론 위의 예제는 실행계획의 모든 항목이 나온것은 아니다.
예를 들면 파티션테이블을 사용하지 않았으므로 Partiton 관련 항목이 빠진것이다.

DBMS_XPLAN 패키지의 실행계획의 항목은 아래처럼 크게 7개로 나눌수 있다.

주의사항:

파랑색부분( 5), 6), 7)번 에해당됨)은 실행통계가 있을경우만 해당된다.

display_cursor, display_awr 의  경우 statistics_level 파라미터를 all 로 설정하거나 SQL 에 gather_plan_statistics 를 사용한경우 실행통계를 볼수 있다.

 

1)Basics 항목 (Always Available)

Id                : Operation ID .

                  * 가 달려있는 경우는 predicate 정보에 access filter 에 관한정보가 나옴을 표시한것임.

Operation  : 각각 실행되는 JOB 을 나타냄

                    row source operation.의 줄임말임.

Name          : Operation 이 엑세스하는 테이블 및 인덱스를 나타냄.

2)Query Optimizer Estimations(옵티마이져의 예상 row 수 및 bytes, cost , temp 사용량)

Rows (E-Rows)  : operation 이 끝났을 때 return되는 건수를 나타냄.

                              이것은 예측 건수 이므로 실제 건수와는 다름.

Bytes (E-Bytes) : operation return byte .
                             
예상치 이므로 실제 받은 byte와는 다름

TempSpc             : operation. temporary space 를 사용한 양(예상치임)

Cost (%CPU)      : operation Cost. (예상치 임)

                              괄호안의 내용은 CPU Cost 의 백분율임.

                               이값은 Child Operation Cost 를 합친 누적치임.

Time                     : 예측 수행시간

 

3)Partitioning (파티션을 엑세스 할경우만 나타남)

Pstart  : 파티션을 엑세스 하는경우 시작파티션을 나타냄

             상수로 들어올때는 파티션 번호로 나타나며 변수로 들어올때는 KEY 로 나타남

Pstop  : 마지막 파티션을 나타냄.

              따라서 patart, pstop 를 이용하면 access 한 파티션을 알수 있음. 


4)Parallel and Distributed Processing (Parallel Processsing
을 사용하거나 DB-LINK 를 사용하는경우)

Inst         :  DB-LINK (사용하는 경우만 나타남).

TQ            :  PARALLEL SQL 사용시 table queue 명을 나타냄
                   
TQ PARALLEL SLAVE 간의 통신을 담당함.

IN-OUT      :  Parallel processing 시에 각각의 Operation 이 Serial 로 실행되는지 parallel 로 진행되는지를
                     나타냄.

PQ Distrib :  Parallel processing 시에 producers 와 consumers 간의 데이터의 분배방식을 나타냄.


* 이부분의 자세한 내용은 아래를 참조하기 바란다.
1.http://scidb.tistory.com/entry/Parallel-Query-의-조인시-Row-Distribution
2.http://scidb.tistory.com/entry/Parallel-Query-의-조인시-또다른-튜닝방법pxjoinfilter 


5)Runtime Statistics (
실제 수행시간밑 실제수행건수)

Starts     : operation try 한 건수(예를 들어 nested loop join 이라면 인덱스를 여러 번 scan )

A-Rows  : operation return 한 건수

A-Time   : 실제 실행시간

0.1초까지 나타남 (HH:MM:SS.FF).

                 이값은 Child Operation Cost 를 합친 누적치임.             
 

6)I/O Statistics (I/O 관련하여 READ / WRITE 한 블록수)

Buffers  : Operation 이 메모리에서 읽은 block .

Reads   : Operation disk 에서 읽은 block .

Writes   : Operation disk write block .

 

7)Memory Utilization Statistics(hash 작업이나 sort 작업시 사용한 메모리 통계)

OMem         : optimal execution 에 필요한 메모리(예측치임).

1Mem          : one-pass execution. 에 필요한 메모리(예측치임)

O/1/M        : operation 이 실행한 optimal/one-pass/multipass 횟수가 순서대로 표시됨.

Used-Mem : 마지막 실행시의 사용한 메모리

Used-Tmp  : 마지막 실행시 메모리가 부족하여 temporary space 를 대신 사용할 때 나타남.

                      보이는값에 1024 를 곱해야함.
 
                     예를들어 32K
로 나타나면 32MB 를 의미함.

Max-Tmp    : 메모리가 부족하여 temporary space 를 사용할 때 최대 temp 사용량임.

                     USED-TMP 와 다른점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을경우에 
                     항상 최대값만 보인다는 것이다.
                     보이는값에 1024 를 곱해야함.
                     예를들어 32K 로 나타나면 32MB 를 의미함.

결론:
이상으로 PLAN 상에 나오는 각 항목에 대하여 빠짐없이 알아보았다.
특히 Runtime 통계,  I/O 통계및 Memory 통계중의 일부항목은 Tkprof 보고서에도 나오지않는 정보들로
튜닝시 요긴하게 사용할수 있다는 점을 기억 해야 한다.  
다음시간에는 3가지 함수의 여러가지 옵션에 대하여 알아볼것이다.

Reference :
1.Ttroubleshooting Oracle Performance (Christian Antognini)
2.Oracle 10g Manual : PLSQL Packages and Types Reference

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.08 15:02 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 동규님 ^^ 강정식입니다.
    XPLAN에서 여쭤볼것이 있어서 이렇게 질문을 남깁니다.

    제가 'XPLAN과 10046 event를 이용한 튜닝방법' 세미나를 준비중이라
    DBMS_XPLAN.DISPLAY(이하 DISPLAY), DBMS_XPLAN.DISPLAY_CURSOR(이하 CURSOR)
    2개를 테스트 해 보고 있습니다.

    그런데 ADVANCED Format을 통해 나온 Query Block Name(이하 블럭)을 이용하여
    인덱스를 변경하는 건을 테스트 하다가 생각지도 못한 Case가 발견되어 질문을 드립니다.

    이야기로 풀어 정리를 하자면 DISPLAY를 통해 나온 블럭을 이용하여 힌트를 교정한 뒤
    CURSOR로 수행을 해보니 그 힌트가 적용되지 않았습니다.

    하여 그 원인을 확인해본 결과 Predicate Information에서 바인드 변수가 TO_NUMBER(:B1)로
    바뀌어서 들어가는데 이로 인해 DISPLAY 할 때와 CURSOR 할 때가 PLAN이 바뀌어져 버렸고
    이로 인해 블럭또한 바뀌어서 적용이 안되었습니다.

    하여 DISPLAY시 바인드 변수의 데이터타입을 지정한 뒤 PLAN을 확인해보니 여전히
    TO_NUMBER(:B1)로 적용이 되고 있었습니다. 결국 PLAN을 확인할 때는 바인드 변수의
    데이터타입을 핸들링 할 수 없었는데 혹시 동규님께서 이를 핸들링하는 방법에 대해
    아시고 계신게 있으신지요?

    아래에 간단하게 데이터타입에 대한 내용을 확인할 수 있는 스크립트를 올려드립니다.

    -- create table
    DROP TABLE TEST_X PURGE;

    CREATE TABLE TEST_X AS
    SELECT LEVEL EMPNO
    FROM DUAL
    CONNECT BY LEVEL <= 100
    ;

    -- create index
    CREATE UNIQUE INDEX EMP_U1 ON TEST_X (EMPNO) COMPUTE STATISTICS;

    -- gather statistics
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_X', CASCADE => TRUE);

    -- 테스트
    VAR B1 NUMBER;
    :B1 := 1;

    EXPLAIN PLAN FOR
    SELECT *
    FROM TEST_X
    WHERE EMPNO = :B1
    ;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

    --------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| TEST_X | 1 | 16 | 1 (0)| 00:00:01 |
    |* 2 | INDEX UNIQUE SCAN | EMP_U1 | 1 | | 0 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------

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

    2 - access("EMPNO"=TO_NUMBER(:B1))
    ;

    SET SERVEROUTPUT OFF;

    VAR B1 NUMBER;
    :B1 := 1;

    SELECT /*+ GATHER_PLAN_STATISTICS */
    *
    FROM TEST_X
    WHERE EMPNO = :B1
    ;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

    --------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | | 1 (100)| |
    | 1 | TABLE ACCESS BY INDEX ROWID| TEST_X | 1 | 16 | 1 (0)| 00:00:01 |
    |* 2 | INDEX UNIQUE SCAN | EMP_U1 | 1 | | 0 (0)| |
    --------------------------------------------------------------------------------------

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

    2 - access("EMPNO"=:B1)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.04.14 17:41 신고  댓글주소  수정/삭제

      :+ 변수명을 사용하면 implicit 형변환을 피할수 없습니다.
      explin plan 에서 : + 변수명을 사용할경우 값은 제대로 binding 되지만 변수자체는 항상 varchar2 로 인식되기 때문입니다.
      컬럼이 varchar2 형이 아닌 경우 항상 형변환이 발생 하겠죠.
      형변환을 피할수 있는 방법은 :+ 변수명 대신에 & + 변수명을 사용하시기 바랍니다.
      또한 accept 명령어를 사용해도 같은 효과를 볼수 있습니다.
      하지만 & + 변수명을 사용하면 binding 을 하지않고 변수를 상수로 replace 하는 개념입니다.
      하지만 explain plan 이라는 것은 어짜피 adhoc query 이므로 latch 걱정은 안하셔도 됩니다.

      아래의 스크립트를 참고 하시기 바랍니다.

      SQL> EXPLAIN PLAN FOR
      2 SELECT *
      3 FROM (SELECT E1.*
      4 FROM EMP E1
      5 UNION ALL
      6 SELECT E1.*
      7 FROM EMP E1
      8 UNION ALL
      9 SELECT E1.*
      10 FROM EMP E1
      11 UNION ALL
      12 SELECT E1.*
      13 FROM EMP E1) EMP_V,
      14 DEPT D
      15 WHERE EMP_V.DEPTNO = D.DEPTNO
      16 AND D.DEPTNO = &v_deptno
      17 ;
      v_deptno의 값을 입력하십시오: 10
      구 16: AND D.DEPTNO = &v_deptno
      신 16: AND D.DEPTNO = 10

      해석되었습니다.

      SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ADVANCED'));

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------

      -------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
      -------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)|
      | 1 | NESTED LOOPS | | 1 | 100 | 2 (0)|
      | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 62 | 1 (0)|
      |* 3 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | | 1 (0)|
      | 4 | VIEW | | 1 | 38 | 1 (0)|
      | 5 | UNION-ALL | | | | |
      | 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 7 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      | 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 9 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      | 10 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 11 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      | 12 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 13 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      -------------------------------------------------------------------------------

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

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------

      3 - access("D"."DEPTNO"=10)
      7 - access("E1"."DEPTNO"=10)
      9 - access("E1"."DEPTNO"=10)
      11 - access("E1"."DEPTNO"=10)
      13 - access("E1"."DEPTNO"=10)
      ... 이하생략
      즐거운 하루 되세요.

  2. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.15 16:33 신고  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다.
    제가 이런 질문을 드리는 이유는 bind 변수가 explain plan과 execution plan에서 틀린 경우가 있어서 그랬구요...
    이와 관련되어 테스트 한 내용을 링크걸어 드리니 한번 봐주시기 바랍니다 ^^

    http://blog.naver.com/xsoft/150045721858

    감사합니다.

  3. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.16 09:12 신고  댓글주소  수정/삭제  댓글쓰기

    메일 보내 드렸습니다. 확인 부탁 드립니다 ^^

  4. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.16 15:00 신고  댓글주소  수정/삭제  댓글쓰기

    동규님. 상세한 답변 감사드립니다. ^^
    FPD가 JPPD보다 COST가 낮아 옵티마이저가 먼저 고려되는 것인지 덕분에 처음 알았습니다.
    정말 동규님의 깊은 내공을 잠시나마 뵐 수 있었습니다.

  5. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.04.24 11:45 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 동규님

    'DBMS_XPLAN.DISPLAY_CURSOR'의 A-Time에 대해 궁금한 점이 있어서 질문을 드립니다.
    제가 알고 있기로는 A-Time이 실제 수행시간을 표현하는 것으로 알고 있는데 이 값이 Trace와
    차이가 나는 부분이 확인되어 이에 대해 질문을 드리고자 합니다.

    아래는 이 내용을 재현할 수 있는 스크립트와 해당 내용에 대해 나열한 것입니다.


    1. 수행 스크립트
    -- create table
    DROP TABLE EMP PURGE;
    DROP TABLE DEPT PURGE;

    CREATE TABLE EMP AS
    SELECT LEVEL EMPNO,
    TO_CHAR(LEVEL) EMPNO_VARCHAR,
    CHR(65 + CEIL(LEVEL / 500000) - 1) JOB,
    TO_DATE('00010101', 'YYYYMMDD') + CEIL(LEVEL / 10) - 1 HIREDATE,
    LENGTH(LEVEL) * 10 DEPTNO
    FROM DUAL
    CONNECT BY LEVEL <= 10000000
    ;

    CREATE TABLE DEPT AS
    SELECT LEVEL * 10 DEPTNO,
    'SALES_' || LEVEL DNAME,
    'ZONE_' || LEVEL LOC
    FROM DUAL
    CONNECT BY LEVEL <= 9
    ;

    -- create index
    CREATE UNIQUE INDEX USER.EMP_U1 ON APPS.EMP (EMPNO) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_U1 NOPARALLEL;

    CREATE INDEX USER.EMP_N1 ON APPS.EMP (DEPTNO) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_N1 NOPARALLEL;

    CREATE INDEX USER.EMP_N2 ON APPS.EMP (HIREDATE) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_N2 NOPARALLEL;

    CREATE INDEX USER.EMP_N3 ON APPS.EMP (EMPNO_VARCHAR) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_N3 NOPARALLEL;

    CREATE UNIQUE INDEX USER.DEPT_U1 ON APPS.DEPT (DEPTNO);

    -- gather statistics
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE, DEGREE => 8);

    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE);




    2. 테스트
    1) Trace 내용
    SElECT *
    FROM EMP E,
    DEPT D
    WHERE E.DEPTNO = D.DEPTNO
    AND (E.DEPTNO = :B1 -- 10
    OR
    E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD') -- 2009/04/01
    AND TO_DATE(:B3, 'YYYYMMDD') -- 2009/04/02
    )
    ;

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 3 9.25 9.06 0 44245 0 29
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 5 9.25 9.06 0 44245 0 29

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 44

    Rows Row Source Operation
    ------- ---------------------------------------------------
    29 HASH JOIN (cr=44245 pr=0 pw=0 time=9059820 us)
    9 TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=180 us)
    29 TABLE ACCESS FULL EMP (cr=44242 pr=0 pw=0 time=9057805 us)


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 3 0.00 0.00
    SQL*Net message from client 3 0.31 0.57






    2) DBMS_XPLAN.DISPLAY_CURSOR 내용
    TEST >
    1 SElECT /*+ GATHER_PLAN_STATISTICS */
    2 *
    3 FROM EMP E,
    4 DEPT D
    5 WHERE E.DEPTNO = D.DEPTNO
    6 AND (E.DEPTNO = :B1 -- 10
    7 OR
    8 E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD') -- 2009/04/01
    9 AND TO_DATE(:B3, 'YYYYMMDD') -- 2009/04/02
    10 )
    11 ;

    EMPNO EMPNO_VARCHAR JOB HIREDATE DEPTNO DEPTNO DNAME
    ---------- ------------- ------ ------------ ---------- ---------- --------
    1 1 A 01-JAN-01 10 10 SALES_1
    2 2 A 01-JAN-01 10 10 SALES_1
    3 3 A 01-JAN-01 10 10 SALES_1
    4 4 A 01-JAN-01 10 10 SALES_1
    5 5 A 01-JAN-01 10 10 SALES_1
    6 6 A 01-JAN-01 10 10 SALES_1
    7 7 A 01-JAN-01 10 10 SALES_1
    8 8 A 01-JAN-01 10 10 SALES_1
    9 9 A 01-JAN-01 10 10 SALES_1
    7334991 7334991 O 01-APR-09 70 70 SALES_7
    7334992 7334992 O 01-APR-09 70 70 SALES_7
    7334993 7334993 O 01-APR-09 70 70 SALES_7
    7334994 7334994 O 01-APR-09 70 70 SALES_7
    7334995 7334995 O 01-APR-09 70 70 SALES_7
    7334996 7334996 O 01-APR-09 70 70 SALES_7
    7334997 7334997 O 01-APR-09 70 70 SALES_7
    7334998 7334998 O 01-APR-09 70 70 SALES_7
    7334999 7334999 O 01-APR-09 70 70 SALES_7
    7335000 7335000 O 01-APR-09 70 70 SALES_7
    7335001 7335001 O 02-APR-09 70 70 SALES_7
    7335002 7335002 O 02-APR-09 70 70 SALES_7
    7335003 7335003 O 02-APR-09 70 70 SALES_7
    7335004 7335004 O 02-APR-09 70 70 SALES_7
    7335005 7335005 O 02-APR-09 70 70 SALES_7
    7335006 7335006 O 02-APR-09 70 70 SALES_7
    7335007 7335007 O 02-APR-09 70 70 SALES_7
    7335008 7335008 O 02-APR-09 70 70 SALES_7
    7335009 7335009 O 02-APR-09 70 70 SALES_7
    7335010 7335010 O 02-APR-09 70 70 SALES_7

    29 rows selected.

    Elapsed: 00:00:09.67
    TEST >
    1 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID gczuawz61s195, child number 0
    -------------------------------------
    SElECT /*+ GATHER_PLAN_STATISTICS */ * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND
    (E.DEPTNO = :B1 -- 10 OR E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD') -- 2009/04/01
    AND TO_DATE(:B3, 'YYYYMMDD') -- 2009/04/02 )

    Plan hash value: 1093152308

    -------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 2021K| 29 |00:00:00.01 | 44245 | 2 | 1023K| 1023K| 811K (0)|
    | 2 | TABLE ACCESS FULL| DEPT | 1 | 9 | 9 |00:00:00.01 | 3 | 0 | | | |
    |* 3 | TABLE ACCESS FULL| EMP | 1 | 2021K| 29 |00:00:00.01 | 44242 | 2 | | | |
    -------------------------------------------------------------------------------------------------------------------------

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

    1 - access("E"."DEPTNO"="D"."DEPTNO";)
    3 - filter(("E"."DEPTNO"=:B1 OR ("E"."HIREDATE">=TO_DATE(:B2,'YYYYMMDD') AND
    "E"."HIREDATE"<=TO_DATE(:B3,'YYYYMMDD'))))




    내용을 보시면 아시겠지만 Trace에서는 수행속도가 9초 정도 걸렸고 이 내용이 row operation에서도
    정확히 표현이 되고 있습니다.
    하지만 'DBMS_XPLAN.DISPLAY_CURSOR'에서는 수행 시 'Elapsed: 00:00:09.67'가 걸렸지만 A-Time에서는
    '00.01'초만 걸린 것으로 확인이 되고 있습니다.

    왜 이런 현상이 나오는지 알 수 있을까요?

  6. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.04.24 15:14 신고  댓글주소  수정/삭제  댓글쓰기

    오라클 버그 입니다.
    이런 경우에는
    alter session set STATISTICS_LEVEL = ALL ;
    을 설정 하시고 다시한번 SQL 과 DBMS_XPLAN.DISPLAY_CURSOR 를 실행 해보시기 바랍니다.
    감사합니다.

  7. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.04.24 18:43 신고  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다 ^^

프로젝트를 하다보면 결합인덱스의 2번째 혹은 3번째 컬럼에 IN 조건을 많이 사용하는것이 관찰된다.
오늘은 결합인덱스 에서 IN 을 사용한경우 나타날수있는3가지 경우(InList, Concatnation, Range Scan)에
대하여 알아 보겠다.

버젼 11.1.0.6
인덱스 : EMP_JOB_MGR_SAL_IDX (JOB_ID, MANAGER_ID, SALARY)  

아래는 인덱스의 첫번째 컬럼이 = 조건으로 들어오고 두번째 컬럼이 IN 변수조건으로 들어 왔을때의
전통적인 방식의 PLAN 이다.

EXPLAIN PLAN FOR
SELECT /*+ RULE */
             a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2);
select * from table(dbms_xplan.display);


------------------------------------------------------------
| Id  | Operation                    | Name                |
------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |
|   1 |  CONCATENATION               |                     |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |
|*  5 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |
------------------------------------------------------------
 
별로 특별할것이 없는 전통적인 방식의 CONCATENATION Plan 이 나왔다.
그렇다면 Rule 힌트를 빼면 어떻게 되는겠는가?
CONCATENATION Plan 대신에 InList Plan 을 기대하는가?
아래의 Plan 을 보자.

EXPLAIN PLAN FOR
SELECT a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2) ;


---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB_ID"=:V_JOB)
       filter("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER2))

InList Plan 이 나오지 않는다.
어떻게 된것인가?
혹자는 NO_EXPAND 힌트를 사용하라고 이야기 하지만 그것은 CONCATENATION Plan 을 방지하는 힌트일뿐
InList Plan으로 유도하지 않는다.

EXPLAIN PLAN FOR
SELECT /*+ NO_EXPAND */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2);

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB_ID"=:V_JOB)
       filter("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER2))

예상과는 다르게 Range Scan 이 나오고 IN 절은 Filter 조건으로 되고말았다.
그렇다면 이런경우 도데체 어떻게 InList Plan 으로 유도 한단 말인가?
이런경우에 사용할수 있는 힌트가 바로 NUM_INDEX_KEYS 이다.
아래의 SQL 을보자.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2)
   ;


----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2)))

깔끔하게 InList Plan 으로 유도 되었다.
그렇다면 NUM_INDEX_KEYS 힌트의 마지막 인자인 숫자 2는 무엇을 의미하는가?
결합 인덱스의 2번째 컬럼(MANAGER_ID) 까지는 InList Plan 으로 풀리는 것을 의미한다.
거꾸로 이야기하면 NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) 로 힌트를 주면 인덱스의 3번째
컬럼인 SALARY 조건에 IN 조건을 주어도 InList Plan 으로 풀리지 않는다.
아래의 SQL및 Plan 을 보고 InList Plan 이 나왔다고 생각하면 오산이다.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2, :v_manager3)
   AND salary IN (:v_sal1, :v_sal2, :v_sal3);


----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER3)))
       filter("SALARY"=TO_NUMBER(:V_SAL1) OR "SALARY"=TO_NUMBER(:V_SAL2) OR
              "SALARY"=TO_NUMBER(:V_SAL3))

위 Plan 에서의 InList 는 Salary 조건과는 상관없이 Job_id 와 Manager_id  두가지 조건만으로 InList 가
나온것이다.
Salary 조건은 역시 Filter 로 빠진것을 알수 있다.
아래처럼 3으로 바꾼다면 인덱스의 3번째컬럼 까지 InList 로 풀리는걸 볼수 있다.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 3) */
        a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2, :v_manager3)
   AND salary IN (:v_sal1, :v_sal2, :v_sal3);


----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER3)) AND
              ("SALARY"=TO_NUMBER(:V_SAL1) OR "SALARY"=TO_NUMBER(:V_SAL2) OR "SALARY"=TO_NUMBER(:V_SAL3)))

하지만 manager_id 종류가 많지않은 경우에는 Range Scan이 더 나은 성능을 보이는 경우가 많으므로
무조건 적용해서는 안된다.
오늘 사용한 Rule 힌트나
NUM_INDEX_KEYS 힌트는 Production 시스템에서는 사용하면 안된다.
사용하려면 deprecate 된 힌트나 undocument 힌트를 사용한 SQL 만 따로 목록을 만들어서 관리해야 할것이다.

PS: 테스트 환경은 11g 에서 테스트 됬지만 10g 에서 테스트 해도 동일한 결과를 얻을수 있다.

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. 2008.12.12 09:26  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  2. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2008.12.15 13:29 신고  댓글주소  수정/삭제  댓글쓰기

    NUM_INDEX_KEYS Hint가 정확하게 어느 Version에서 추가되었나요?
    (저도 한번 확인해봐야겠네요)

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2008.12.17 22:31 신고  댓글주소  수정/삭제

      확인결과 10.2.0.2 에서 추가 되었습니다.
      참고로 10.2.0.1 에서는 힌트도 없을 뿐더러 힌트와 상관없이 무조건 inlist PLAN 을 선택합니다.
      또한 10.2.0.4 버젼에서는 Index Range Scan 과 Inlist 의 갈림길에서 Costing 을 하는것이 관찰되었습니다.