SQL>EXPLAIN PLAN FOR
SELECT * FROM SCOTT.EMP; --要解析的SQL腳本
SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SQL>SET TIMING ON 　　　　　　　　　　　　--控制顯示執(zhí)行時(shí)間統(tǒng)計(jì)數(shù)據(jù)
SQL>SET AUTOTRACE ON EXPLAIN 　　　 　　--這樣設(shè)置包含執(zhí)行計(jì)劃、腳本數(shù)據(jù)輸出，沒(méi)有統(tǒng)計(jì)信息
SQL>執(zhí)行需要查看執(zhí)行計(jì)劃的SQL語(yǔ)句
SQL>SET AUTOTRACE OFF 　　　　　　　　　 --不生成AUTOTRACE報(bào)告，這是缺省模式
SQL> SET AUTOTRACE ON 　　　　　　　　　 --這樣設(shè)置包含執(zhí)行計(jì)劃、統(tǒng)計(jì)信息、以及腳本數(shù)據(jù)輸出
SQL>執(zhí)行需要查看執(zhí)行計(jì)劃的SQL語(yǔ)句
SQL>SET AUTOTRACE OFF
SQL> SET AUTOTRACE TRACEONLY 　　　　　--這樣設(shè)置會(huì)有執(zhí)行計(jì)劃、統(tǒng)計(jì)信息，不會(huì)有腳本數(shù)據(jù)輸出
SQL>執(zhí)行需要查看執(zhí)行計(jì)劃的SQL語(yǔ)句
SQL>SET AUTOTRACE TRACEONLY STAT --這樣設(shè)置只包含有統(tǒng)計(jì)信息
SQL>執(zhí)行需要查看執(zhí)行計(jì)劃的SQL語(yǔ)句

SQL> ALTER SESSION SET SQL_TRACE=TRUE;
SQL> SELECT * FROM SCOTT.EMP;
SQL> ALTER SESSION SET SQL_TRACE =FALSE;

SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T

TKPROF 選項(xiàng)
選項(xiàng) 說(shuō)明
TRACEFILE 跟蹤輸出文件的名稱
OUTPUTFILE 已設(shè)置格式的文件的名稱
SORT=option 語(yǔ)句的排序順序
PRINT=n 打印前 n 個(gè)語(yǔ)句
EXPLAIN=user/password 以指定的用戶名運(yùn)行 EXPLAIN PLAN
INSERT=filename 生成 INSERT 語(yǔ)句
SYS=NO 忽略作為用戶 sys 運(yùn)行的遞歸 SQL 語(yǔ)句
AGGREGATE=[Y|N] 如果指定 AGGREGATE = NO TKPROF 不聚集相同
SQL 文本的多個(gè)用戶
RECORD=filename 記錄在跟蹤文件中發(fā)現(xiàn)的語(yǔ)句
TABLE=schema.tablename 將執(zhí)行計(jì)劃放入指定的表而不是缺省的PLAN_TABLE

可以在操作系統(tǒng)中鍵入 tkprof 以獲得所有可用選項(xiàng)和輸出的列表
注 排序選項(xiàng)有

排序 選項(xiàng)說(shuō)明
prscnt execnt fchcnt 調(diào)用分析執(zhí)行提取的次數(shù)
prscpu execpu fchcpu 分析執(zhí)行提取所占用的 CPU 時(shí)間
prsela exela fchela 分析執(zhí)行提取所占用的時(shí)間
prsdsk exedsk fchdsk 分析執(zhí)行提取期間的磁盤(pán)讀取次數(shù)
prsqry exeqry fchqry 分析執(zhí)行提取期間用于持續(xù)讀取的緩沖區(qū)數(shù)
prscu execu fchcu 分析執(zhí)行提取期間用于當(dāng)前讀取的緩沖區(qū)數(shù)
prsmis exemis 分析執(zhí)行期間庫(kù)高速緩存未命中的次數(shù)
exerow fchrow 分析執(zhí)行期間處理的行數(shù)
userid 分析游標(biāo)的用戶的用戶 ID

