Oracle Tkprof(Trace Kernel Profile)工具

曹巧

TKPROF介绍

Tkprof:分析ORACLE跟踪文件并且产生一个更加人性化清晰的输出结果的可执行工具

1)TKPROF的参数：
不输入任何参数，直接输入tkprof，回车，可以获得一个完整的参数列表.
C:\>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

2)几个重要参数的用法讲解
·sys参数，如果不指定默认值为yes.这个参数的含义是，输出文件中是否包含以SYS用户运行的sql语句。这个参数还是蛮有用的，我们执行sql语句的时候，后台经常会执行很多递归的语句，比如你输入了SELECT * FROM TEST;如果这个语句是硬解析的话，那么会产生很多递归的SQL，递归的去查询表的统计信息，列的统计信息，索引的统计信息等，当然递归的不止是这些。这些递归的sql都是以SYS用户运行的，如果你不希望看到这些递归SQL，那么就加上这个参数sys=no.

·record参数，它指定的是一个路径下的文件，这个文件用来生成在跟踪文件中找到的所有的非递归SQL。比如你在SQLPLUS里执行了三条语句,select * from a;select * from b;select * from c;，那么如果你指定了这个参数如：record=c:\test.log，那么你用tkprof格式化跟踪文件后，这个test.log里就会记录这三个SQL。这个特性在有些时候还是蛮有用的，因为跟踪文件往往都会比较大，找起来会比较费劲，我们可以通过指定这个参数先大体了解下，跟踪文件里都有哪些非递归SQL。而且这个功能还有助于我们重演SQL语句（绑定变量的不可以）。

·aggregate参数，它指定tkprof是否将同样文本内容的sql聚合处理，比如，你执行了十次select * from a,如果你指定这个参数为no（默认情况），那么产生的输出文件会有十个这样语句的执行信息，如果你指定的是yes，那么tkprof会把这十次的执行信息汇总显示。这个参数怎么指定就看你的需要了，个人觉得还是满有用的一个参数。

·sort参数，这个参数是经常使用到的一个参数，它用来指定tkprof输出文件里sql语句按照什么排序，默认是按照执行的先后顺序排序的，我们可以指定它按照其他方式排序，比如磁盘读取数，CPU时间等。这个参数最经常用的方式是：sort=prsela,exeela,fchela,其实这三个值加起来就是响应时间，即按照响应时间排序。这里别产生误解，tkprof会根据prsela,exeela,fchela三个值的和进行排序，而不是像SQL语句似的一个个的排序。

·print参数，它经常搭配sort参数一起使用，用来指定tkprof输出sql语句的数量。这两个参数搭配使用起来就比较妙，比如你想知道一个跟踪文件里响应时间排前十的SQL，那么你就可以sort=prsela,exeela,fchela print=10来搭配使用。

·explain参数，这个参数的含义是为每一个SQL提供一个执行计划。使用的方法是explain=用户名/密码，其实原理很简单，就是通过你指定的用户名，密码登陆数据库，然后为每一个sql执行以下explain plan for sql，输出到plan_table里，最后添加到输出文件里。注意，由于explain plan for 命令要求执行操作的用户要对sql语句里包含的对象都有执行权限，如果包含视图，也要对视图基于的基础表有执行权限，否则产生不了执行计划。注意增加了这个参数后，执行tkprof会比较慢。

·wait参数，指定输出文件中包含不包含等待事件，默认是包含的。一般都取默认值。

基本最常用到的就这些，其他的我就不介绍了，平时这些也就基本够用了。

TKPROF使用步骤

1.设置参数文件

设置三个参数timed_staticstices user_dump_dest max_dump_file_size

TIMED_STATICSTICES 用于启动或禁止对定时统计信息（如CUP时间、占用时间），以及动态性能表中多种统计信息的收集功能

alter session set timed_statistics true;
alter system set timed_statistics false;

MAX_DUP_FILE_SIZE 当实例层启用SQL TRACE的时候，在每次请求服务器的时候，都将在跟踪文件中产生一个文本行，这些文件的最大尺寸受限于初始化参数的设置。默认为500(blocks)。若里面的数据被截断则增大SIZE。若为UNLIMITED则意味着没有上限。

USER_DUMP_DEST 设置跟踪文件的存储位置。默认为admin/用户/udump;

alter system set user_dump_dest=newdir

2.启动SQL TRACE实用工具

