SQL TRACE和TKPROF的使用步骤-优快云博客

本文介绍如何在Oracle数据库中使用SQL Trace收集SQL执行信息，并利用tkprof工具对trace文件进行格式化处理，以便更好地分析SQL性能问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.设置参数文件

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

timed_staticstices

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

SQL>alter session set timed_statistics = true;

SQL>alter system set timed_statistics = true;

max_dump_file_size

当实例层启用SQLTRACE的时候，在每次请求服务器的时候，都将在跟踪文件中产生一个文本行，这些文件的最大尺寸受限于初始化参数的设置。默认为500(blocks)。若里面的数据被截断则增大SIZE。若为UNLIMITED则意味着没有上限。

user_dump_dest

设置跟踪文件的存储位置。默认为admin/用户/udump;

SQL>alter system set user_dump_dest = 'newdir';

2.启动SQL TRACE实用工具

对会话启动关闭SQL TRACE

SQL>alter session set sql_trace = true;

SQL>alter session set sql_trace = false;

SQL>exec SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION =（SID，SERIAL#，TRUE);

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

对用户实例启动关闭SQL TRACE

SQL>alter system set sql_trace = true;

SQL>alter system set sql_trace = false;

3.使用tkprof格式化trace文件

Usage

tkprofinputfileoutputfile[optional|parameters]

（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参数和选项

explain=user/password执行explain命令将结果放在SQLtrace的输出文件中
table=schema.table指定tkprof处理sqltrace文件时临时表的模式名和表名
insert=scriptfile创建一个文件名为scriptfile的文件，包含了tkprof存放的输出sql语句
sys=[yes/no]确定系统是否列出由sys用户产生或重调的sql语句
print=number将仅生成排序后的第一条sql语句的输出结果
record=recordfile这个选项创建一个名为recorderfile的文件，包含了所有重调用的sql语句
sort=sort_option按照指定的方法对sqltrace的输出文件进行降序排序
sort_option选项
prscnt按解析次数排序
prscpu按解析所花cpu时间排序
prsela按解析所经历的时间排序
prsdsk按解析时物理的读操作的次数排序
prsqry按解析时以一致模式读取数据块的次数排序
prscu按解析时以当前读取数据块的次数进行排序
execnt按执行次数排序
execpu按执行时花的cpu时间排序
exeela按执行所经历的时间排序
exedsk按执行时物理读操作的次数排序
exeqry按执行时以一致模式读取数据块的次数排序
execu按执行时以当前模式读取数据块的次数排序
exerow按执行时处理的记录的次数进行排序
exemis按执行时库缓冲区的错误排序
fchcnt按返回数据的次数进行排序
fchcpu按返回数据cpu所花时间排序
fchela按返回数据所经历的时间排序
fchdsk按返回数据时的物理读操作的次数排序
fchqry按返回数据时一致模式读取数据块的次数排序
fchcu按返回数据时当前模式读取数据块的次数排序
fchrow按返回数据时处理的数据数量排序

4.举例:

trace其他session

查询某用户的session的SID及SERIAL#

$sqlplus / as sysdba

SQL>select s.USERNAME,s.SID,s.SERIAL#,s.COMMAND from v$session s

where s.USERNAME='ETOH' ; --注意用户名用大写

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文件号

SQL>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生成分析文件

$tkprof /u01/app/oracle/admin/center/udump/<SID>_ora_24012.trc/u01/app/oracle/admin/center/udump/center_ora_24012.txtaggregate=yes sys=no sort=fchela(此例中将执行最耗时的sql放在分析文件的开头)

trace本session

查询本session信息

SQL>SELECT Sid, Serial# FROM V$session WHERE sid = Sys_Context ( 'USERENV' , 'SID' );

SQL>alter session set sql_trace=true;

SQL>#SQL Statements#

SQL>alter session set sql_trace=false;

查询生成的trace文件名

SQL>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格式化处理

$tkprofcenter_ora_24012.trccenter_ora_24012.txt {一系列参数}