TKPROF 使用

话说oracle的剖析工具有很多，这里就介绍下常用的tkprof剖析工具。

敲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

参数是挺多的，这里拣几个常用的理解一下吧

explain这里需要输入用户名和密码 ，explain=user/password，得到执行计划和行源信息

table 指定保存执行计划的表

aggregate 是否单独处理同样内容的SQL

waits等待事件是否加入到输出文件

sort指定输出文件的SQL顺序

sys=no 就是不看系统的SQL

参数就介绍这些，下面我们介绍下怎么阅读输出的文本信息，这个才是关键，一般都是udump下的trace文件

tkprof skyread_ora_10409.trc /home/oracle/0818.txt sort=prsela,exeela,fchela print=3 explain=yypt/yypt aggregate=no sys=no

这时上面输出的文件，我来解释下

图示可以看出trace file的名称，以及sort的方式，这时是unknown session id，一般这里回显示一个session的id，这个头部信息会出现多次，而且在不同的session之间分隔。

count：表示执行的数据库调用数量

CPU： 提供执行CPU所花的时间单位是秒
Elapsed：提供了执行时所花的时间。单位是秒。这个参数值等于用户响应时间
Disk：提供缓存区从磁盘读取的数据块量
Query：以一致性模式从缓存区获得数据块的数量

Current：以当前模式从缓存区获得数据的块数量
ROWs： 返回调用或执行调用时，处理的数据行的数量。

这里我们做个试验：

create table t1 (a int);
insert into t1values (1000); commit;
alter session set events '10046 trace name context forever,level 12';
select * from t1;
update t1t1_1set x=x+1 ;

update t1 t1_2 set x=x+1 ;
这样我们就对后面三个sql进行了跟踪。

tkprof oradb_ora_25204.trc /home/oracle/zhh.txt explain=charge/charge sys=no

可以看到上面三条SQL的信息，还有一定要记得只有加上explain参数，才可以看到执行计划哦。