对会话启动SQL TRACE
alter session set sql_trace=true;
alter session set sql_trace=false;
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION=（ SID ， SERIAL# ， TRUE ）；
SID，SERIAL#可以从V$SESSION视图获得。
ALTER SESSION SET EVENTS
alter session set events '10046 trace name context forever,level<n>';
alter session set events '10046 trace name context off';
alter system set events '10046 trace name context forever,level 1'
alter system set events '10046 trace name context off'

n=1---------激活标准SQL_TRACE工具，这与设置SQL_TRACE=TRUE 没有任何不同；
n=4---------激活标准SQL_TRACE，且可以扑获跟踪文件中的绑定变量。
n=8---------激活标准SQL_TRACE,且可在查询级上扑获跟踪文件中的等待事件。
n=12--------激活标准SQL_TRACE,并包括扑获绑定变量与等待事件。

对用户实例启动 SQL TRACE
alter system set sql_trace=true;
alter system set sql_trace=false;

3.使用tkprof格式化trace文件
Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]

table=schema.tablename Use 'schema.tablename' with 'explain=' option.
用于指定在将执行规划写进输出文件之前，TKPROF用于临时存放执行规划所用表的架构和名称

explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.

print=integer List only the first 'integer' SQL statements. 只列出输出文件中的第一个INTEGER的SQL语句，若忽略，则TKPROF将列出所有跟踪的SQL语句

aggregate=yes|no 若用户指定AGGREGATE=NO，TKPROF将不会对相同SQL文本的多个用户进行汇总

insert=filename List SQL statements and data inside INSERT statements. SQL脚本的一种，用于将跟踪文件的动机信息存储到数据库中

sys=no TKPROF does not list SQL statements run as user SYS. 于启动或禁止将用户SYS所发布的SQL语句列表到输出文件之中，也包括递归SQL(为执行用户的SQL语句，ORACLE还必须执行一些附加语句)语句在内。默认为YES

record=filename Record non-recursive statements found in the trace file. 对于跟踪文件中的所用非递归SQL语句，TKPROF 将以指定的名称来创建某个SQL脚本。用于对跟踪文件中的用户时间进行重放

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: 在将被跟踪的SQL语句列表输出到跟踪文件之前，先将其按照指定排序选项的降序关系对其进行排序；若指定了多种排序选项，那么根据排序选项所指定值的和的降序关系对其进行排序；若忽略此参数，那么TKPROF将按照使用次序把语句列表到输出文件中
prscnt number of times parse was called 语句解析的数目
prscpu cpu time parsing 语句解析所占用的CPU时间
prsela elapsed time parsing 语句解析所占用的时间（总是大于或等于CPU时间）；
prsdsk number of disk reads during parse 语句解析期间，从磁盘进行物理读取的数目
prsqry number of buffers for consistent read during parse 语句解析期间，一致模式块读取（CONSISTENT MODE BLOCK READ）的数目
prscu number of buffers for current read during parse 语句解析期间，当前模式读取（CURRENT MODE BLOCK READ）的数目
prsmis number of misses in library cache during parse 语句解析期间，库缓存失败的数目
execnt number of execute was called 语句执行的数目
execpu cpu time spent executing 语句执行所占用的CPU时间
exeela elapsed time executing 语句执行所占用的时间（总是大于或等于CPU时间）
exedsk number of disk reads during execute 语句执行期间，从磁盘进行物理读取的数目
exeqry number of buffers for consistent read during execute 语句执行期间，一致模式块读取（CONSISTENT MODE BLOCK READ）的数目
execu number of buffers for current read during execute 语句执行期间，当前模式读取（CURRENT MODE BLOCK READ）的数目
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 取数据所占用的CPU时间
fchela elapsed time fetching 取数据所占用的时间（总是大于或等于CPU时间）
fchdsk number of disk reads during fetch 取数据期间，从磁盘进行物理读取的数目
fchqry number of buffers for consistent read during fetch 取数据期间，一致模式块读取（CONSISTENT MODE BLOCK READ）的数目
fchcu number of buffers for current read during fetch 取数据期间，当前模式读取（CURRENT MODE BLOCK READ）的数目
fchrow number of rows fetched 所获取的行数
userid userid of user that parsed the cursor

4.tkprof文件的阅读

==================

ORACLE TKPROF使用步骤

1．Tkprof是一个分析ORACLE跟踪文件并且产生一个更加人性化清晰的输出结果的可执行工具。C:\oracle\ora92\bin\tkprof.exe

