tkprof的基本使用详细介绍

Tkprof：Trace Kerner PROFile

是oracle用来分析跟踪文件(*.trc)的一个工具，提供人性化的分析结果。辅助我们分析跟踪文件找出影响效率的SQL，并优化它。

操作环境WinXP+oracle10.2.0.1.0下SQLPLUS

1、  配置跟踪环境

1.1   设置timed_statistics为true，它能够计算CPU时间，定时统计信息

方法一、直接改写init.ora中，对整个数据库有效：

timed_statistics=true

              方法二、用alter system命令，对当前实例有效：

alter system set timed_statistics=true;

方法三、用alter session命令，对当前会话有效：

alter session set timed_statistics=true;

              用show parameter timed_statistics查看当前timed_statistics的值

1.2   在init.ora中设置user_dump_dest,既是跟踪文件的存放位置

user_dump_dest= D:/oracle/product/10.2.0/admin/zianed/udump

用show parameter user_dump_dest查看当前timed_statistics的值

也可通过一下程序获取：

declare

        l_intval number;

        l_strval varchar2(2000);

        l_type number;

begin

                                   l_type:=dbms_utility.get_parameter_value(‘user_dump_dest’,l_intval,l_strval);

        dbms_output.put_line(l_strval);

end;

1.3   查看max_dump_file_size的值，既是跟踪文件所能达到的大小, UNLIMITED表示大小没有限制。

show parameter max_dump_file_size查看

2、  生成跟踪

2.1 系统级跟踪

更改init.ora中为SQL_TRACE = TRUE

(系统消耗较大，在正式库中请不要使用)

2.2 会话级跟踪

2.2.1SQLPLUS中跟踪本会话的执行

              开始：alter session set sql_trace=true；

              结束：alter session set sql_trace=false；

2.2.2PL/SQL中跟踪本会话的执行

              开始：exec dbms_session.set_sql_trace(true);

              结束：exec dbms_session.set_sql_trace(false);    

2.2.3跟踪其他会话的执行

步骤一、找到其他会话的sid和serial#（假设是当前会话，其他会话则必须知道SID即可）

                            select sid, serial#

from v$session

where sid=(select distinct sid from v$mystat);

 

              步骤二、执行跟踪（需要一些特殊权限）：

              开始：exec dbms_system.set_sql_trace_in_session(sid,serial#,true);

       结束：exec dbms_system.set_sql_trace_in_session(sid,serial#,false);

2.3 用event来打开跟踪

       开始：alter session set events '10046 trace name context forever,level N';

       N参数的说明：

       N=1 同alter session set sql_trace = true

N=4 可以捕捉绑定变量

N=8 可以捕捉查询时的等待事件

N=12 可以捕捉绑定变量与等待事件

       结束：alter session set events '10046 trace name context off';

2.4 确定跟踪文件:

              select a.SPID

from v$process a,v$session b

where a.addr=b.paddr

and b.audsid=USERENV('sessionid');

       在user_dump_dest跟踪文件命名为:${SID}_ORA_${SPID}.trc

3、分析跟踪文件(*.trc)

3.1使用tkprof来查看帮助即可现实其所有的参数

       cmd>tkprof

3.2 一般执行如下：(也可加上sort选项)

       tkprof D:/oracle/product/10.2.0/admin/zianed/udump/zianed_ora_556.trc D:/report.txt

 

它包括一下参数：

tkprof的参数有下面几个　　
　　/*********************************/

explain=username/passWord　

　    connect to oracle and issue explain plain

/*********************************/

/*********************************/

talbe=schema.tablename

　　use'schema.table' with explain option

/*********************************/

/*********************************/
　　aggregate=yes/no

       aggregate=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
　　/*********************************/

/*********************************/

print=integer

list only the first 'integer' sql statements
/*********************************/　　

3.3分析report.txt

其中包括：SQL语句本身、相关的诊断信息、SQL语句的执行计划

在其头文件中描述了各个参数的解释。

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

count    = number of times OCI procedure was executed

cpu      = cpu time in seconds executing

elapsed   = elapsed time in seconds executing

disk     = number of physical reads of buffers from disk

query    = number of buffers gotten for consistent read

current   = number of buffers gotten in current mode (usually for update)

rows     = number of rows processed by the fetch or execute call

 

CALL：每次SQL语句的处理都分成三个部分
      Parse：执行硬分析。这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。
      Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。
      Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。

例如：

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

 

select sys_context('userenv', 'current_schema')

from

 dual

 

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        4      0.00       0.00          0          0          0           0

Execute      4      0.00       0.00          0          0          0           0

Fetch        4      0.00       0.00          0          0          0           4

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

total       12      0.00       0.00          0          0          0           4

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 54 

 

Rows     Row Source Operation

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

      1  FAST DUAL  (cr=0 pr=0 pw=0 time=5 us)

 

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

问题判断：
1.query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
2.parse count/Execute count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse。
要检查Pro*C程序的MAXOPENCURSORS是不是太低了，或不适当的使用的RELEASE_CURSOR选项
3. rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，
增加了数据在客户端和服务器之间的往返次数。在Pro*C中可以用prefetch=NN,Java/JDBC中可调用SETROWPREFETCH,
在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)　　
4. disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)　　
5. elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源　　
6. cpu　OR　elapsed 太大表示执行时间过长，或消耗了大量的CPU时间，应该考虑优化
7.　执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少

参考文章：

《Expert one on one Oracle》

《Tkprof工具介绍和分析》

《Tkprof--博客之家》

《tkprof和sql trace》