首先，开启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博客

通过10046与tkprof了解SQL运行