DBMS_XPLAN 정보 조회

출처 : http://blog.naver.com/onlywin7788?Redirect=Log&logNo=140155221360

출처 : http://cafe.naver.com/ocmkorea/book2025209/9365

/*

--> 방식 1. (이 sql문은 이 방식임)

        1. 테이블 전체 목록 조회

           SQL> select * from tab;

        2. 수행한 쿼리의 Plan을 조회

           SQL> @xplan.sql

        3. 정확한 통계를 위해 힌트 실행

           SQL> select /*+ gather_plan_statistics */ * from tabs;

        * 오타 나지 않도록 주의함.

--> 방식 2

        1. 실행시 Plan  수집

           SQL> EXPLAIN FOR

                 SELECT * FROM TAB;

        2. 실제 정보 조회

           SQL> select * from tab(dbms_xplan.display());

                or

           SQL> select *

                from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +alias +outline +predicate'))

/

--> 인자 정보

        dbms_xplan.display_cursor(인자1, 인자2, 인자3);

        인자1 : plan table 명을 입력하며, 따로 지정하지 않았을 때는 기본으로 'PLAN_TABLE'이다.

        인자2 : Execution Plan시 Set STATEMENT_ID를 지정한 경우 이를 불러올 수 있다.

                값이 Null일 경우 마지막에 실행된 문장을 불러온다.

        인자3 : 출력 포맷 형태 지정한다.

