DBMS_XPLAN 활용

 

**************************************************************************************************

* 실행계획 및 Row Source별 수행통계 시 설정(한개이상 설정필요)

**************************************************************************************************

/* 1. Hint 추가(Row Source execution정보를 활성화) - 추천*/

/*+GATHER_PLAN_STATISTICS*/ 

/* 2. 세션설정 - 추천사항 아님 개발용으로나 사용*/

ALTER SESSION SET STATISTICS_LEVEL = ALL;

/* 참고사항> _ROWSOURCE_EXECUTION_STATISTICS 파라미터값을TRUE로 변경*/

*************************************************************************************************** DBMS_XPLAN.DISPLAY_CURSOR(단일 SQL문에 대해 실제 수행된 실행계획 보여주는 Function) ==> (예측실행계획만 확인 시, DBMS_XPLAN.DISPLAY 사용)

1. FUNCTION PARAMETER

    - DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID, ChildNumber, FORMAT)

       ;NULL,NULL 인경우 가장 최근에 실행된 쿼리의미(SQL ID, ChildNumber 입력 시, 특정쿼리지정) 

2. FORMAT 옵션

2-1. 예측내용

         - BASIC, TYPICAL, ALL, OUTLINE, ADVANCED 

      2-2. 실측내용

          - 가장많은 정보나타내는 옵션   : advanced allstats last

          - 쿼리변형없는 단순쿼리 튜닝 시 : allstats last -rows +predicate (실행통계, Row수(E-row)생략, Predicate Information출력)

          - 쿼리변형이 발생 또는 복잡한 쿼리 튜닝시    : allstats last -rows +alias +outline +predicate (실행통계, Row수(E-row)생략, 쿼리블럭추가, 힌트정보출력, Predicate Information출력)

  3. Query Optimizer Estimations 항목(옵티마지어의 예상치)

      - E-rows        : 각 Operation이 끝났을 때 Return되는 건수

      - E-Bytes       : 각 Operation이 Return한 Byte수

      - Cost (%CPU)   : 각 Operation의 Cost

      - E-Time        : 수행시간

  4. Runtime statistics항목(실제수행시간 및 실제수행건수)

      - Starts        : 각 Operation을 반복 수행한건수(NL조인의 경우 조인횟수)

      - A-Rows        : 각 Operation이 Return 한 건수

      - A-Time        : 각 실제실행시간

  5. I/O statistics(I/O관련하여 Read/Write한 Block수)

      - Buffers       : 각 Operation이 Memory에서 읽은 수

      - Reads         : 각 Operation이 Disk에서 Read한 Block수(예지에서는 나타나지 않음)

      - Writes        : 각 Operation이 Disk에서 Write한 Block수(예지에서는 나타나지 않음)

**************************************************************************************************

/* 1. 가장많은 정보나타내는 옵션*/

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'advanced allstats last'));

  

/* 2. 쿼리변형없는 단순쿼리 튜닝 시*/

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +predicate'));

  

/* 3. 쿼리변형이 발생 또는 복잡한 쿼리 튜닝시*/

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +alias +outline +predicate'));

ADVANCED 옵션이면 튜닝에 필요한 모든 정보 나옴

**************************************************************************************************

 Row Source별 수행통계 조회 SQL 

 - 파라미터 : SQL_ID, CHILD_NUMBER

**************************************************************************************************

/* 1. Row Source별 수행통계 전체내역 조회 SQL */

SELECT a.LAST_OUTPUT_ROWS "Rows"

     , a.*

  FROM V$SQL_PLAN_STATISTICS_ALL a

 WHERE SQL_ID = '3p0bf0usv002s'

   AND CHILD_NUMBER = 0

ORDER BY ID;

/* 2. Row Source별 수행통계 조회 SQL(컬럼가공) */

