꿈꾸는 개발자, DBA 커뮤니티 구루비

h1.01.Explain plan
h3.준비

explain plan을 사용하기 위해 plan_table이 존재해야한다.
plan_table이 존재하지 않을 시 다음을 수행(10g 이후 부터 기본적으로 설치되어 있다.)


SQL> @?/rdbms/admin/utlxplan.sql;
=> 내용 : create table PLAN_TABLE ~

h3.사용

explan plan for 명령을 통해 수행하며 수행 결과는 plan_table에 저장된다.
(set statement_id = 'query1' 생략 가능)


SQL> explain plan set statement_id = 'query1' for
  2  select * from test_table1
  3  where col1 = '12345';

참고

statement_id 를 중복으로 부여하는 경우 plan_table에 이중으로 데이터가 등록되어 포매팅 쿼리 이용시 올바르지 않은 실행계획이 출력된다.(패키지 이용시에는 동일한 statement_id에 대해 최신버전 실행계획 보여줌)

중복된 statement_id가 입력되는 것을 차단 하기 위해 unique index를 생성
=> create unique index uidx_plan_table_statment_id on plan_table(statement_id,id);

plan_table에 저장된 정보를 포맷팅하려면 별도의 쿼리가 필요하다.


SELECT lpad(id, 4, ' ') || nvl(lpad(parent_id, 6, ' '), '     ')
    || ' ' || lpad(' ', (level-1)*2, ' ')
    || operation || nvl2(options, ' (' || options || ')', '')
    || nvl2(object_name, ' OF '''
    || object_owner || '.' || object_name, NULL)
    || nvl2(object_name, '''', '')
    || decode(parent_id, null, ' Optimizer=' || optimizer)
    || (case
        when cost is null and cardinality is null and bytes is null
        then ''
        else ' (' || nvl2(cost, 'Cost=' || cost, '')
                  || nvl2(cardinality, ' Card=' || cardinality, '')
                  || nvl2(bytes, ' Bytes=' || bytes, '')
                  || ')' end) "Execution Plan"
FROM plan_table p
START WITH statement_id = 'query1' AND id = 0
CONNECT BY prior id = parent_id AND prior statement_id = statement_id
ORDER BY id;

Execution Plan
-----------------------------------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=52)
   1     0   INDEX (UNIQUE SCAN) OF TEST_TABLE1_PK' (Cost=1 Card=1 Bytes=52)

9i부터는 오라클이 제공해주는 utlxpls.sql 또는 utlxplp.sql 스크립트를 사용할 수 있다.
utlxpls.sql => 내용 : select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
utlxplp.sql => 내용 : select * from table(dbms_xplan.display());


SQL> set lines 200;
SQL> @?/rdbms/admin/utlxpls;

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

Plan hash value: 11495898

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |    52 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| TEST_TABLE1_PK |     1 |    52 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

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


   1 - access("COL1"='12345')

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

Explain plan (by hrson81) [2016.12.01]