이펙티브 오라클 (2009년)
EXPLAIN_PLAN 1 0 68,665

by 구루비스터디 EXPLAIN_PLAN [2018.05.26]


  1. Introduction
    1. EXPLAIN PLAN 설치
  2. DBMS_XPLAN Package
    1. DBMS_XPLAN Package에서 제공하는 Procedure
    2. 이 Package를 사용하기 위해서는 아래의 객체에 SELECT권한이 있어야 한다.
    3. DBMS_XPLAN.DISPLAY
    4. Function Parameter
    5. 사용방법
    6. Format
    7. DBMS_XPLAN.DISPLAY_CURSOR
    8. Function Parameter
    9. FORMAT
    10. Runtiime Executiion Plan 확인방법
    11. DBMS_XPLAN Package의 실행계획의 항목
    12. 이문서의 내용은 아래 책,문서를 참고했습니다.


Introduction

EXPLAIN PLAN은 주어진 SQL 쿼리를 지금 실행한다면 이 쿼리의 쿼리 계획이 어떻게 될지를 오라클에게 알려달라고 하는 데 이용될 수 있는 SQL 명령이다.


EXPLAIN PLAN 설치

  • EXPLAIN PLAN 을 설치하면 $ORACLE_HOME/rdbms/admin에 다수의 스크립트가 포함된다.
  • PLAN_TALBE이라 명명된 테이블의 CREATE TABLE문을 포함하고 있는 utlxplan.sql(UTiLility eXplain의 줄임말)
  • 병렬 쿼리 계획에 특화된 정보를 포함하여 계획 테이블의 내용을 보여 주는 utlxplp.sql(UTiLtility eXplain Plan Parallel의 줄임말)
  • 일반적인 직렬(비병렬) 계획에 대한 계획 테이블의 내용을 보여 주는 utlxpls.sql(UTiLity eXplain PLan으 줄임말)
  • EXPLAIN PLAN을 설정하기 위해서는 먼저 PLAN_TABLE을 만들어야 한다. PLAN_TABLE 생성은 오라클에서 스크립트로 제공해 주고 있다.
  • 오라클 제공 스크립트 모음 폴더 : $ORACLE_HOME\RDBMS\ADMIN



SQL > @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan

테이블이 생성되었습니다.

경   과: 00:00:00.75
SQL > desc plan_table
 이름                                                                                널?      유형
 ----------------------------------------------------------------------------------- -------- ------
 STATEMENT_ID                                                                                 VARCHAR2(30)
 PLAN_ID                                                                                      NUMBER
 TIMESTAMP                                                                                    DATE
 REMARKS                                                                                      VARCHAR2(4000)
 OPERATION                                                                                    VARCHAR2(30)

  ~~~~~~~ (이하 생략)


  • 책의 설명은.... 잘 이해가 안간다... 그냥 자체 이해한 내용이니... 틀린점있으면 지적해 주면 매우매우 좋다^^.
  • PLAN TABLE을 공유하여 사용하자~ 인것 같다..
  • 스크립트 실행을 위해 특정 유저로 접속하여 스크립트를 실행시키면 테이블의 OWNER는 특정 유저가 된다. 모.. 자체적으로 일정 유저에 공유 시켜도 될거 같은데... 이 책에서는 UTILS 혹은 TOOLS라는 유저를 생성하여 공유할 것을 권한다.
  • UTILS 혹은 TOOLS라고 불리는 스키마 생성 : SQL> CREATE USER UTILS IDENTIFIED BY UTILS;
  • CREATE SESSION과 CREATE TABLE 권한을 부여한다. : SQL> CREATE USER UTILS IDENTIFIED BY UTILS;
  • 스키마를 이용하여 "ON COMMIT PRESERVE ROWS"옵션을 가진 글로벌 임시 테이블 형태로 계획 테이블을 생성한후 PLAN_TALBE의 모든 권한(ALL privilege)을 일반(public)에게 부여한다.


글로벌 임시 테이블
  • 임시 테이블은 사용자가 DML(update, insert, delete) 문을 실행한 후, 트랜젝션을 종료(commit)하면 변경된 데이터들이 테이블에 저장되지 않는 테이블이다.

-- CREATE 문법
CREATE GLOBAL TEMPORARY TABLE <table_name> (
<column_name>  <column_data_type>,
<column_name>  <column_data_type>,
<column_name>  <column_data_type>)
\[ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS\];


