转 使用 Tkprof 分析 ORACLE 跟踪文件https://blog.csdn.net/tianlesoftware/article/details/5632003Tkprof是一个用于分析Oracle跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具。如果一个系统的执行效率比较低,一个比较好的方法是通过跟踪用户的会话并且使用Tkprof工具使用排序功能格式化输出,从而找出有问题的SQL语句。
一. TKPROF 命令语法:
TKPROF filename1, filename2 [ SORT = [ opion][,option] ]
[ PRINT = integer ]
[ AGGREGATE = [ YES | NO ] ]
[ INSERT = filename3 ]
[ SYS = [ YES | NO ] ]
[ [ TABLE = schema.table ] | [ EXPLAIN = user/password ] ]
[ RECORD = filename ]
相关说明:
filename1 指定的输入文件,可以是多个文件联起来。
Filename2 格式化输出文件。
SORT 在输出到输出文件前,先进程排序。如果省去,则按照实际使用的顺序输出到文件中。排序选项有以下多种:
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
PRINT 只列出输出文件的第一个integer 的SQL语句。默认为所有的SQL语句。
AGGREGATE 如果= NO ,则不对多个相同的SQL进行汇总。
INSERT SQL 语句的一种,用于将跟踪文件的统计信息存储到数据库中。在TKPROF创建脚本后,在将结果输入到数据库中。
SYS 禁止或启用 将SYS用户所发布的SQL语句列表到输出文件中。
TABLE 在输出到输出文件前,用于存放临时表的用户名和表名。
EXPLAIN 对每条SQL 语句确定其执行规划。并将执行规划写到输出文件中。
其中比较有用的一个排序选项是fchela,即按照elapsed time fetching来对分析的结果排序(记住要设置初始化参数timed_statistics=true),生成的文件将把最消耗时间的sql放在最前面显示。另外