Tkprof工具可用來格式化sql trace產生的檔案，讓你更容易看懂trace的內容
用法：
tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]
引數說明：
tracefile：你要分析的trace檔案
outputfile：格式化後的檔案
explain=user/password@connectstring
table=schema.tablename
這兩個引數是一起使用的，通過連線資料庫對在trace檔案中出現的每條sql語句檢視執行計劃，並將之輸出到outputfile中
注意，該table必須是資料庫中不存在的，如果存在會報錯
print=n：只列出最初N個sql執行語句
insert=filename：會產生一個sql檔案，執行此檔案可將收集到的資料insert到資料庫表中
sys=no：過濾掉由sys執行的語句
record=filename：可將非巢狀執行的sql語句過濾到指定的檔案中去
waits=yes|no：是否統計任何等待事件
aggregate=yes|no：是否將相同sql語句的執行資訊合計起來，預設為yes
sort=option：設定排序選項，選項如下：
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
可根據自己的需要設定排序
舉例：（trace檔案可用sql trace去產生，在此略過）
1.列出前2條sql語句的執行情況：
C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt print=2
2.將資料儲存到資料庫：
C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt insert=c:\insert.sql
執行後會在c:\產生insert.sql檔案，執行該檔案即可將資料儲存到資料庫，以下為insert.sql部分內容：
REM Edit and/or remove the following CREATE TABLE
REM statement as your needs dictate.
CREATE TABLE tkprof_table
(
date_of_insert DATE
,cursor_num NUMBER
,depth NUMBER
,user_id NUMBER
,parse_cnt NUMBER
,parse_cpu NUMBER
,parse_elap NUMBER
,parse_disk NUMBER
,parse_query NUMBER
,parse_current NUMBER
,parse_miss NUMBER
,exe_count NUMBER
,exe_cpu NUMBER
,exe_elap NUMBER
,exe_disk NUMBER
,exe_query NUMBER
,exe_current NUMBER
,exe_miss NUMBER
,exe_rows NUMBER
,fetch_count NUMBER
,fetch_cpu NUMBER
,fetch_elap NUMBER
,fetch_disk NUMBER
,fetch_query NUMBER
,fetch_current NUMBER
,fetch_rows NUMBER
,ticks NUMBER
,sql_statement LONG
);
INSERT INTO tkprof_table VALUES
(
SYSDATE, 1, 0, 61, 1, 0, 418, 0, 0, 0, 1
, 1, 15625, 1435, 0, 0, 0, 0, 0
, 4, 0, 4417, 0, 24, 0, 36, 13450151
, ’select * from tblinventoryhistory
‘);
3.提取sql執行語句：
C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt record=sqlstr.sql
sqlstr.sql中的內容：
alter session set sql_trace=true ;
alter session set events ‘10046 trace name context forever,level 12′ ;
select * from tblinventoryhistory ;
select * from tblorder ;
select * from tblproduct ;
select * from tbluser ;
select * from tblroute ;
4.產生執行計劃：
C:\>tkprof c:\oc_ora_2892.trc c:\ff.txt explain=ocuser/ocuser table=ocuser.test1
在產生的ff.txt檔案中會體現其執行計劃：
Rows Execution Plan
——- —————————————————
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (FULL) OF ‘TBLROUTE’
小技巧：
1.如何查詢你產生的trace檔案：
可用eygle寫的指令碼去查詢：
SQL> select
2 d.value||’/'||lower(rtrim(i.instance, chr(0)))||’_ora_’||p.spid||’.trc’ trace_file_name
3 from
4 ( select p.spid
5 from sys.v$mystat m,sys.v$session s,sys.v$process p
6 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
7 ( select t.instance from sys.v$thread t,sys.v$parameter v
8 where v.name = ‘thread’ and (v.value = 0 or t.thread# = to_number(v.value))) i,
9 ( select value from sys.v$parameter where name = ‘user_dump_dest’) d
10 /
TRACE_FILE_NAME
——————————————————————————–
/opt/oracle/admin/hsjf/udump/hsjf_ora_1026.trc
2.sort選項可同時用多個，做法是用括號括起來，中間用逗號分割：
C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt sort=(prsela, exeela, fchela)
注意：最後排序是按照各個選項的數字之和進行排序，類似於order by (sort1+sort2+sort3)，而不是order by sort1,sort2,sort3
---------------------------------------------------------
3. 基本的使用步驟