예측 실행계획, 실제 실행계획 및 Trace 확인법

정리해둡니다. 참고하실분들은 참고하세요.

--예측 실행계획-------------------------------------------------------------------

/*

1. EXPLAIN PLAN FOR 뒤에 쿼리문 작성 후 실행

2. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 실행

3. 끝. 단, 해당 실행계획은 예상 실행계획이므로 실제 실행계획과는 다를 수 있음

*/

EXPLAIN PLAN FOR

;

SELECT * FROM TABLE(DBMS_XPLAN_DISPLAY);

/*예시*/

EXPLAIN PLAN FOR

SELECT SYSDATE FROM DUAL;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

--실제 실행계획-------------------------------------------------------------------

/*

1. 쿼리 실행 전 STATISTICS_LEVEL을 ALL로 변경

2. 실행계획을 보고자하는 쿼리 실행

3. 하단 V$SQL에서 방금 돌린 쿼리를 검색하여 SQL_ID, CHILD_NUMBER를 확인

  + PARSING_SCHEMA_NAME은 해당 스키마명, MODULE은 알고있다면 쓰는게 좋으며(TOAD 등), SQL TEXT는 쿼리내용 입력(일부분만)

4. dbms_xplan.display_cursor를 이용하여 SQL_ID, CHILD_NUMBER 입력 후 쿼리실행

5. 실행계획 확인 후 STATISTICS_LEVEL을 BASIC로 변경

PS. SQL_ID, CHILD_NUMBER를 NULL로 할 경우 최근 실행된 쿼리의 실행계획을 보여준다.

    단, 운영중인 시스템에서는 사용이 힘들다.(마지막 실행된 쿼리가 계속 바뀌기 때문)

*/

ALTER SESSION SET STATISTICS_LEVEL = ALL;

select * from table(dbms_xplan.display_cursor(/*SQL_ID*/'',/*CHILD_NUMBER*/'0','ALLSTATS LAST'));

ALTER SESSION SET STATISTICS_LEVEL = BASIC;

/*예시*/

SELECT COUNT(2) FROM TN_PW00_APT_CD;

SELECT SUBSTR(SQL_TEXT, 1, 100) SQL_TEXT, SQL_ID, CHILD_NUMBER

FROM V$SQL

WHERE PARSING_SCHEMA_NAME = 'CITYWASTE'

AND MODULE = 'SQL Developer'

AND SQL_TEXT LIKE '%SELECT A.CTZN_SRNO%'

;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(/*SQL_ID*/'fyaw7c6z5t935',/*CHILD_NUMBER*/'0','ALLSTATS LAST'));

--SQL TRACE---------------------------------------------------------------------

/*

1. USER_DUMP_DEST가 어디에 설정되어있는지 확인(RAC등을 사용하여 이중화 된 경우 DB1,2 다를 수 있음)

2. TRACEFILE_IDENTIFIER='ZZZ' 를 설정하여 뒤에 생성할 TRACE 파일명 뒤에 _ZZZ를 붙임(구분하기 위함)

3. SQL_TRACE를 활성화

4. 쿼리 실행

5. SQL_TRACE 비활성화

6. TRACEFILE_IDENTIFIER='' 를 설정하여 기존대로 복구

7. UX등에서 USER_DUMP_DEST 경로로 이동 후 "TKPROF 현재파일명 변환할파일명" 입력

8. 변환된 파일을 메모장, 에디터플러스, 노트패드++ 등을 이용하여 확인

*/

SHOW PARAMETER USER_DUMP_DEST

ALTER SESSION SET TRACEFILE_IDENTIFIER='ZZZ';

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(1) FROM DUAL;

ALTER SESSION SET SQL_TRACE = FALSE;

ALTER SESSION SET TRACEFILE_IDENTIFIER='';

/*UX 등에서 실행*/

cd /oracle/product/.../trace

cd /oracle/product/.../trace

tkprof ..._ora_8112_ZZZ.trc ..._ora_8112_ZZZ.txt

==> 아래와 같이 나올 시 성공

TKPROF: Release 11.2.0.2.0 - Development on Tue Jul 28 17:06:59 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

/*끝*/