SQL_TRACE和10046事件

1. SQL_TRACE命令将sql的整个过程输出到一个trace文件，通过读取这个trace文件来了解这个sql执行过程中oracle究竟做了什么事情。

  1. 设置SQL_TRACE生成的trace文件标识：alter session set tracefile_identifier='mytest';
  2. 在当前会话中启用SQL_TRACE：alter session set sql_trace=true; 也可以在当前实例中启用SQL_TRACE：alter system set sql_trace=true;这样就是对实例上所有sql进行跟踪，这种方式代价很大，同时跟踪的sql很多。
  3. 关闭SQL_TRACE：alter session set sql_trace=false;

    SQL_TRACE生成的trace文件默认路径是：$Oracle_base/admin/SID/udump。$Oracle_base是oracle安装时设定的环境变量，SID是当前Oracle的实例SID。在windows上路径在D:\Oracle11g\diag\rdbms\[SID]\[SID]\trace

2. tkprof来处理原始的可读性比较差的trace文件，它是Oracle自带的工具。

    命令行：D:>tkprof mytest.trc output.txt     

               D:>tkprof mytest.trc output.txt explain=[username]/[password] sys=(yes/no)  aggregate=(yes/no)  输出执行计划，使用sys=no来剔除所有以sys用户执行的sql(数据字典视图的sql)，aggregate=yes来将相同的sql在输入文件中合并。

    Usage: tkprof tracefile outputfile [explain=] [table=] [print=] [insert=] [sys=] [sort=]

    trace结果中，parse:将sql转化为执行计划，execute：执行，对于insert，update，delete操作，这步会修改数据，对于select操作，这步只是确定选择的记录，fetch：返回查询语句中所获得的记录，这步只有select会被执行。count：计数器，表示当前操作被执行多少次。cpu：当前操作消耗的cpu时间。elapsed：当前操作一共用时，包含cpu时间和等待时间。disk：当前操作的物理读的块的数量，磁盘i

/o次数。query：当前操作的一致性读取的数据块数，通常是查询的方式。current：当前操作的current的方式读取的数据块数，通常是修改数据使用的方式。rows：当前操作处理的数据记录数。

3. trace原始文件也可以进行读取，它让你知道sql在哪个点在做什么，以及sql是如何工作的。

4. 10046事件并不是oracle官方提供给用户使用的命令，但是目前使用广泛，它比SQL_TRACE能够获取更多的信息。

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

  • level 1：等同于SQL_TRACE的功能
  • level 4：在level1的基础上增加绑定变量的信息，绑定变量的信息只能在原始trace文件中获取，在tkprof之后文件里看不到。
  • level 8：在level1的基础上增加等待事件的信息，可以在tkprof文件里看到
  • level 12：等同于level4+level8

    打开10046事件并设定级别为4：alter session set events '10046 trace name context forever, level 4';

    关闭10046事件：alter session set events '10046 trace name context off';

5. 用下面的方法对其他会话进行跟踪：

SQL_TRACE：

    1. 获得需要trace会话的SID和serial#的值：select sid, serial# from v$session where sid=[SID];
    2. 启动SQL_TRACE: execute dbms_system.set_sql_trace_in_session([SID],[seial#],true); 需要sysdba权限
    3. 停止SQL_TRACE: execute dbms_system.set_sql_trace_in_session([SID],[seial#],false); 需要sysdba权限

10046事件：

    1. 获得需要trace会话的SID和serial#的值：select sid, serial# from v$session where sid=[SID];
    2. 启动SQL_TRACE: execute dbms_monitor.session_trace_enable([SID],[seial#],waits=>true,binds=>true);
    3. 停止SQL_TRACE: execute dbms_monitor.session.trace_disable([SID],[seial#]);

6. SQL_TRACE和STATSPACK是两个适用于不同级别的性能分析工具：

  • 当我们遇到一个数据库系统整体性能下降时，而没有特定对象可以分析时，做一个STATSPACK或者AWR报告来发现瓶颈在哪，如果瓶颈是出现在sql语句上，就可以获得相应的sql语句，然后用SQL_TRACE进行分析。
  • 当一个确定的sql语句出现性能问题，SQL_TRACE(10046)是合适的。

 

posted on 2014-05-18 10:10  当时已枉然  阅读(1069)  评论(0)  编辑  收藏  举报