4. DBMS_XPLAN 패키지

조회 수 7296 추천 수 0 2010.06.06 11:41:33
balto *.204.128.206

- DBMS_XPLAN 패키지를 이용하여 plan_table의 실행계획을 더 쉽게 출력해볼 수 있다.

▣ 예상 실행 계획 출력

- @/rdbms/admin/utlxpls 파일을 보면 dbms_xplan 패키지를 호출하고 있다.

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

- 2번째 인자 : 문장번호, 없으면 가장 마지막 explain plan 명령의 실행 계획

(문법)

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'BASIC'));

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'TYPICAL'));

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'SERIAL'));

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'ALL'));

- 옵션 선택 사용 가능

(ROWS BYTES COST PARTITION PARALLEL PREDICATE PROJECTION ALIAS REMOTE NOTE)

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'BASIC ROWS BYTES COST'));

- 모두 볼 경우

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'ALL'));

- 힌트 목록을 볼 경우

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'OUTLINE'));

- ALL+OUTLINE=ADVANCED

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'ADVANCED'));

(실습)

explain plan set statement_id='SQL1' for

select * from emp e, dept d where d.deptno=e.deptno and e.sal > 1000;

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'ADVANCED'));


 PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    11 |   627 |     4   (0)|
|   1 |  NESTED LOOPS                |         |    11 |   627 |     4   (0)|
|*  2 |   TABLE ACCESS FULL          | EMP     |    11 |   407 |     3   (0)|
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)|
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|
-----------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."SAL">1000)
   4 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
   - 'PLAN_TABLE' is old version

▣ 캐싱된 커서의 실행 계획 출력

- 커서 : 하드파싱과정을 거쳐서 메모리에 적재된 SQL과 Parse Tree, 실행계획, 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area를 말한다.

- 오라클은 라이브러리 캐시에 캐싱되어 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql 뷰를 제공.

- 이와 함께 sql_id 값으로 조인에서 사용할 수 있도록 v$sql_plan, v$sql_plan_statistics, v$sql_plan_statistics_all 등의 뷰를 제공

(실습)

set serveroutput off

select * from emp e, dept d where d.deptno=e.deptno and e.sal >= 1000;

column prev_sql_id new_value sql_id

column prev_child_number new_value child_no

select prev_sql_id, prev_child_number from v$session

where sid=userenv('sid') and username is not null

and prev_hash_value <> 0;

- v$sql_plan 뷰를 일반 plan_table처럼 쿼리해서 조회할 수 있으나, dbms_xplan.display_cursor함수를 이용하면 편리.

select * from table(dbms_xplan.display_cursor('sql_id', child_no, 'format'));

select * from table(dbms_xplan.display_cursor('&sql_id', &child_no, 'BASIC ROWS BYTES COST PREDICATE'));

(실행결과)

 PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from emp e, dept d where d.deptno=e.deptno and e.sal >= 1000

Plan hash value: 351108634
-----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     4 (100)|
|   1 |  NESTED LOOPS                |         |    12 |   684 |     4   (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL          | EMP     |    12 |   444 |     3   (0)|
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)|
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."SAL">=1000)
   4 - access("D"."DEPTNO"="E"."DEPTNO")


- 참고로, dbms_xplan.display_awr함수를 이용하면 AWR에 수집된 과거 수행되었던 SQL에 대해서도 같은 분석작업을 진행할 수 있다.

▣ 캐싱된 커서의 Row Source별 수행통계 출력

- SQL문에 gather_plan_statistics 힌트를 사용하거나, 시스템 또는 세션레벨에서 statistics_level파라미터를 all로 설정하면, 오라클은 실제 SQL을 수행하는 동안의 실행계획 각 오퍼레이션 단계(Row Source)로 수행통계를 수집한다.

(실습)

set serveroutput off

select /*+ gather_plan_statistics */ *

from emp e, dept d

where d.deptno=e.deptno and e.sal >=1000

select *

from v$sql_plan_statistics_all

where sql_id='$sql_id' and child_number=&child_no

order by id;

- dbms_xplan.display_cursor 사용

select * from table(dbms_xplan.display_cursor('...', 0, 'IOSTATS'));

select * from table(dbms_xplan.display_cursor('...', 0, 'MEMSTATS'));

select * from table(dbms_xplan.display_cursor('...', 0, 'IOSTATS MEMSTATS'));

select * from table(dbms_xplan.display_cursor('...', 0, 'ALLSTATS'));

(실습)

 SQL>  select * from table(dbms_xplan.display_cursor('90x6aga13xhu3', 0, 'ALLSTATS'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  90x6aga13xhu3, child number 0

-------------------------------------
select * from emp e, dept d where d.deptno=e.deptno and e.sal >= 1000

Plan hash value: 351108634
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |         |      1 |     12 |     10 |00:00:00.02 |      20 |      1 |
|*  2 |   TABLE ACCESS FULL          | EMP     |      1 |     12 |     10 |00:00:00.01 |       8 |      0 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     10 |      1 |     10 |00:00:00.02 |      12 |      1 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     10 |      1 |     10 |00:00:00.02 |       2 |      1 |

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."SAL">=1000)
   4 - access("D"."DEPTNO"="E"."DEPTNO")