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这步

使用MySQL时的遇到问题整理SQL tuning和shared pool结构的关联介绍相关资讯 Oracle基础知识

查看Oracle 32位还是64位（x86 or （10/05/2014 19:10:00）
Oracle中表的建立与修改-五种约束（02/07/2013 09:49:18）
Oracle select 语句字段连接（12/17/2012 14:51:53）

Oracle online redo log 基础知识（02/09/2013 09:43:04）
如何查看Oracle数据库的session阻（01/01/2013 09:11:15）
Oracle Redo Log 及Logmnr配置使用（12/14/2012 19:36:51）

本文评论查看全部评论（0）