概述

分享一个工作中比较多用来分析跟踪文件的一个工具，还是挺好用的。TKPROF 是一个用于分析 Oracle 跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具。如果一个系统的执行效率比较低，一个比较好的方法是跟踪用户的会话并且使用 TKPROF 工具的排序功能格式化输出，从而找出有问题的 SQL 语句。

语法格式

格式: 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，即按照 elapsed time fetching 来对分析的结果排序（记住要设置初始化参数 TIME_STATISTICS=true），生成的.prf 文件将把最消耗时间的SQL 放在最前面显示。另外一个有用的参数就是 SYS，这个参数设置为 no 可以阻止所有以 SYS 用户执行的SQL 被显示出来。

TKPROF 工具的使用步骤

用这个工具之前要先产生trace文件，根据自己平时优化的过程整理如下，也做个备忘（因为自己平时不会去记命令啥的，只会去记大概的一个思路）

1、在数据库级别上设置 TIMED_STATISTICS 为 true：

alter system set timed_statistics=false scope=both; （这样就可以不用重启生效了）

2、得到想要查看 session 的 trace

如果需要在 session 级别上设置 trace，可以在 SQL*Plus 中使用下列语句：

SQL> alter session set sql_trace=true;

3、对 trace 文件使用 TKPROF 工具进行分析

tkprof tracefile outfile [explain=user/password] [options...]

一般来说，使用 TKPROF 得到的输出文件中包含 3 个部分。

1）SQL 语句本身。 
2）相关的诊断信息，包括 CPU 时间、总共消耗的时间、读取磁盘数量、逻辑读的数量、以及查询中返回的记录数目等。 
3）列出这个语句的执行计划。

Tkprof命令输出的解释

call count cpu elapsed disk query current rows 
------- ------ -------- ---------- ---------- ---------- ---------- 
Parse 12 3.02 6.04 0 101 0 0 
Execute 12 0.00 0.03 0 0 0 0 
Fetch 12 6.41 5.47 88 143290 0 10 
------- ------ -------- ---------- ---------- ---------- ---------- 
total 36 9.43 11.55 88 143391 0 10 
Misses in library cache during parse: 11 
Optimizer goal: CHOOSE 
Parsing user id: 43 (TEST) 
Rows Row Source Operation 
------- --------------------------------------------------- 
 0 SORT ORDER BY (cr=445 r=1 w=0 time=18059 us) 
 0 COUNT STOPKEY (cr=445 r=1 w=0 time=17987 us) 
 0 TABLE ACCESS FULL ERROREVENT (cr=445 r=1 w=0 time=17983 us) 
 0 SELECT STATEMENT GOAL: CHOOSE 
 0 SORT (ORDER BY) 
 0 COUNT (STOPKEY) 
 0 TABLE ACCESS (FULL) OF 'ERROREVENT' 

这里解释下输出文件中列的含义：

CALL：每次SQL语句的处理都分成三个部分

Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。

Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。

Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。

COUNT:这个语句被parse、execute、fetch的次数。

CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。

ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。

DISK：从磁盘上的数据文件中物理读取的块的数量。

QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。

CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。

ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。

总结：

简单说下自己平时数据库卡时找问题的过程：一般先从 OS 上利用 top 命令找到当前占用 CPU 资源最高的一个进程的 PID 号，然后在数据库中根据 PID 号找到相应的 SID 号和 SERIAL#，接下来用 dbms_system.set_sql_trace_in_session 包来对这个 session 进行 trace（这里也可以按上面方法产生），最后TKPROF 工具分析下刚刚产生的trace文件基本就可以知道问题出在哪了。

后面会分享更多关于DBA和devops方面的内容，感兴趣的朋友可以关注下！！