SQL TRACE和TKPROF的使用步骤

1.设置参数文件

　　设置三个参数timed_statistics、user_dump_dest、max_dump_file_size。

timed_staticstices

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

alter session set timed_statistics = true;

alter system set timed_statistics = true;

max_dump_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);

SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION =（SID，SERIAL#，FALSE);

        对用户实例启动 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= ]

例:1.tkprof tracefile outfile [explain=user/password] [options...];

例:2.tkprof uat_ora_14936.trc trace.txt sort=(prsdsk,exedsk,fchdsk) print=10 explain=apps/apps table=apps.temp_plan_table_a insert=storea.sql sys=no

tkprof参数介绍

table=schema.tablename Use 'schema.tablename' with 'explain=' option.

用于指定在将执行计划写进输出文件之前，TKPROF用于临时存放执行计划所用表的属主和名称，需要和explain参数一起使用。

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.

记录trace文件中所有等待事件的摘要。

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 所获取的行数。

4.举例:

trace其他session

SQL>exec dbms_system.SET_SQL_TRACE_IN_SESSION(12,73,true);

等待被跟踪session活动一段时间

SQL>exec dbms_system.SET_SQL_TRACE_IN_SESSION(12,73,false);

--查询生成的.trc文件号

SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||

       p.spid || '.trc' trace_file_name

  from (select p.spid

          from v$session s, v$process p

         where s.sid = &sid

           and s. SERIAL# = &serial#

and p.addr = s.paddr) p,

       (select t.instance

          from v$thread t, v$parameter v

         where v.name = 'thread'

           and (v.value = 0 or t.thread# = to_number(v.value))) i,

       (select value from v$parameter where name = 'user_dump_dest') d;

--使用tkprof生成分析文件

OS>tkprof e:\oracle\admin\hunter\hunter_ora_4188.trc c:\test.prf aggregate=yes sys=no sort=fchela(此例中将执行最耗时的sql放在分析文件的开头)

 

trace本session

SQL>alter session set sql_trace=true;

SQL>#SQL Statements#

SQL>alter session set sql_trace=false;

查询生成的trace文件名

SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||

       p.spid || '.trc' trace_file_name

  from (select p.spid

          from v$mystat m, v$session s, v$process p

         where m.statistic# = 1

           and s.sid = m.sid

           and p.addr = s.paddr) p,

       (select t.instance

          from v$thread t, v$parameter v

         where v.name = 'thread'

           and (v.value = 0 or t.thread# = to_number(v.value))) i,

       (select value from v$parameter where name = 'user_dump_dest') d;

tkprof格式化处理

OS>tkprof standdb_ora_770326.trc standdb_ora_770326.txt

 

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

转载于:http://blog.itpub.net/21129591/viewspace-701530/