on commit delete rows트랜젝션을 발생시킨 후, commit문을 실행할 때 없어지는 방법
한 transaction 동안만 data를 저장(default임)
on commit preserve rows트랜젝션을 종료하면(commit) 테이블 내에 데이터가 저장되었다가 세션을 종료하면 임시 테이블에 저장되었던 데이터들이 모두 없어지는 방법
한 session 동안만 data를 저장
  • temporary table은 특정 session 또는 transaction 동안만 data를 저장한다.
  • TABLE의 definition 정보는 data dictionary에 영구적으로 저장되지만, data는 일시적으로 저장된다.
  • CREATE로 TABLE 생성시 SEGMENT가 할당되는 것이 아니라 첫 번째 INSERT시 할당된다.
  • DML 작업시 해당 data에 대해 LOCK을 걸지 않는다.
  • data 변경시 redo log에 기록이 남지 않는다.



CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE (
        statement_id       varchar2(30),
        plan_id            number,
        timestamp          date,
        remarks            varchar2(4000),
        operation          varchar2(30),
        options            varchar2(255),
        object_node        varchar2(128),
        object_owner       varchar2(30),
        object_name        varchar2(30),
        object_alias       varchar2(65),
        object_instance    numeric,
        object_type        varchar2(30),
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(30),
        other_xml          clob
)
ON COMMIT PRESERVE ROWS;

-- 모든권한 
 grant ALL on PLAN_TABLE 모든유저;

-- 동의어 생성 : PUBLIC SYNONYM 을 생성할수 있는 권한이 있는 유저로 가야한다.
CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;

  • 계획 테이블의 내용을 보여 주는 utlxpls.sql 스크립트의 내용을보면 결국은 DBMS_XPLAN 패키지를 불러서 실행계획을 보여준다.



select plan_table_output 
  from table(dbms_xplan.display('plan_table',null,'serial'));

  • DBMS_XPLAN 패키지가 10g부터는 많은 업그레이드로 인해 요즘의 튜닝도구로 널리 사용되고 있다.
  • 우리는 이제 약간 구식인 그리고 자료가 아주 많은 EXPLAIN PLAN 보다 좀더 세련되게 DBMS_XPLAN 패키지에 대해 심도있게 탐구해 보자~~


DBMS_XPLAN Package

  • Oracle에서 실행 계획을 분석하기 위한 Tool을 세가지 들라면?
  • DBMS_XPLAN Package
  • DBMS_XPLAN Package
  • DBMS_XPLAN Package
  • 오타가 아니다. DBMS_XPLAN Package가 CBO, 그리고 Query 튜닝의 시작과 끝을 담당하는 유일무이한Tool이라는 것을 강조하기 위한 것이다.
  • 이라고.. Oracle Optionizing Oracle Optimizer에서 조동욱씨가 표현하였다.


  • DBMS_XPLAN은 EXPLAIN PLAN 구문보다 확장된 정보를 보여준다.
  • Oracle 9.2버젼에 소개되었으며 plan_table에 저장된 실행계획을 좀 더 쉽게 출력해 볼 수 있게 되었다.
DBMS_XPLAN Package에서 제공하는 Procedure
  • DISPLAY
  • DISPLAY_CURSOR
  • DISPLAY_AWR
  • DISPLAY_SQLSET
  • PREPARE_RECORDS
  • VALIDATE_FORMAT


이 Package를 사용하기 위해서는 아래의 객체에 SELECT권한이 있어야 한다.
  • V$SESSION
  • V$SQL_PLAN
  • V$SQL(optional)
  • V$SQL_PLAN_STATISTICS_ALL
  • @$ORACLE_HOME/rdbms/admin/ultxpls 스크립트를 열여보면 내부적으로 DBMS_XPLAN Package를 호출하고 있는것을 볼 수 있다.

select plan_table_output 
  from table(dbms_xplan.display('plan_table',null,'serial'));

  • 자~ 그럼 본격적으로 DBMS_XPLAN Package에 대해 알아볼까요~~~


DBMS_XPLAN.DISPLAY

  • 단일 SQL문에 대해 예측 실행계획을 보여주는 Function


Function Parameter


FUNCTION DISPLAY(TABLE_NAME   VARCHAR2 DEFAULT 'PLAN_TABLE',
                 STATEMENT_ID VARCHAR2 DEFAULT NULL,
                 FORMAT       VARCHAR2 DEFAULT 'TYPICAL',
                 FILTER_PREDS VARCHAR2 DEFAULT NULL)


