TKPROF工具使用学习

Tkprof工具可用来格式化sql trace产生的文件，让你更容易看懂trace的内容

用法： tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] ...

参数说明： 

tracefile：你要分析的trace文件

outputfile：格式化后的文件

explain=user/password@connectstring

table=schema.tablename

    注1：这两个参数是一起使用的，通过连接数据库对在trace文件中出现的每条sql语句查看执行计划，并将之输出到outputfile中

    注2：该table必须是数据库中不存在的，如果存在会报错

print=n            ：只列出最初N个sql执行语句

insert=filename    ：会产生一个sql文件，运行此文件可将收集到的数据insert到数据库表中

sys=no             ：过滤掉由sys执行的语句

record=filename    ：可将非嵌套执行的sql语句过滤到指定的文件中去

waits=yes|no       ：是否统计任何等待事件

aggregate=yes|no   ：是否将相同sql语句的执行信息合计起来，默认为yes

sort= option       ：设置排序选项，选项如下

示例如下：

1.列出前2条sql语句的执行情况： 

[oracle@silent trace]$ tkprof orcl_ora_4601_10046.trc out.txt print=2

 

2.将数据保存到数据库： 

[oracle@silent trace]$ tkprof orcl_ora_4601_10046.trc out.txt insert=insert.sql

 

执行后会在当前目录产生insert.sql文件，执行该文件即可将数据保存到数据库，以下为insert.sql部分内容： 

[oracle@silent trace]$ cat insert.sql
REM  Edit and/or remove the following  CREATE TABLE
REM  statement as your needs dictate.
CREATE TABLE  tkprof_table
(
 date_of_insert                       DATE   
,cursor_num                           NUMBER
,depth                                NUMBER
,user_id                              NUMBER
,parse_cnt                            NUMBER
,parse_cpu                            NUMBER
,parse_elap                           NUMBER
,parse_disk                           NUMBER
,parse_query                          NUMBER
,parse_current                        NUMBER
,parse_miss                           NUMBER
,exe_count                            NUMBER
,exe_cpu                              NUMBER
,exe_elap                             NUMBER
,exe_disk                             NUMBER
,exe_query                            NUMBER
,exe_current                          NUMBER
,exe_miss                             NUMBER
,exe_rows                             NUMBER
,fetch_count                          NUMBER
,fetch_cpu                            NUMBER
,fetch_elap                           NUMBER
,fetch_disk                           NUMBER
,fetch_query                          NUMBER
,fetch_current                        NUMBER
,fetch_rows                           NUMBER
,ticks                                NUMBER
,sql_statement                        LONG
);
set sqlterminator off
INSERT INTO tkprof_table VALUES
(
  SYSDATE, 2, 0, 86, 1, 5999, 5882, 0, 0, 0, 1
, 1, 0, 70, 140733193388032, 0, 206158430208, 140733193388032, 140733193388032
, 2, 0, 195, 0, 4, 0, 1, 1131
, 'select * from t where id=100
')
/
INSERT INTO tkprof_table VALUES
(
  SYSDATE, 2, 0, 86, 1, 0, 103, 0, 0, 0, 0
, 1, 0, 753, 140733193388032, 0, 0, 0, 0
, 0, 0, 0, 0, 0, 0, 0, 7832849
, 'alter session set events ''10046 trace name context off''
')
/
set sqlterminator on

 

3.提取sql执行语句：
[oracle@silent trace]$ tkprof orcl_ora_4601_10046.trc out.txt record=sqlstr.sql 

[oracle@silent trace]$ cat sqlstr.sql
select * from t where id=100 ;
alter session set events '10046 trace name context off' ;

 

4.产生执行计划：
[oracle@silent trace]$ tkprof orcl_ora_4601_10046.trc out.txt explain=whc/whc table=whc.notable

在产生的out.txt文件中会体现其执行计划： 

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'T' (TABLE)
      1    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'IDX_T' (INDEX)

 

小技巧：

 

1.如何查找你产生的trace文件：

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
------------------------------------------------------------
/u01/diag/rdbms/orcl/orcl/trace/orcl_ora_4601_10046.trc

 

2.sort选项可同时用多个，做法是用括号括起来，中间用逗号分割：
[oracle@silent trace]$ tkprof orcl_ora_4601_10046.trc out.txt sort=(prsela, exeela, fchela)
注意：最后排序是按照各个选项的数字之和进行排序，类似于order by (sort1+sort2+sort3)，而不是order by sort1,sort2,sort3

 

oracle性能优化：如何读懂tkprof

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这步。

 

A、query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
B、Parse count/Execute count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse
C、rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，增加了数据在客户端和服务器之间的往返次数。
D、disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
E、elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
F、cpu　Or　elapsed 太大表示执行时间过长，或消耗了了大量的CPU时间，应该考虑优化
G、执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少

 

EG：

alter session set max_dump_file_size=unlimited;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';
select 'Hello, world; today is '||sysdate from dual; exit;

 

tkprof orcl_ora_4601_10046.trc out.txt print=100 record=sqlstr.txt sys=no

 

然后查询out.txt就是分析内容了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28909249/viewspace-2076245/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28909249/viewspace-2076245/