tkprof是Oracle自带的命令行工具,主要用于将trace跟踪日志转化为易于阅读的格式化文本。
最简单的是 f:\app\WXH-NOTE\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_15096.trc orcl_ora_15096.txt
直接在cmd命令行里面输入tkprof查看相关参数
C:\Users\WXH-NOTE>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
参数说明
tracefile:你要分析的trace文件
outputfile:格式化后的文件
explain=user/password@connectstring
table=schema.tablename
PRINT:只列出输出文件的最初N个SQL语句。默认为所有的SQL语句。
AGGREGATE:如果= NO,则不对多个相同的SQL进行汇总。如果为yes则对trace文件中的相同sql进行合并。
INSERT:SQL 语句的一种,用于将跟踪文件的统计信息存储到数据库中。在TKPROF创建脚本后,在将结果输入到数据库中。
SYS:如果设置为yes,则所有sys用户的操作(也包含用户sql语句引发的递归sql),这样可以减少分析出来的文件的复杂度,便于查看。
TABLE:在输出到输出文件前,用于存放临时表的用户名和表名。
EXPLAIN:对每条SQL 语句确定其执行规划,并将执行规划写到输出文件中。如果不是有explain,在trace文件中我们看到的是SQL实际的执行路径,不会有sql的执行计划
sort:对trace文件的sql语句根据需要排序,其中比较有用的一个排序选项是fchela,即按照elapsed time fetching来对分析的结果排序(记住要设置初始化参数timed_statistics=true),生成的文件将把最消耗时间的sql放在最前面显示。
参考:
http://docs.oracle.com/cd/E11882_01/server.112/e41573/sqltrace.htm#PFGRF94992