파라미터 설명
파라미터설명
TABLE_NAMEExecution Plan이 저장되는 테이블을 테이블을 지정하며, Defalut는 'PLAN_TABLE'이다.
STATEMENT_IDExecution Plan시 SET STATEMENT_ID를 지정한 경우 이를 불러올 수 있다.
값이 NULL일 경우 마지막에 실행된 문장을 불러온다.
FORMAT(BASIC)가장 기본적인 정보만 보여줌
FORMAT(TYPICAL)Format의 Default값인 Typical은 SQL 튜닝에 필요한 Normal한 정보를 보여줌
SQL 튜닝에 가장 유용하게 사용되는 Predicate Information이 제공된다
FORMAT(ALL)Typical Format에 Query Block Name과 Column Projection Information이 추가로 제공된다
FORMAT(OUTLINE)Typical Format에 추가적으로 Hidden Hint인 Outline Global Hint를 제공한다
FORMAT(ADVANCED)ALL Format에 OUTLINE Format를 합친 정보를 제공한다
FILTER_PREDS저장된 PLAN에서 일부 Row 또는 Row Set을 제한하여 출력할 수 있다.


사용방법

  • SQL*PLUS open
    • Oracle Client인SQL*PLUS를실행한다.
  • EXPLAIN PLAN FOR + SQL;
    • 분석하고자 하는 SQL을'EXPLAIN PLAN FOR' 다음에넣고 Enter
  • SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
    • SQL이 EXPLAIN 되었으면 그다음에 원하는 포멧으로 출력한다.
    • 위와같이 하였으면 기본적으로 'PLAN_TABLE' 과 'TYPICAL' Format으로 출력하겠다는의미이다.



  • 여기서 잠깐~! Predicate Information에 나오는 Access,Filter Predicate에 대해 알아보자~!
  • Access Predicate는 Access Type을 결정하는데 사용되는 Predicate(조건)을 의미한다. 더 정확하게 말하면 실제 Block을 읽기 전에 어떤 방법으로 Block을 읽을 것인가를 결정한다는 의미이다.
  • 따라서 Index Lookup이나 Join등은 Access Predicate로 표현된다.
  • Filter Predicate는 실제 Block을 읽은 후 Data를 걸러 내기 위해 사용되는 Predicate(조건)를 의미한다.


Predicate Information test

SQL > create table t1(c1 int, c2 int);

테이블이 생성되었습니다.

경   과: 00:00:00.28
SQL >  create table t2(c1 int, c2 int);

테이블이 생성되었습니다.

경   과: 00:00:00.00
SQL > create index t1_n1 on t1(c1);

인덱스가 생성되었습니다.

경   과: 00:00:00.07
SQL > create index t2_n1 on t2(c1)
  2  ;

인덱스가 생성되었습니다.

경   과: 00:00:00.01
SQL > insert into t1
  2  select level,level from dual connect by level <= 1000;

1000 개의 행이 만들어졌습니다.

경   과: 00:00:00.06
SQL > insert into t2
  2  select level,level from dual connect by level <= 1000
  3  ;

1000 개의 행이 만들어졌습니다.

경   과: 00:00:00.01

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS('HNJ','T1', METHOD_OPT =>'FOR ALL COLUMNS SIZE 1', NO_INVALIDATE => FALSE);

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:01.03
SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS('HNJ','T2', METHOD_OPT =>'FOR ALL COLUMNS SIZE 1', NO_INVALIDATE => FALSE);

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.06

SQL >explain plan for
  2  select /*+ use_nl(t1 t2)  */ *
  3    from t1, t2
  4   where t1.c1 = t2.c2
  5    and t1.c1 = 1;
  
해석되었습니다.

경   과: 00:00:00.12
  
SQL > select * from table(dbms_xplan.display('plan_table',null,'typical',null));


PLAN_TABLE_OUTPUT                                                                                
-------------------------------------------------------------------------------------------------
Plan hash value: 1755794669                                                                      
                                                                                                 
--------------------------------------------------------------------------------------           
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |           
--------------------------------------------------------------------------------------           
|   0 | SELECT STATEMENT             |       |     1 |    14 |     5   (0)| 00:00:01 |           
|   1 |  NESTED LOOPS                |       |     1 |    14 |     5   (0)| 00:00:01 |           
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |           
|*  3 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |           
|*  4 |   TABLE ACCESS FULL          | T2    |     1 |     7 |     3   (0)| 00:00:01 |           
--------------------------------------------------------------------------------------           
                                                                                                 
