11. Oralce之10046事件

10046事件：

　　该事件号是oracle为跟踪一个sql的执行过程而特定的一个事件号，通过该事件号可以跟踪sql执行过程中的各种信息。

10046等级级别：

level 1:跟踪sql语句，包括解析、执行、提取、提交和回滚等
level 4: 包括变量的详细信息
level 8:包括等待事件
lever 12: 包括绑定变量和等待事件

　其中 leve 1相当于打开了sql_trace，通常一般会使用level 12

10046事件开启

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

10046事件关闭

alert session set events '10046 trace name context forever off';

10046 事件相关的参数

1.timed_statistics

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

2.max_dump_file_size

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

3. tracefile_identifier

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

4. diagnostic_dest

　　diagnostic_dest这个参数11g新增的，用于控制存放trace文件与core文件的路径，默认是$ORACLE_BASE目录。

5. user_dump_dest

　　user_dump_dest参数指定用户进程trace文件目录。

6. background_dump_dest

　　background_dump_dest参数用于后台进程产生的trace文件存储目录下。

7.跟踪当前用户

跟踪当前用户自己的sql执行信息

SQL> conn username/password

alter session set tracefile_identifier = 'id_10046';

alter session set timed_statistics = true;

alter session set statistics_level=all;

alter session set max_dump_file_size = unlimited;

alter session set "_rowsource_execution_statistics" = TRUE;

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

<Run your SQL here;>

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

一般跟踪：

SQL> conn username/password

alter session set tracefile_identifier = 'id_10046';

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

<Run SQL>

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

查看生成的转储文件(11g)：

SELECT VS.SID SESSION_ID, VS.SERIAL#,VS.USERNAME LOGIN_USERNAME,
       VP.TRACEFILE
  FROM V$SESSION VS
  LEFT JOIN V$PROCESS VP
    ON VS.PADDR = VP.ADDR
 WHERE EXISTS (SELECT 1 FROM V$MYSTAT VM WHERE VM.SID = VS.SID);

查看生成的转储文件(10g)

SELECT VALUE || '/' || (SELECT INSTANCE_NAME FROM V$INSTANCE) || '_ora_' ||
       (SELECT spid || '.trc'
        FROM   v$process x, v$session y
        WHERE  x.ADDR = y.PADDR
        AND    Y.SID = sys_context('userenv', 'sid'))
FROM   V$PARAMETER X
WHERE  X.NAME = 'user_dump_dest';

8.跟踪其他用户session的设置

通过DBMS_SYSTEM.SET_EV系统包来实现

　8.1 查看需要跟踪会话的sid，serial#

SQL> select sid,serial#,username from v$session where username is not null;  # 用这条语句进行跟踪

       SID    SERIAL# USERNAME
---------- ---------- --------------------
       402      40632 SYS
       422      14679 TEST
       448      10490 TEST
       452      35256 SYS

或者用这句开启会话监控
sql> select sid from v$mystat rownum<2;  # 查出sid
SQL> SELECT SID,SERIAL# FROM GV$SESSION X WHERE X.SID = 140;  #查出sid,然后将sid带入到该语句，查出sid和serial#

8.2 启动跟踪

exec dbms_system.set_ev(196,41,10046,12,'');

exec dbms_system.set_sql_trace_in_session(196,41,true); --有时候需要提前开启SQL_TRACE才可以生成trace文件。

--当上面得语句执行完了之后就意味者开启了10046事件开始监控会话(196,41)所有操作

exec dbms_system.set_sql_trace_in_session(196,41,false);

exec dbms_system.set_ev(196,41,10046,0,'');

select tracefile from v$process where addr=(select paddr from v$session where sid=196); --这个是查看生成得trc文件路径

9.通过DBMS_MONITOR包进行监控

-- 开启级别为12的Trace，当前会话

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(WAITS=>true,BINDS=>true);

-- 开启级别为12的Trace，其他会话

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(:sid, :serial, true, true);

-- 关闭Trace，当前会话

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE();

-- 关闭Trace，其他会话

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(:sid, :serial);