通過10046與tkprof瞭解SQL執行

壹頁書發表於2014-05-06
SQL
首先，開啟10046事件
  1. --開啟10046
  2. alter session set events '10046 trace name context forever,level 12';
  3. --關閉10046
  4. alter session set events '10046 trace name context off';

  6. --或者
  7. exec dbms_system.set_ev(sid,serial#,10046,12,'go');
  8. exec dbms_system.set_ev(sid,serial#,10046,0,'go');

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

  1. --檢視當前使用的Trace檔案
  2. SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' AS trace_file
  3. FROM (
  4.     SELECT VALUE
  5.     FROM v$parameter
  6.     WHERE NAME = 'user_dump_dest'
  7. ) a, (
  8.     SELECT SUBSTR(VALUE, -6, 1) AS symbol
  9.     FROM v$parameter
  10.     WHERE NAME = 'user_dump_dest'
  11. ) b, (
  12.     SELECT instance_name
  13.     FROM v$instance
  14. ) c, (
  15.     SELECT spid
  16.     FROM v$session s, v$process p, v$mystat m
  17.     WHERE s.paddr = p.addr
  18.         AND s.SID = m.SID
  19.         AND m.statistic# = 0
  20. ) d
  21. ;
或者設定跟蹤檔案標識
alter session set tracefile_identifier='優化';

使用tkprof格式化Trace檔案
  1. tkprof mvbox_ora_13810.trc tkprof.txt sys=no sort=fchela


可以看到執行計劃的每一步的資源消耗情況,便於定位問題。
其中tkprof的選項
  1. print=integer 只列出前幾個SQL語句，integer 為指定的數量，例如print=10
  2. sys=no 不列出以SYS使用者執行的語句
  3. sort=option 排序選項， 選項列表如下：
  5. prscnt number of times parse was called
  6. prscpu cpu time parsing
  7. prsela elapsed time parsing
  8. prsdsk number of disk reads during parse
  9. prsqry number of buffers for consistent read during parse
  10. prscu number of buffers for current read during parse
  11. prsmis number of misses in library cache during parse
  12. execnt number of execute was called
  13. execpu cpu time spent executing
  14. exeela elapsed time executing
  15. exedsk number of disk reads during execute
  16. exeqry number of buffers for consistent read during execute
  17. execu number of buffers for current read during execute
  18. exerow number of rows processed during execute
  19. exemis number of library cache misses during execute
  20. fchcnt number of times fetch was called
  21. fchcpu cpu time spent fetching
  22. fchela elapsed time fetching
  23. fchdsk number of disk reads during fetch
  24. fchqry number of buffers for consistent read during fetch
  25. fchcu number of buffers for current read during fetch
  26. fchrow number of rows fetched
  27. 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章