Predicate Information (identified by operation id):                                              
---------------------------------------------------                                              
                                                                                                 
   3 - access("T1"."C1"=1)                                                                       
   4 - filter("T2"."C2"=1)                                                                       

17 개의 행이 선택되었습니다.

경   과: 00:00:00.09

SQL > explain plan for            
  2  select /*+ use_hash(t1 t2) */ *
  3    from t1, t2                
  4   where t1.c1 = t2.c2         
  5    and t1.c2 = 1;   

해석되었습니다.

경   과: 00:00:00.00
SQL > select * from table(dbms_xplan.display('plan_table',null,'typical',null));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
Plan hash value: 1838229974                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                          
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT   |      |     1 |    14 |     7  (15)| 00:00:01 |                          
|*  1 |  HASH JOIN         |      |     1 |    14 |     7  (15)| 00:00:01 |                          
|*  2 |   TABLE ACCESS FULL| T1   |     1 |     7 |     3   (0)| 00:00:01 |                          
|   3 |   TABLE ACCESS FULL| T2   |  1000 |  7000 |     3   (0)| 00:00:01 |                          
---------------------------------------------------------------------------                         
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   1 - access("T1"."C1"="T2"."C2")                                                                  
   2 - filter("T1"."C2"=1)                                                                          

16 개의 행이 선택되었습니다.

경   과: 00:00:00.09
SQL > 


  • 왜 Nested Loops Join과 Hash Join에서 Access Predicate가 표현되는 방식의 차이가 발생하는가?
  • 두 Join의 동작방식의 차이에서 비롯된다.
구분방식결론
Nested Loops Join선행 Table읽으면서 후행 Table을 한번씩 Access실제 Join은 후행 Table에 대한 Access에서 발생하기 때문에 실제 Join은 후행 Table에 대한 Acccess에서 발생한다.
그러므로 후행 Table을 읽는 단계가 Access Predicate가 된다.
Hash Join선행 Table을 먼저 Build한 후, 후행 Table과 한번에 Join실제 Join이 발생하는 Hash Join 단계가 Access Predicat로 표현된다.


Format

  • DBMS_XPLAN 패키지의 파라미터중 Format의 옵션5가지(BASIC,TYPICAL,ALL,OUTLINE,ADVANCED)에 대하여 알아본다. 자세한 내용은 아래 링크 클릭~
  • DBMS_XPLAN Format


DBMS_XPLAN.DISPLAY_CURSOR

  • 단일 SQL문에 대해 실제 수행된 실행계획을 보여주는 Function


Function Parameter


FUNCTION DISPLAY_CURSOR(SQL_ID          VARCHAR2 DEFAULT NULL,
                        CURSOR_CHILD_NO INTEGER  DEFAULT 0,
                        FORMAT          VARCHAR2 DEFAULT 'TYPICAL'))

파라미터 설명
파라미터설명
TABLE_NAME실제 수행된 SQL의 SQL_ID 값을 입력하면 해당 SQL의 실행계획을 볼 수 있다.
SQL_ID 값은 V$SQL, V$SQLAREA에서 확인할 수 있다.
SQL_ID 값을 명시하지 않으면 해당 세션의 마지막에 실행된 문장을 의미한다.
STATEMENT_ID해당 SQL_ID의 CHILD NUMBER 값을 지정한다.
CURSOR_CHILD_NO 값을 명시하지 않으면 해당 SQL_ID의 첫번째 CURSOR_CHILD_NO 값을 가져온다.
FORMAT저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터


사용조건
  • Oracle 10g에 추가된 기능이다.
  • 이 Package를 사용하기 위해서는 아래의 객체에SELECT 권한이있어야 한다.
    • V$SESSION
    • V$SQL_PLAN
    • V$SQL(optional)
    • V$SQL_PLAN_STATISTICS_ALL

