- 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'));

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

- 커서 : 하드파싱과정을 거쳐서 메모리에 적재된 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'));

(실행결과)

- 참고로, 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'));

(실습)