오라클 성능 고도화 원리와 해법 I (2009년)
DBMS_XPLAN 패키지 0 0 3,611

by 구루비 DBMS_XPLAN [2009.11.16]


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별 수행통계가 수집된다.)
"코어 오라클 데이터베이스 스터디 모임" 에서 2009년에 "오라클 성능 고도화 원리와 해법 I " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3022

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입