DBMS_XPLAN 패키지

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

코드를 입력하세요

(1) 예상 실행계획 출력

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

• 첫번째 인자에는 실행 계획이 저장된 Plan_Table 명을 입력한다.
• 두번째 인자가 NULL일 경우 가장 마지막 explain_plan을 보여준다.
• 세번째 인자를 통해 5가지 포맷 옵션을 선택한다.
  • (Basic, Typical, All , Outline, Advanced)

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

• 오라클은 라이브러리 캐시에 캐싱되어 있는 수행 통계를 볼 수 있도록 v$sql 뷰를 제공한다.
• 활용도가 높은 뷰는 v$sql_plan과 v$sql_plan_statistics와 두개를 합친 v$sql_plan_statistics_all 이다.

1. 커서

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

2. 사용

• dbms_xplan.display_cursor함수를 이용해 조회가 가능하다.

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

• ms_xplan.display_awr 함수를 이용하면 AWR에 수집된 과거 수행 SQL에 대해서도 분석 작업을 할 수 있다.
  • AWR와 과거는 서로 짝꿍 키워드이다.

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

1. 수행 통계 출력 방법

[순서]
1. /+ gather_plan_statistics / 힌트를 사용한다. (set serveroutput off)
2. 시스템 또는 세션 레벨에서 statisticts_level 파라미터를 All로 설정한다.
⭐️ (단, 운영DB에서는 삼가해야한다 !!) ⭐️
3. _rowsource_execution_statisticts 파라미터를 True로 설정한다.
4. SQL 트레이스를 수행한다.
5. v$sql_plan_statistics 또는 v$sql_plan_statistics_all 뷰를 이용하여 조회한다.
6. 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'));

```sql
================================================================================================================
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  aduuuwpa8fv64v, 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)
================================================================================================================