오라클은 라이브러리 캐시에 캐시에 캐싱돼 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql 뷰를 제공한다. 이것과 함께 _id 값으로 조인해서 사용할 수 있도록 제공되는 뷰가 몇 가지 더 있는데, 그 중 활용도가 가장 높은 것이 v$sql_plan과 v$sql_plan_statistics다. 그리고 이 두 뷰를 합쳐서 보여주는 것이 V$SQL_PLAN_STATISTICS_ALL 이다.

  • PLAN_STATISTICS 정보는 다음의 조건 중 하나를 만족해야 기록된다.
    • 'STATISTICS_LEVEL' Parameter 값을 ALL로변경한경우
    • '_ROWSOURCE_EXECUTION_STATISTICS' Parameter 값을TRUE로변경한경우
    • GATHER_PLAN_STATISTICS HINT를 사용할때, 10g부터지원
  • 쿼리 실행전에 SERVEROUTPUT을 OFF하는것을 잊지말자.
  • DISPLAY_CURSOR 을 조회하려면 sql_id와 child_number를 알아야한다. SERVEROUTPUT을 OFF하지 않으면 dbms_output.disable프로시저를 호출하는 커서의 sql_id를 찾게 된다.
  • SQL> SET SERVEROUTPUT OFF;


FORMAT

구분FORMAT내용
예측내용BASICDBMS_XPLAN.DISPLAY 포멧과 같은 내용
예측내용TYPICALDBMS_XPLAN.DISPLAY 포멧과 같은 내용
예측내용ALLDBMS_XPLAN.DISPLAY 포멧과 같은 내용
예측내용OUTLINEDBMS_XPLAN.DISPLAY 포멧과 같은 내용
예측내용ADVANCEDDBMS_XPLAN.DISPLAY 포멧과 같은 내용
실측내용ALLSTATS실제 액세스한 로우수와 수행시간 그리고 CR, PR, PW 정보를 보여준다.
수행횟수에 따라 누적된 값을 보여준다
실측내용ALLSTATS LAST실제 액세스한 로우수와 수행시간 그리고 CR, PR, PW 정보를 보여준다.
가장 최근에 수행된 값만 보여준다.
실측내용ADVANCED ALLSTATS LASTDBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 Format의
정보를 보여준다.


Runtiime Executiion Plan 확인방법

  • 예측실행계획과 실제실행계획을 비교하여 본다.


예측실행계획
  • SQL 실행

SQL >  set serveroutput off
SQL >  select *
  2    from scott.emp e, scott.dept d
  3   where d.deptno = e.deptno
  4     and e.sal >= 1000;

~~~~~~~~~~~~~
24 개의 행이 선택되었습니다.


  • V$SESSION에서 SQL_ID 확인

SQL > SELECT PREV_SQL_ID, PREV_CHILD_NUMBER
  2    FROM V$SESSION
  3   WHERE SID = USERENV('sid')
  4     AND USERNAME IS NOT NULL
  5     AND PREV_HASH_VALUE <> 0;

PREV_SQL_ID   PREV_CHILD_NUMBER              
------------- -----------------              
2ja7z9r1v93a2                 0              

1 개의 행이 선택되었습니다.


  • DBMS_XPLAN.DISPLAY_CURSOR 조회

SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2ja7z9r1v93a2', 0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  2ja7z9r1v93a2, child number 0                                                               
-------------------------------------                                                               
 select *   from scott.emp e, scott.dept d  where d.deptno = e.deptno                               
 and e.sal >= 1000                                                                                  
                                                                                                    
Plan hash value: 615168685                                                                          
                                                                                                    
-----------------------------------------------------------------------                             
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem |                             
-----------------------------------------------------------------------                             
|*  1 |  HASH JOIN         |      |     24 |   825K|   825K|  659K (0)|                             
|   2 |   TABLE ACCESS FULL| DEPT |      4 |       |       |          |                             
|*  3 |   TABLE ACCESS FULL| EMP  |     24 |       |       |          |                             
-----------------------------------------------------------------------                             
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   1 - access("D"."DEPTNO"="E"."DEPTNO")                                                            
   3 - filter("E"."SAL">=1000)                                                                      
                                                                                                    
Note                                                                                                
-----                                                                                               
   - dynamic sampling used for this statement                                                       
   - Warning: basic plan statistics not available. These are only collected when:                   
       * hint 'gather_plan_statistics' is used for the statement or                                 
       * parameter 'statistics_level' is set to 'ALL', at session or system level                   
                                                                                                    

28 개의 행이 선택되었습니다.

경   과: 00:00:00.81


수행한SQL이 다음의 조건을 충족하지 않았기 때문에 기록이 안됨

  • 'STATISTICS_LEVEL' Parameter 값을 ALL로변경한경우
  • '_ROWSOURCE_EXECUTION_STATISTICS' Parameter 값을TRUE로변경한경우
  • GATHER_PLAN_STATISTICS HINT를사용할때, 10g부터지원


