通過10046與tkprof瞭解SQL執行

首先，開啟10046事件

--開啟10046

alter session set events '10046 trace name context forever,level 12';

--關閉10046

alter session set events '10046 trace name context off';

--或者

exec dbms_system.set_ev(sid,serial#,10046,12,'go');

exec dbms_system.set_ev(sid,serial#,10046,0,'go');

執行SQL語句,然後檢視跟蹤日誌檔案。
獲取Trace檔案路徑。

--檢視當前使用的Trace檔案

SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' AS trace_file

FROM (

    SELECT VALUE

    FROM v$parameter

    WHERE NAME = 'user_dump_dest'

) a, (

    SELECT SUBSTR(VALUE, -6, 1) AS symbol

    FROM v$parameter

    WHERE NAME = 'user_dump_dest'

) b, (

    SELECT instance_name

    FROM v$instance

) c, (

    SELECT spid

    FROM v$session s, v$process p, v$mystat m

    WHERE s.paddr = p.addr

        AND s.SID = m.SID

        AND m.statistic# = 0

) d

;

或者設定跟蹤檔案標識
alter session set tracefile_identifier='優化';

使用tkprof格式化Trace檔案

tkprof mvbox_ora_13810.trc tkprof.txt sys=no sort=fchela

可以看到執行計劃的每一步的資源消耗情況,便於定位問題。
其中tkprof的選項

print=integer 只列出前幾個SQL語句，integer 為指定的數量，例如print=10

sys=no 不列出以SYS使用者執行的語句

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

參考:
http://www.askmaclean.com/archives/maclean-tech-tkprof-10046.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1154766/，如需轉載，請註明出處，否則將追究法律責任。

通過10046與tkprof瞭解SQL執行

相關文章