Oracel SQL_TRACE摘抄

SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段，是强有力的辅助诊断工具.在日常的数据库问题诊断和解决中，SQL_TRACE是非常常用的方法。
1.在数据库级别开启trace

 

SQL> ALTER SYSTEM SET SQL_TRACE = TRUE;

SQL> ALTER SYSTEM SET SQL_TRACE = FALSE;

 

2.在当前SESSION设置trace

 

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

3.对其他Session进行trace

 

EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,FALSE);

EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,FALSE);

 

4.使用10046事件进行SQL的追踪

 

10046事件主要用来跟踪SQL语句，它并不是官方提供的命令，在官方文档上也找不到事件的说明信息，但是用的却比较多。10046事件获取SQL的信息比SQL_TRACE更多，更利于我们对SQL的判断。

10046事件按照收集信息内容，可以分为4个级别：

Level 1：等同与SQL_TRACE的功能

Level 4 :  在Level1基础上增加搜集绑定变量的信息

Level 8 :  在Level1基础上增加等待事件的信息

Level 12： 等同与Level4+Level 8，即同时收集绑定变量信息和等待时间信息

 

4.1 对当前session 启动10046事件

 

alter session set events '10046 trace name context forever, level 12';

 

4.2 对当前session 关闭 10046 事件

 

alter session set events '10046 trace name context off';

 

4.3 当其他session 启动 10046事件

 

exec dbms_monitor.session_trace_enable(147,128,waits=>true,binds=>true);

 

4.4 对其他session 关闭 10046事件

 

 exec dbms_monitor.session_trace_disable(147,128);

 

如何获得当前session 的 SID,SERIAL#

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT DISTINCT SID FROM V$MYSTAT);

       SID    SERIAL#
---------- ----------
       144         11

 

如何获得生成的TRACE文件
select d.value || '\' || lower(rtrim(i.instance_name, 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,
       v$instance i,
       (select value from v$parameter where name = 'user_dump_dest') d

 

--另外一种查看方式：

 

 

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

将生成的TRACE 文件转换成易读格式
语法：
tkprof tracefile outputfile [optional | parameters ]

如：

tkprof  E:\admin\ora10\udump\ora10_ora_4832.trc  output= d:\10046.txt  sys=no sort=prsela,exeela,fchela

参数和选项如下：
explain=scott/scott 表示用scott连接并进行执行计划分析。
sys=[yes/no]确定系统是否列出由sys用户产生或重调的sql语句
sort=sort_option按照指定的方法对sql trace的输出文件进行降序排序
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按返回数据时处理的数据数量排序

tkprof输出文件各列的含义：（理解下面的含义对我们快速定位问题很有帮助）

parse:
将sql语句转换成执行计划，包括检查是否有正确的授权，需要到得表，列及其他引用到得对象是否存在，这些信息分别存在v$librarycache.v$rowcache..

execute
oracle实际执行的语句，如：insert,update,delete,这些会修改数据，对于select操作，这部只是确定选择的行数。

fetch
返回查询获得的行数，只有执行select会被收集。

Count
这个语句被parse，execute，fetch的次数的统计

Cpu
这个语句所有的parse，execute，fetch所用的cpu总的时间，以秒为单位。如果TIMED_STATISTICS 关闭的话，值为0。

Elapsed
这个语句所有的parse，execute，fetch所消耗的总的时间，以秒为单位。如果TIMED_STATISTICS 关闭的话，值为0。

Disk
这个语句所有的parse，execute，fetch从磁盘上的数据文件中读取的数据块的数量

Query
在一致性读的模式下，这个语句所有的parse，execute，fetch所获取的buffer数量（这部分是从内存读取的也就是逻辑读取的，相当于执行计划里的consistent gets）

Current
在current模式下，这个语句所有的parse，execute，fetch所获取的buffer数量，一般是current模式下发生的delect,insert,update的操作都会获取buffer。

Rows
语句返回的行数，不包括子查询中返回的记录数目。对于select语句，返回在fetch这步，对于insert，delete，update操作，返回记录是在execute这步。

 

总之，当SQL语句操作出现性能问题时，我们可以用SQL_TRACE 或者10046事件进行跟踪是最合适的。 如果是数据库整体性能下降，就需要使用statspack或者AWR对数据库进行分析。

 

本文参考： http://blog.csdn.net/tianlesoftware/article/details/5857023