Oracle 工具:tkprof
Oracle 工具:tkprof
1 定义
用于分析Oracle跟踪文件并且可按需产生一个更加清晰合理的输出结果的可执行工具
2 首选项
- F:\>tkprof
- 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
- 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输出文件中各列的含义
- call count cpu elapsed disk query current rows
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
注释:
㈠ 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这步
|
评论暂时关闭