WITH V1 AS (

           SELECT VP.ID PID,

                  NVL(SUM( VP1.LAST_CR_BUFFER_GETS ),0) SUM_LIO,

                  NVL(SUM( VP1.LAST_ELAPSED_TIME), 0) SUM_ELA

           FROM   V$SQL_PLAN_STATISTICS_ALL VP ,

                  V$SQL_PLAN_STATISTICS_ALL VP1

           WHERE  VP.SQL_ID = :SQL_ID

           AND    VP.CHILD_NUMBER= :CHDNUM

           AND    VP.SQL_ID = VP1.SQL_ID(+)

           AND    VP.CHILD_NUMBER=VP1.CHILD_NUMBER(+)

           AND    VP.ID = VP1.PARENT_ID(+)

           GROUP  BY VP.ID

     ),

     V2 AS (

            SELECT MAX( VP.LAST_CR_BUFFER_GETS ) MAX_LIO ,

                   MAX( VP.LAST_ELAPSED_TIME ) MAX_ELA

            FROM   V$SQL_PLAN_STATISTICS_ALL VP

            WHERE  VP.SQL_ID = :SQL_ID

            AND    VP.CHILD_NUMBER= :CHDNUM

     )

SELECT /*+ OPT_PARAM('PARALLEL_EXECUTION_ENABLED', 'FALSE') */ /* EXEC_FROM_DBMS_XPLAN */

       ID,

       DEPTH ,

       POSITION ,

       PARENT_ID,

       LPAD(' ', DEPTH*2       )||OPERATION||' '||OPTIONS OPERATION,

       OBJECT_NAME NAME,

       CARDINALITY E_ROWS ,

       OUTROWS A_ROWS ,

       CRGETS "LIO_SIGMA(BLOCK)",

       CRGETS - (SELECT SUM_LIO FROM V1 WHERE V1.PID=A.ID) "LIO_DELTA(BLOCK)",

       ROUND((CRGETS - (SELECT SUM_LIO FROM V1 WHERE V1.PID=A.ID))/(SELECT MAX_LIO FROM V2)*100,1) "LIO_RATIO(%)",

       ROUND(ETIME/1000000,2) "ELA_SIGMA(SEC)",

       ROUND((ETIME  - (SELECT SUM_ELA FROM V1 WHERE V1.PID=A.ID))/1000000,2) "ELA_DELTA(SEC)",

       ROUND((ETIME  - (SELECT SUM_ELA FROM V1 WHERE V1.PID=A.ID))/(SELECT MAX_ELA FROM V2)*100,1) "ELA_RATIO(%)"

FROM   (

        SELECT 

               VP.ID ID ,

               VP.DEPTH DEPTH ,

               VP.POSITION POSITION ,

               VP.OPERATION OPERATION ,

               VP.OPTIONS OPTIONS ,

               VP.PARENT_ID PARENT_ID,

             --  VP.COST COST ,

             --  VP.TIME TIME ,

               VP.CARDINALITY CARDINALITY ,

             --  VP.BYTES BYTES ,

             --  VP.OBJECT_NODE OBJECT_NODE ,

               VP.OBJECT_NAME OBJECT_NAME ,

             --  VP.TEMP_SPACE TEMP_SPACE ,

               VP.LAST_STARTS STARTS ,

               VP.LAST_OUTPUT_ROWS OUTROWS ,

               VP.LAST_CR_BUFFER_GETS CRGETS ,

             --  VP.LAST_CU_BUFFER_GETS CUGETS ,

             --  VP.LAST_DISK_READS READS ,

             --  VP.LAST_DISK_WRITES WRITES ,

               VP.LAST_ELAPSED_TIME ETIME --,

             --  VP.ESTIMATED_OPTIMAL_SIZE MEM_OPT ,

             --  VP.ESTIMATED_ONEPASS_SIZE MEM_ONE ,

             --  VP.LAST_MEMORY_USED LAST_MEM_USED ,

             --  VP.LAST_EXECUTION LAST_MEM_USAGE ,

             --  VP.OPTIMAL_EXECUTIONS OPT_CNT ,

             --  VP.ONEPASS_EXECUTIONS ONE_CNT ,

             --  VP.MULTIPASSES_EXECUTIONS MULTI_CNT ,

             --  VP.MAX_TEMPSEG_SIZE MAX_TMP ,

             --  VP.LAST_TEMPSEG_SIZE LAST_TMP

        FROM   V$SQL_PLAN_STATISTICS_ALL VP

        WHERE  VP.SQL_ID = :SQL_ID

        AND    VP.CHILD_NUMBER= :CHDNUM

       ) A;

 

 

 

 