TKPROF 統(tǒng)計(jì)數(shù)據(jù)
Count: 執(zhí)行調(diào)用數(shù)
CPU: CPU 的使用秒數(shù)
Elapsed: 總共用去的時(shí)間
Disk: 物理讀取次數(shù)
Query: 持續(xù)讀取的邏輯讀取數(shù)
Current: 當(dāng)前模式下的邏輯讀取數(shù)
Rows: 已處理行數(shù)
TKPROF 統(tǒng)計(jì)信息
統(tǒng)計(jì) 含義
Count 分析或執(zhí)行語(yǔ)句的次數(shù)以及為語(yǔ)句發(fā)出的提取調(diào)用數(shù)
CPU 每個(gè)階段的處理時(shí)間以秒為單位如果在共享池中找到該語(yǔ)句對(duì)于分析階段為 0
Elapsed 占用時(shí)間以秒為單位通常不是非常有用因?yàn)槠渌M(jìn)程影響占用時(shí)間
Disk 從數(shù)據(jù)庫(kù)文件讀取的物理數(shù)據(jù)塊如果該數(shù)據(jù)被緩沖則該統(tǒng)計(jì)可能很低
Query 為持續(xù)讀取檢索的邏輯緩沖區(qū)通常用于 SELECT 語(yǔ)句
Current 在當(dāng)前模式下檢索的邏輯緩沖區(qū)通常用于 DML 語(yǔ)句
Rows 外部語(yǔ)句所處理的行對(duì)于 SELECT 語(yǔ)句在提取階段顯示它對(duì)于 DML 語(yǔ)句在執(zhí)行階段顯示它

Query 和Current 的總和為所訪問(wèn)的邏輯緩沖區(qū)的總數(shù)

TKPROF: Release 10.2.0.1.0 - Production on 星期三 5月 23 16:56:41 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
begin :id := sys.dbms_transaction.local_transaction_id; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 2
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
SELECT *
FROM
SCOTT.EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 14
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT MODE: CHOOSE
TABLE ACCESS MODE: ANALYZED (FULL) OF 'EMP' (TABLE)
********************************************************************************
ALTER SESSION SET SQL_TRACE = FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89 (ETL)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 2
Fetch 1 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 7 0 16
Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
user SQL statements in session.
internal SQL statements in session.
SQL statements in session.
statement EXPLAINed in this session.
********************************************************************************
Trace file: D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc
Trace file compatibility: 10.01.00
Sort options: default
session in tracefile.
user SQL statements in trace file.
internal SQL statements in trace file.
SQL statements in trace file.
unique SQL statements in trace file.
SQL statements EXPLAINed using schema:
ETL.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
lines in trace file.
elapsed seconds in trace file.

SELECT SID, SERIAL#, USERNAME FROM V$SESSION WHERE USERNAME = 'ETL'
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(61,76,TRUE);
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(61,76,FALSE);

ALTER SESSION SET TRACEFILE_IDENTIFIER = 10046;
ALTER SESSION SET EVENTS='10046 trace name context forever, level 8';
SELECT * FROM SCOTT.EMP;
ALTER SESSION SET EVENTS ='10046 trace name context off';
然后你可以用腳本查看追蹤文件的位置
SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T
查詢結(jié)果為wgods_ora_28279.trc文件， 但是去相應(yīng)目錄卻沒(méi)有找到對(duì)應(yīng)的追蹤文件，而是如下trace文件：wgods_ora_28279_10046.trc

SELECT * FROM V$SQL_PLAN
SELECT * FROM V$RSRC_PLAN_CPU_MTH
SELECT * FROM V$SQL_PLAN_STATISTICS
SELECT * FROM V$SQL_PLAN_STATISTICS_ALL
SELECT * FROM V$SQLAREA_PLAN_HASH
SELECT * FROM V$RSRC_PLAN_HISTORY