通过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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1154766/