--> 세번째 인자의 포맷 정보

        1) 기본 Format Controller : 반드시 적용되어야 하는 기본적인 Controller임.

                                    적용하지 않더라도 자동으로 기본값으로 적용된다.

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

                basic    : 가장 기본적인 포맷으로서 Id, Operation, Object Name을 출력한다.

                typical  : basic 옵션에서 한발 더 나아가서 Optimizer가 예상할 수 있는 모든 것을 보여준다.

                           출력되는 정보로는 예상 Rows, 예상 Bytes, 예상 Temporary Space 사용량, Cost 예상 시간 Predicate Imformat(Operation 별로 Access 및 Filter 정보)이다.

                serial   : typical과 같으나 Parallel 쿼리 사용시 관련정보가 나오지 않는다.

                all      : Plan 정보는 typical과 같으나 Plan이외의 정보 중에서 Outline Data 정보를 제외하고 전부 출력한다.

                advanced : all과 같지만 Peeked Binds, Outline Data, Note 등을 더 보여준다.

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

        2) 세부 Format Controller : 기본 포맷정보에 의해서 표시되거나 생략되는 세부적인 포맷을 Control한다.

                                    이 Control은 +표시로 추가되거나 - 표시로 생략이 가능하다.

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

                alias    : 가장 기본적인 포맷으로서 Id, Operation, Object Name을 출력한다.

                bytes    : basic 옵션에서 한발 더 나아가서 Optimizer가 예상할 수 있는 모든 것을 보여준다.

                           출력되는 정보로는 예상 Rows, 예상 Bytes, 예상 Temporary Space 사용량, Cost 예상 시간 Predicate Information (Operation 별로 Access 및 Filter 정보)이다.

                cost     : typical과 같으나 Parallel쿼리 사용시 관련 정보가 나오지 않는다.

                note     : Plan 정보는 typical과 같으나 Plan이외의 정보 중에서 Outline Data 정보를 제외하고 전부 출력한다.

                outline  : all과 같지만 Peeked Binds, Outline Data, Note 등을 더 보여준다.

                parallel : Parallel 쿼리인 경우 TQ, IN-OUT, PQ Distribute 등의 정보를 Control한다.

                partition : Partition Access가 포함된 경우

                peeked_binds : Bind 변수의 값을 control 한다. 단, _optim_peek_user_binds 파라미터의 값이 true로 되어 있는 경우에만 해당되며 파라미터는 세션 단위로 수정이

                               가능하다. (Explain Plan은 출력되지 않음)

                predicate : Predicate Information을 Control한다. Operation 별로 Access 및 Filter 정보를 나타낸다.

                            일반적인 튜닝시 가장 눈 여겨봐야 할 정보이다.

                projection : Projection Information을 Control한다. Operation 별로 Select 되는 컬럼 정보를 나타낸다.

                remote   : DB Link를 사용할 때 Remote 쿼리의 수행 정보를 Control한다.

                rows     : Plan상의 E-Rows 수를 Control한다.

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

        3) 실행통계 Format Controller : 이 Control을 적용하면 실행시의 PGA 통계를 출력한다.

        이 정보들은 DBMS_XPLAN.DISPLAY 함수에는 적용되지 않는다.

        왜냐하면 Explain Plan은 실제 수행되는 것이 아니므로 실행통계정보가 없기 때문이다.

        또한 DBMS_XPLAN.DISPLAY_CURSOR 이나 DBMS_XPLAN.DISPLAY_AWR등의 함수 수행시에도 GATHER_STATISTICS 힌트를 주거나 아니면 STATISTICS_LEVEL 파라미터를 ALL로 설정해야 출력이 가능하다.

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

                allstats : I/O 통계정보(Buffers, Reads, Writes)와 PGA 통계 정보(OMem, 1Mem, Used-Mem, Used-Tmp, Max-Tmp 등)를 동시에 Control 한다.

                iostats  : I/O 통계정보(Buffers, Reads, Writes)를 Control 한다.

                last     : 실행 통계 출력시 이 Control을 명시하면 가장 마지막에 수행된 실행 통계를 출력한다.

                           이 Control을 명시하지 않으면 실행 통계츼 누적치를 출력하므로 주의가 필요합니다.

                memstats : PGA 통계정보(OMem, 1Mem, Used-Mem, Used-Tmp, Max-Tmp등)를 동시에 Control한다.

                runstats_last : iostats과 last Control 과 동일하다.

                                이 Control은 Oracle 10g Release 1에서만 사용할 수 있다.

                runstats_tot : iostats Control과 동일하다.

                               이 Control은 oracle 10g Release 1 에서만 사용할 수 있다.

                * runstats_last와 runstats_tot를 제외한 4가지의 Control은 Oracle 10g Release 2 이상에서만 사용할 수 있다.

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

        4) format 사용 예제

        Format중 가장 많은 정보를 출력 할 수 있는 포맷은 'advanced allstats last'이며 출력 가능한 모든 내용이 출력되게 되므로 상황이나, 자신에 맞는 포맷을 사용해야 적절함.

        4-1) 쿼리 변형이 없는 단순 쿼리 튜닝의 경우(최대한 단순한 포맷형태 사용)

             SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last-rows + predicate');

             => 포맷을 'allstats last-rows + predicate'로 주었으므로 예측 Row 수(E-row)가 생략되고 실행통계와 Predicate Information 만 출력된다.

        4-2) 쿼리 변형이 발생하거나 복잡한 쿼리 튜닝시 쿼리 블러과 힌트 정보를 추가로 출력

             SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last-rows + alias + outline + predicate');

             => 'allstats last-rows + alias + outline + predicate' 포맷을 사용하면 Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력된다.

                + alias는 쿼리블럭 정보를 추가하는 것이며, + Outline는 오라클이 내부적으로 사용하는 힌트정보 출력의 의미이다.

                쿼리 변형이 발생한 경우나 뷰(혹은 인라인뷰) 등을 튜닝할 경우 적합한 옵션이다.

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

        10046 + tkprof 와 동일한 정보를 조회는

        select /*+ gather_plan_statistics */ * from tab;

        select *

        from table(dbms_xplan.display_cursor(null, null, 'advanced ALLSTATS LAST +alias +outline +predicate'))

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

AWR 정보를 이용

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(SQL_ID입력,NULL,NULL, 'ADVANCED ALLSTATS LAST'));

사용 권한 뷰

DISPLAY_CURSOR 함수 : V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL 뷰에 대한 SELECT 권한 필요

DISPLAY_AWR    함수 : DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, V$DATABASE 뷰에 대한 SELECT 권한 필요

DISPLAY_SQLSET 함수 : ALL_SQLSET_STATEMENTS, ALL_SQLSET_PLANS 뷰에 대한 SELECT 권한 필요

*/

select *

  from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +alias +outline +predicate'))

/