실제실행계획

SQL > ALTER SESSION SET "_ROWSOURCE_EXECUTION_STATISTICS" = TRUE;

세션이 변경되었습니다.
SQL > set serveroutput off

-- ALTER SESSION SET "_ROWSOURCE_EXECUTION_STATISTICS" = TRUE; 대신 
-- /*+ GATHER_PLAN_STATISTICS */ 힌트를 사용해도 된다.
SQL > select *
  2   from scott.emp e, scott.dept d
  3  where d.deptno = e.deptno
  4    and e.sal >= 1000;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO     DEPTNO DNAME          LOC  
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- ----
      8499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30         30 SALES          CHICAG
      8521 WARD       SALESMAN        7698 81/02/22       1250        500         30         30 SALES          CHICAG

     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

24 개의 행이 선택되었습니다.

경   과: 00:00:00.25
SQL >  SELECT PREV_SQL_ID, PREV_CHILD_NUMBER
  2    FROM V$SESSION
  3   WHERE SID = USERENV('sid')
  4     AND USERNAME IS NOT NULL
  5     AND PREV_HASH_VALUE <> 0;

PREV_SQL_ID   PREV_CHILD_NUMBER                                                                     
------------- -----------------                                                                     
aduuuwpa8f64v                 0                                                                              

1 개의 행이 선택되었습니다.

경   과: 00:00:00.11

SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('aduuuwpa8f64v', 0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  aduuuwpa8f64v, child number 0                                                                
-------------------------------------                                                               
select *  from scott.emp e, scott.dept d where d.deptno = e.deptno   and e.sal >= 1000              
                                                                                                    
Plan hash value: 615168685                                                                          
                                                                                                    
----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-
----------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |     24 |     24 |00:00:00.01 |      16 |   825K|   825K|  679K (0)|             
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |       
|*  3 |   TABLE ACCESS FULL| EMP  |      1 |     24 |     24 |00:00:00.01 |       9 |       |       |          |       
----------------------------------------------------------------------------------------------------
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   1 - access("D"."DEPTNO"="E"."DEPTNO")                                                            
   3 - filter("E"."SAL">=1000)                                                                      
                                                                                                    
Note                                                                                                
-----                                                                                               
   - dynamic sampling used for this statement                                                       
                                                                                                    

24 개의 행이 선택되었습니다.

경   과: 00:00:00.45
SQL > 


Note
: 부가적으로 필요한 정보를 제공한다. 위 예문에서 는 dynamic sampling 이 사용되었음 알려준다.
Oracle 10g에서는 통계 정보가 없는 Table 에 대해서 dynamic sampling 을 수행한다.


예측실행계획과 실제실행계획 비교
  • A-Rows, A-Time, Buffer 추가정보보여줌
  • E : Estimate, A : Actual


DBMS_XPLAN Package의 실행계획의 항목


SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7ww3qp7ahvbkk', 0, 'BASIC ROWS BYTES COST PREDICATE'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:                                                                            
------------------------                                                                            
 select *  from scott.emp e, scott.dept d where d.deptno = e.deptno                                 
and e.sal >= 1000                                                                                   
                                                                                                    
Plan hash value: 615168685                                                                          
                                                                                                    
----------------------------------------------------------------                                    
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|                                          
----------------------------------------------------------------                                    
|   0 | SELECT STATEMENT   |      |       |       |     7 (100)|                                             
|*  1 |  HASH JOIN         |      |    28 |  1876 |     7  (15)|                                             
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)|                                            
|*  3 |   TABLE ACCESS FULL| EMP  |    28 |  1036 |     3   (0)|                                        
----------------------------------------------------------------                                    
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   1 - access("D"."DEPTNO"="E"."DEPTNO")                                                            
   3 - filter("E"."SAL">=1000)                                                                      
                                                                                                    

22 개의 행이 선택되었습니다.

경   과: 00:00:01.57
SQL > 


이문서의 내용은 아래 책,문서를 참고했습니다.

  • THOMAS KYTE 저, 박민호 역의 이펙티브오라클
  • 강정식군 오라클클럽 세미나자료, 오라클클럽세미나_4.DMBS_XPLAN과 10046 Trace를 이용한 SQL문 튜닝
  • 조동욱저, Oracle Optionizing Oracle Optimizer
  • 조시형저, 오라클 성능 고도화 원리와 해법I
"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3461

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입