DBMS_XPLAN패키지

• DBMS_XPLAN 패키지를 통해 plan_table에 저장된 실행계획을 좀더 편리하게 볼수 있다.
• 오라클 10g부터는 라이브러리 캐시에 캐싱되어있는 SQL커서에 대한 실행계획뿐 아니라 Row Source별 수행통계까지 손쉽게 출력할수 있도록 기능이 확장되었다.
• AWR에 수집되었던 과거수행했던 SQL 실행계획을 확인하는 것도 가능하다.

(1) 실행계획 출력하기

• 저장된 실행계획 보기위해 오라클에서 제공하는 UTLXPLS.SQL, UTLXPLP.SQL 스크립트를 이용하면 된다고 했는데, 이 스크립트 열어보면, DBMS_XPLAN 패키지에 있는 DISPLAY function을 호출하고 있다.
  (병렬쿼리에 대한 실행계획을 보려면, UTLXPLP.SQL를 이용)

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

• 직접 DBMS_XPLAN.DISPLAY function을 호출하면 다양한 포맷 옵션을 선택할 수 있다.
  

• FORMAT을 아래와 함께 구사하면, 다양하게 이용가능
  ROWS, BYTES, COST, PARTITION, PARALLEL, PREDICATE, PROJECTION, ALIAS, REMOTE, NOTE

• 암것도 입력 안하면
  'PLAN_TABLE'에 담긴 실행계획정보중에서 마지막에 실행된 실행계획을 보여주는데, 출력포맷은 'TYPICAL'옵션으로 출력한다라는 의미\!

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

• 커서한 하드파싱과정을 거쳐서 메모리에 적재된 SQL과 Parse Tree, 실행계획, 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area를 말한다.
• 오라클은 라이브러리 캐시에 캐싱되어 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql 뷰를 제공.
• 이와 함께 sql_id 값과 조인에서 사용할 수 있도록 v$sql_plan, v$sql_plan_statistics, v$sql_plan_statistics_all 등의 뷰를 제공
• v$sql_plan 뷰를 일반 plan_table처럼 쿼리해서 조회할 수 있으나, dbms_xplan.display_cursor함수를 이용하면 편리.

• dbms_xplan.display_cursor함수
  단일 SQL문에 대해 실제 수행된 실행계획을 보여주는 Function
  
  

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

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

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