通过案例学调优之--10046事件

通过案例学调优之--10046事件10046事件概述Oracle的10046事件，可以跟踪应用程序所执行的SQL语句，并且得到其解析次数.执行次数,CPU使用时间等信息。这对我们分

（先确保要event的会话环境符合条件）

1、必须确保timed_statistics为TRUE，这个参数可以在会话级上进行修改。

关于参数timed_statistics的一段解释
      Without timed statistics, Oracle records the reason for each wait before it begins to wait, and when the wait is over, it records whether it timed out. But with timed statistics enabled, Oracle checks the time just before and after each wait, and also records the time waited. The time waited is recorded in hundredths of a second— that is, centiseconds.
true
      The statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views.

false

     The value of all time-related statistics is set to zero. This setting lets Oracle avoid the overhead of requesting the time from the operating system.

      Starting with release 11.1.0.7.0, the value of the TIMED_STATISTICS parameter cannot be set to false if the value of STATISTICS_LEVEL is set to TYPICAL or ALL.

与SQL Trace相关的参数

在打开10046时间的SQL Trace之前，要先设置好下面几个参数。

timed_statistics

这个参数决定了是否收集与时间相关的统计信息，如果这个参数为FALSE的话，那么SQL Trace的结果基本没有多大的用处，默认情况下这个参数设置为TRUE。

max_dump_file_size

dump文件的大小，也就是决定是否限制SQL Trace文件的大小，在一个很忙的系统上面做SQL Trace的话可能会生成很多的信息，因此最好在会话级别将这个参数设置成unlimited。

tracefile_identifier

给Trace文件设置识别字符串，这是个非常有用的参数，设置一个易读的字串能更快的找到Trace文件。

要在当前会话修改上述参数很简单，只要使用下面的命令即可：

ALTER SESSION SET timed_statistics=true ALTER SESSION SET max_dump_file_size=unlimited ALTER SESSION SET tracefile_identifier='my_trace_session

当然，这些参数可以在系统级别修改的，也可以加载init文件中或是spfile中，让系统启动时自动做全局设置。

要是在系统运行时动态的修改别的会话的这些参数就需要借助DBMS_SYSTEM这个包了，设置方法如下

SYS.DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(   :sid, :serial,   'timed_statistics', true )   SYS.DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(   :sid, :serial,   'max_dump_file_size', 2147483647 )

案例：

13:55:30 SYS@ prod >select username,sid,serial# from v$session where username is not null; USERNAME                              SID    SERIAL# ------------------------------ ---------- ---------- SYS                                     1          5 SCOTT                                  40        587 SYS                                    48        569 14:31:33 SYS@ prod >desc dbms_system PROCEDURE ADD_PARAMETER_VALUE  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  PARNAME                        VARCHAR2                IN  VALUE                          VARCHAR2                IN  SCOPE                          VARCHAR2                IN     DEFAULT  SID                            VARCHAR2                IN     DEFAULT  POSITION                       BINARY_INTEGER          IN     DEFAULT PROCEDURE DIST_TXN_SYNC  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  INST_NUM                       NUMBER                  IN PROCEDURE GET_ENV  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  VAR                            VARCHAR2                IN  VAL                            VARCHAR2                OUT PROCEDURE KCFRMS PROCEDURE KSDDDT PROCEDURE KSDFLS PROCEDURE KSDIND  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  LVL                            BINARY_INTEGER          IN PROCEDURE KSDWRT  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  DEST                           BINARY_INTEGER          IN  TST                            VARCHAR2                IN PROCEDURE READ_EV  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  IEV                            BINARY_INTEGER          IN  OEV                            BINARY_INTEGER          OUT PROCEDURE REMOVE_PARAMETER_VALUE  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  PARNAME                        VARCHAR2                IN  VALUE                          VARCHAR2                IN  SCOPE                          VARCHAR2                IN     DEFAULT  SID                            VARCHAR2                IN     DEFAULT PROCEDURE REMOVE_PARAMETER_VALUE  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  PARNAME                        VARCHAR2                IN  POSITION                       BINARY_INTEGER          IN  SCOPE                          VARCHAR2                IN     DEFAULT  SID                            VARCHAR2                IN     DEFAULT PROCEDURE SET_BOOL_PARAM_IN_SESSION  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  SID                            NUMBER                  IN  SERIAL#                        NUMBER                  IN  PARNAM                         VARCHAR2                IN  BVAL                           BOOLEAN                 IN PROCEDURE SET_EV  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 PROCEDURE SET_INT_PARAM_IN_SESSION  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  SID                            NUMBER                  IN  SERIAL#                        NUMBER                  IN  PARNAM                         VARCHAR2                IN  INTVAL                         BINARY_INTEGER          IN PROCEDURE SET_SQL_TRACE_IN_SESSION  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  SID                            NUMBER                  IN  SERIAL#                        NUMBER                  IN  SQL_TRACE                      BOOLEAN                 IN PROCEDURE WAIT_FOR_EVENT  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  EVENT                          VARCHAR2                IN  EXTENDED_ID                    BINARY_INTEGER          IN  TIMEOUT                        BINARY_INTEGER          IN  14:28:02 SYS@ prod >exec dbms_system.set_bool_param_in_session(40,587,'time_statistics',true); PL/SQL procedure successfully completed. 14:33:59 SYS@ prod >exec dbms_system.set_int_param_in_session(40,587,'max_dump_file_size',2147364847); PL/SQL procedure successfully completed.

10046 Trace启动方法