oracle工具:tkprof简析
1 定义
用于分析oracle跟踪文件并且可按需产生一个更加清晰合理的输出结果的可执行工具
2 首选项
[sql]
F:\>tkprof www.2cto.com
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
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 www.2cto.com
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
注释：
① fchela这是个比较有用的sort选项，在生成的.prf文件将把最消耗时间的sql放在最前面显示
② sys=no 以sys身份执行的sql不会被显示，增加可读性
3 tkprof输出文件中各列的含义
[sql]
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
注释： www.2cto.com
㈠ call：
每次sql的处理都分成3个部分
① parse：将sql语句转成执行计划，包括检查权限、表及其关联对象是否存在等
② execute：由sql引擎执行，因事务类型不同而异
③ fetch：这步只有select语句被执行，buffer cache是主战场
㈡ count：
这条sql被parse、execute和fetch的次数
㈢ cpu：该sql语句所消耗的cpu的时间（单位：s）
㈣ elapsed：该sql语句在各个阶段的执行时间
㈤ disk：
从磁盘上的数据文件中物理读取的块的数量
㈥ query：
在一致性读的模式下，各阶段所获得的buffer的数量。一致性模式下，buffer是用于给一个长查询提供一致性
读的快照
㈦ current：
在当前读的模式下，各阶段所获得的buffer的数量。
㈧ rows：
所有的sql返回的记录数目，但不包括子查询返回的记录。
对于select语句，返回是在fetch这步
对于DML语句，返回是在execute这步
www.2cto.com
4 tkprof的使用步骤
⑴ 在实例级别上设置timed_statistic=true
SQL> show parameter timed_statistic
NAME_COL_PLUS_SHOW_P TYPE VALUE_COL_PLUS_SHOW_
-------------------- ---------------------- --------------------
timed_statistics boolean TRUE
⑵ 使用各种工具得到想要查看session的trc文件