oracle SQL_TRACE和10046事件

最新推荐文章于 2018-06-20 07:22:00 发布

xile99

最新推荐文章于 2018-06-20 07:22:00 发布

阅读量661

点赞数

分类专栏： Oracle基础文章标签： SQL_TRACE 10046事件 oracle

本文链接：https://blog.csdn.net/xile99/article/details/14521887

版权

Oracle基础专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一、SQL_TRACE/10046事件是什么

SQL_TRACE/10046事件是Oracle提供的用来进行SQL跟踪的强有力工具，可跟踪到SQL的解析过程、执行计划、绑定变量、递归调用等详细信息，从而能更好地分析和解决问题。

首先是在数据库层面执行SQL的TRACE命令，执行后生成TRACE文件，由于TRACE文件格式比较难以阅读，所以通过TKPROF工具进行格式化，最终生成方便阅读的SQL跟踪分析文件。

二、SQL_TRACE/10046事件具体使用方法

1. 开启关闭

1）当前session

启用SQL_TRACE功能的方法比较简单，如下：

开启会话跟踪 alter session set sql_trace=true;

关闭会话跟踪 alter session set sql_trace=false;

相比SQL_TRACE的使用方法，10046事件跟踪略显得有些麻烦，命令如下：

开启会话跟踪

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

Session altered.

关闭会话跟踪

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

Session altered.

其中LEVEL代表的是10046事件设置的级别，可分为4类，具体如下：

1 - 启用标准的SQL_TRACE功能，等价于SQL_TRACE

4 - Level 1加上绑定值（bind values）

8 - Level 1 + 等待事件跟踪

12 - Level 1 + Level4 + Level 8

通过这个设置的级别可看出，10046事件跟踪其实就是SQL_TRACE的增强版，所以对SQL进行跟踪，完全可用10046事件来代替SQL_TRACE命令。

此外要特别注意，LEVEL级别越大，产生的TRACE内容越多，这样对该SESSION的执行效率将会有很大的影响，同时还会较大程度地影响TKPROF格式化的用时。

2）全局session（尽量避免使用）

除了在session级别设置外，还可以进行全局的SQL_TRACE/10046事件跟踪，具体方法如下，但是这样将会对整个系统的性能产生严重的影响，所以一般不建议开启。

SQL> alter system set sql_trace=true;

System altered.

SQL> alter system set events '10046 trace name context forever,level 8';

System altered.

SQL> alter system set events '10046 trace name context off';

System altered.

3）指定用户SESSION

跟踪指定用户的SESSION可采用Oracle强大的DBMS_SYSTEM包，包参数如下（其中SI、SE、NM分别对应v$SESSION中的SID、SERIAL#和USERNAME。LE表示TRACE的级别，具体值再前面已有介绍，当LE=0时表示跟踪结束，EV表示跟踪的事件类型，比如10046则表示跟踪10046事件）。

通过desc dbms_system查看该包的SET_EV过程参数如下（sys用户登录）：

Argument Name                  Type                    In/Out Default
-------------------------- ----------------------- ------ --------
SI                               BINARY_INTEGER          IN
SE                             BINARY_INTEGER          IN
EV                             BINARY_INTEGER          IN
LE                             BINARY_INTEGER          IN
NM                             VARCHAR2                      IN

下面举例如何使用该包跟踪指定SESSION。

首先找到指定用户SESSION的SID、SERIAL#和USERNAME，即包中的SI、SE、NM参数。

SQL> select sid,serial#,username from v$session where username='CLD';

SID SERIAL# USERNAME
---------- ---------- ------------------------------
17 36770 CLD

接着使用如下方式开启和关闭指定用户跟踪（一般SYS用户才拥有这样的权限）。

SQL> exec dbms_system.set_ev(17,36770,10046,8,'CLD');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_ev(17,36770,10046,0,'CLD');

PL/SQL procedure successfully completed.

2. 获取跟踪文件