----- Original Message -----

From : 이은상/웹&모바일 

To : 이금율/상품/자원

Sent : 2016-11-17 15:44:03

 

ITH v1 AS (

           SELECT vp.id pid,

                  nvl(SUM( vp1.last_cr_buffer_gets ),0) sum_lio,

                  nvl(SUM( vp1.last_elapsed_time), 0) sum_ela

           FROM   v$sql_plan_statistics_all vp ,

                  v$sql_plan_statistics_all vp1

           WHERE  vp.sql_id = :sql_id

           AND    vp.child_number=0

           AND    vp.sql_id = vp1.sql_id(+)

           AND    vp.child_number=vp1.child_number(+)

           AND    vp.id = vp1.parent_id(+)

           GROUP  BY vp.id

     ),

     v2 AS (

            SELECT MAX( vp.last_cr_buffer_gets ) max_lio ,

                   MAX( vp.last_elapsed_time ) max_ela

            FROM   v$sql_plan_statistics_all vp

            WHERE  vp.sql_id = :sql_id

            AND    vp.child_number=0

     )

SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ /* EXEC_FROM_DBMS_XPLAN */

       id,

       depth ,

       position ,

       parent_id,

       lpad(' ', depth*2       )||operation||' '||options Operation,

       object_name name,

       cardinality e_rows ,

       outrows a_rows ,

       crgets "LIO_SIGMA(BLOCK)",

       crgets - (select sum_lio from v1 where v1.pid=a.id) "LIO_DELTA(BLOCK)",

       round((crgets - (select sum_lio from v1 where v1.pid=a.id))/(select max_lio from v2)*100,1) "LIO_RATIO(%)",

       round(etime/1000000,2) "ELA_SIGMA(SEC)",

       round((etime  - (select sum_ela from v1 where v1.pid=a.id))/1000000,2) "ELA_DELTA(SEC)",

       round((etime  - (select sum_ela from v1 where v1.pid=a.id))/(select max_ela from v2)*100,1) "ELA_RATIO(%)"

FROM   (

        SELECT 

               vp.id id ,

               vp.depth depth ,

               vp.position position ,

               vp.operation operation ,

               vp.options options ,

               vp.parent_id parent_id,

             --  vp.cost cost ,

             --  vp.time TIME ,

               vp.cardinality cardinality ,

             --  vp.bytes bytes ,

             --  vp.object_node object_node ,

               vp.object_name object_name ,

             --  vp.temp_space temp_space ,

               vp.last_starts starts ,

               vp.last_output_rows outrows ,

               vp.last_cr_buffer_gets crgets ,

             --  vp.last_cu_buffer_gets cugets ,

             --  vp.last_disk_reads reads ,

             --  vp.last_disk_writes writes ,

               vp.last_elapsed_time etime --,

             --  vp.estimated_optimal_size mem_opt ,

             --  vp.estimated_onepass_size mem_one ,

             --  vp.last_memory_used last_mem_used ,

             --  vp.last_execution last_mem_usage ,

             --  vp.optimal_executions opt_cnt ,

             --  vp.onepass_executions one_cnt ,

             --  vp.multipasses_executions multi_cnt ,

             --  vp.max_tempseg_size max_tmp ,

             --  vp.last_tempseg_size last_tmp

        FROM   v$sql_plan_statistics_all vp

        WHERE  vp.sql_id = :sql_id

        AND    vp.child_number=0

       ) a;

  

-----------------------------------------------------------------------------------------------------

아래는 다음 링크에 있슴 (유명한 오동규님의 블로그)

http://scidb.tistory.com/entry/About-DBMSXPLAN-1%EC%8B%A4%ED%96%89%EA%B3%84%ED%9A%8D   

  

최근의 많은수의 사람들이 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 를 의미함.

-----------------------------------------------------------------------------------------------------