DBMS_XPLAN 패키지

dbms_xplan 패키지를 통해 Plan_Table에 저장된 실행계획을 좀 더 쉽게 출력 할 수 있고,
10g부터는 실행계획은 물론 Row Source별 수행 통계까지 출력 가능함

(1) 예상 실행계획 출력

첫번째 인자에는 실행 계획이 저장된 Plan_Table 명을 입력하고,
두번째 인자(statement_id)가 NULL일 경우 가장 마지막 explain_plan을 보여주며
세번째 인자(포맷옵션)를 통해 "Basic, Typical, SERIAL, All , Outline, Advanced" 선택할 수 있음

SQL> select plan_table_output
       from table (dbms_xplan.display('plan_table',null,'all'));

Outline = 같은 실행계획을 수립하는데 필요한 힌트 목록 보여줌
Advanced = all + outlien

(2) 캐싱된 커서의 실제 실행계획 출력

1)커서란 하드파싱 과정을 거쳐 메모리에 적재된 SQL과 Parse Tree,
실행 계획 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area
2)오라클은 라이브러리 캐시에 캐싱되어 있는 수행 통계를 볼 수 있도록 v$sql 뷰를 제공
3)활용도가 높은 뷰는 v$sql_plan과 v$sql_plan_statistics와 두개를 합친 v$sql_plan_statistics_all
4)dbms_xplan.display_cursor함수를 이용해 조회 가능
라이브러리 캐시에 현재 캐싱돼 있는 sql 커서의 실제 실행계획, 실행계획을 만들면서 예상한 rows, bytes,cost, time 정보 보여줌.

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

참고로 ms_xplan.display_awr 함수를 이용하면 AWR에 수집된 과거 수행SQL에 대해서도 분석 작업을 할 수 있음

(3) 캐싱된 커서의 Row Source별 수행 통계 출력

1)수행 통계 출력

• /*\+ gather_plan_statistics \*/힌트를 사용
  

• 시스템 또는 세션 레벨에서 statisticts_level 파라미터를 All로 설정-->운영DB에서는 삼가해야함
• \_rowsource_execution_statisticts 파라미터를 True로 설정
• SQL 트레이스 수행
• v$sql_plan_statistics 또는 v$sql_plan_statistics_all 뷰를 이용하여 조회
• dbms_xplan.display_cursor함수를 이용해 조회 가능
  
  

2)항목 설명

• E-Rows는 SQL을 수행하기 전 옵티마이저가 각 Row Source별 예상했던 로우 수로서 v$sql_plan에서 읽어온 값
• A-Rows는 실제 수행 시 읽었던 로우 수로서 v$sql_plan_statistics에서 읽어온 값
• 기본적으로 누적값을 보여주며, 아래 처럼 Format에 last를 추가해주면 마지막 수행했을 때의 일량을 보여줌

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)