2. tkprof 全称

TKPROF stands for transient kernel profiler.

3. 基本的使用步骤

1)        SQL> alter system set timed_statistics=true;

2)        用户级自跟踪：

SQL>ALTER SESSION SET SQL_TRACE=TRUE;

SQL>ALTER SESSION SET SQL_TRACE=FALSE;

用户级DBA跟踪：(例如sys跟踪test，需要用sysdba登录)

a). SQL>select s.USERNAME,s.SID,s.SERIAL#,s.COMMAND from v$session s where s.USERNAME='COLM' ;

b). SQL>exec sys.dbms_system.set_sql_trace_in_session(9,7,true);

c). SQL>exec sys.dbms_system.set_sql_trace_in_session(9,7,false);

       ps：9为SID，7为SERIAL#

3)        C:> cd C:\oracle\admin\COLM\udump

4)        C:\oracle\admin\COLM\udump> tkprof colm_ora_2056.trc trace.txt print=100 record=sql.txt sys=no

5)        查看trace.txt文件

范例：

*******************************************************************************

SELECT *

FROM

col_case


call     count       cpu    elapsed       disk      query    current        rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      0      0.00       0.00          0          0          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total        1      0.00       0.00          0          0          0           0


Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 62

需要调整的语句符合以下几点：

(1).CPU占用过多

(2).Parse,Execute,Fetch花费太多时间

(3).DISK读取太多，query/current(SGA)中数据块读取太少

(4).访问许多块，只返回2行



========================
使用TKPROF转换跟踪文件为可读格式

Oracle可以使用ALTER SESSION来设置一个底层的跟踪，只要启用该跟踪，Oracle将所有的SQL和应用程序使用的顶层的PL/SQL调用记录到服务器上的跟踪文件（udump/*.trc）。该跟踪文件不仅有SQL和PL/SQL调用，也将包括定时的信息、等待事件的信息，执行的逻辑I/O和物理I/O的数量、CPU和挂钟时间、已经处理的行数、带有行记数的查询计划等，但是.trc文件很难阅读，我们可以使用TKPROF转换其为易读的格式。

     首先、为了起用跟踪并使得Oracle生成有效的.trc文件我们需要做的第一件事就是在session级别上开启SQL_TRACE：            
sql 代码
alter session set timed_statistics=true;   
alter session set events '10046 trace name context forever,level 12';
    上面的SQL语句1主要是设置定时系统，而第2句主要是开启跟踪：需要注意的一点是level <N>，说明如下：
           1   启用标准的SQL_TRACE工具，等价于SQL_TRACE=true
         4   启用SQL_TRACE并捕捉跟踪文件中的绑定变量
           8   启用SQL_TRACE并捕捉跟踪文件的等待事件
           12  启用标准的SQL_TRACE并捕捉绑定变量和等待事件

    如果您需要确认某个数据库中有多少.trc文件的话，这里有一个查询可以帮助您（Oracle9i）上调试通过：
               
sql 代码
select rtrim(c.value,'/') || '/' || d.instance_name || '_ora_' || ltrim(to_char(a.spid)) || '.trc' from v$process a,v$session b,v$parameter c,v$instance d
where a.addr = b.paddr and b.audsid= sys_context('userenv','sessionid')
and c.name ='user_dump_dest';

     测试结果大致为：
             absolute_path\dbname\udump/dbname_ora_384.trc
           absolute_path\dbname\udump/dbname_ora_2000.trc
    下面我们就使用TKPROF来转化dbname_ora_2000.trc为可读的格式（因为本人在win系统上做业，因此转换其为.txt格式）：
        >tkprof dbname_ora_2000.trc traceview.txt
    执行完此转化后会有一个traceview.txt文件在当前目录下生成（其内容我们结合具体的跟踪语句说明）：

     这里我们随便执行一条查询语句：
      
sql 代码
select count(*) from test_trace_an;
exit;--退出sql plus
     
      假设上面的dbname_ora_2000.trc就是目前的跟踪结果：则使用TKPROF转化后的结果文件中，我们可以发现：

select count(*) from test_trace_an

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.00       0.00          0          3          0           1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows     Row Source Operation
------- ---------------------------------------------------
      1 SORT AGGREGATE
      1   TABLE ACCESS FULL OBJ#(30327)

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait Total Waited
----------------------------------------   Waited ---------- ------------
SQL*Net message to client                       2        0.00          0.00
SQL*Net message from client                     2        0.00          0.00