上述步骤生成的跟踪文件一般位于user_dump_dest目录下，具体的文件名及位置可以通过以下两种方法定位：

方法1（通过脚本来定位，注意UNIX平台需将' \ ' 更换为' / '）

SQL> select c.value||'\'||d.instance_name||'_ora_'||a.spid||'.trc' trace_name_file
2 from v$process a,v$session b,v$parameter c,v$instance d
3 where a.addr=b.paddr
4 and b.audsid=userenv('sessionid')
5 and c.name='user_dump_dest';

TRACE_NAME_FILE
--------------------------------------------------------------------------------
c:\app\db2admin\diag\rdbms\o02scl0\o02scl0\trace\o02scl0_ora_10680.trc

方法2（通过对跟踪文件加标识，以方便跟踪）

SQL> alter session set tracefile_identifier='10046';

Session altered.

SQL> host dir c:\app\db2admin\diag\rdbms\o02scl0\o02scl0\trace\*10046*
Directory of c:\app\db2admin\diag\rdbms\o02scl0\o02scl0\trace

11/08/2013 03:53 PM 1,108 o02scl0_ora_11544_10046.trc

3. TKPROF格式化

TKPROF是Oracle自带的TRACE文件的格式化工具，通过TKRPOF的格式化，可以将TRACE文件转化为方便阅读的格式，这是一个相当实用的小工具，具体使用命令很简单，如下所示：

tkprof tracefile outputfile [options]

其中[options]选项众多，具体可通过直接在操作系统命令行输入tkprof得到。

C:\>tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
print=integer    List only the first 'integer' SQL statements.
aggregate=yes|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.
waits=yes|no     Record summary for any wait events found in the trace file
sort=option      Set of zero or more of the following sort options:
    prscnt number of times parse was called
    prscpu cpu time parsing
    prsela elapsed time parsing
    prsdsk number of disk reads during parse
    prsqry number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis number of misses in library cache during parse
    execnt number of execute was called
    execpu cpu time spent executing
    exeela elapsed time executing
    exedsk number of disk reads during execute
    exeqry number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    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
    fchela elapsed time fetching
    fchdsk number of disk reads during fetch
    fchqry number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow number of rows fetched
    userid userid of user that parsed the cursor

其中的SORT部分默认为SQL执行的先后顺序，如此排序不利于抓住问题的主要矛盾，因此建议此处不采用默认值，而采用指定方式，例如：

tkprof tracefile outputfile sys=no sort=prsela,exeela,fchela

其中prsela表示对一个游标解析耗费的时间，exeela表示针对游标执行所花费的时间，fchela表示针对游标获取数据行所花费的时间，sys表示指定SYS用户运行的SQL语句，会输出解析操作阶段对数据字典递归查询的语句，只有在药分析解析细节时才考虑将其开启，在多数情况下建议将其关闭，例如：

C:\Documents and Settings\db2admin>tkprof c:\app\db2admin\diag\rdbms\o02scl0\o02scl0\trace\o02scl0_ora_10680.trc c:\10046.txt

TKPROF: Release 11.2.0.1.0 - Development on Fri Nov 8 14:23:53 2013

C:\>dir 10046.txt
Directory of C:\

11/08/2013 02:23 PM         1,122,411 10046.txt
               1 File(s)      1,122,411 bytes
               0 Dir(s)   4,680,732,672 bytes free

整理自《剑破冰山---Oracle开发艺术》

xile99

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle SQL_TRACE和10046事件

一、SQL_TRACE/10046事件是什么 SQL_TRACE/10046事件是Oracle提供的用来进行SQL跟踪的强有力工具，可跟踪到SQL的解析过程、执行计划、绑定变量、递归调用等详细信息，从而能更好地分析和解决问题。首先是在数据库层面执行SQL的TRACE命令，执行后生成TRACE文件，由于TRACE文件格式比较难以阅读，所以通过TKPROF工具进行
复制链接

扫一扫