使用TKPROF重置追踪文件的格式

tkprof工具是大多数数据库管理员经常使用的，由oracle提供的，用于分析追踪文件的分析工具。其命令格式如下：

[oracle@rhf ~]$ 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

从其命令格式可以看出，tkprof 命令的主要参数只有两个：

tracefile ： 追踪文件

outputfile : 生成的输出文本

如下列子：

[oracle@rhf trace]$ tkprof orcl_ora_29583_rtrace.trc rhf1.prf

TKPROF: Release 11.2.0.3.0 - Development on 星期二 8月 30 15:37:47 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

[oracle@rhf trace]$ ls -l rhf1.prf

-rw-r--r-- 1 oracle oinstall 49829 Aug 30 15:37 rhf1.prf

生成的rhf1.prf可以直接通过文本编辑器查看。

其他重要参数释义：

waits : 指定输出文件中是否记录等待事件的汇总，默认值为是。waits=yes|no

sort ： 默认情况下，tkprof按照sql语句的执行顺序将它们在追踪文件中列出。sort的选项常用的有以下几种：

prscpu：解析所用的cpu时间

prsela ： 解析的运行时间

execpu： 执行所用的cpu时间

exeela ： 执行所用的运行时间

fchela ： 获取所用的运行时间

print ： 默认情况下，tkprof将会列出所有追踪的sql语句。通过print选项指定一个值，可以限制输出文件中所列出的sql语句个数。print=integer

sys：默认情况下，tkprof列出sys用户提交的所有sql语句，包括递归语句。为sys参数声明no，则tkprof会忽略这些语句。

explain ： 将执行计划写入输出文件。tkprof使用这个参数提供的用户名和密码连接到数据库，并提交解释计划语句。

table： 指定存储执行计划的表，默认的是explain参数提供的用户架构下的名为plan_table的表。

width ： 这是一个确定某些类型的输出行宽度的整型值。

示例：

[oracle@rhf trace]$ tkprof orcl_ora_29583_rtrace.trc rhf2.prf explain=system/oracle sort=exeela,prsela,fchela

[oracle@rhf trace]$ ls -l rhf2.prf

-rw-r--r-- 1 oracle oinstall 49906 Aug 30 16:00 rhf2.prf