10046跟踪事件

最新推荐文章于 2021-04-12 01:35:08 发布

cuimiwan3140

最新推荐文章于 2021-04-12 01:35:08 发布

阅读量200

点赞数

文章标签：数据库

1.什么是10046事件

10046是一个oracle的内部事件（event），是用来进行sql跟踪的强有力工具，可以跟踪解析过程，执行计划，绑定变量，递归调用等

2.1046事件等级

等级二进制作用

0 0000 无输出

1 0001 输出****,APPNAME(应用程序名),PARSING IN CURSOR,PARSE ERROR(SQL解析),EXEC(执行),FETCH(获取),UNAMAP,SORT UNMAP(排序，临时段),ERROR,STAT(执行计划),XCTEND(事务)..

2 0011 与等级1完全一样

4 0101 包括等级1的输出，加上BIND行（绑定变量信息）

8 1001 包括等级1的输出，加上WAIT行（等待事件信息）

12 1101 输出等级1、等级4、等级8的所有信息

3.SQL trace相关的参数

----在打开10046事件的sql trace之前，要先设置好一些参数

timed_statistics

----这个参数决定了是否收集与时间相关的统计信息，如果这个参数为false的话，那么sql trace的结果基本没有多大的用处。默认true

max_dump_file_size

----dump文件的大小，是否限制sql trace文件的大小。默认unlimited

tracefile_identifier

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

----修改方法：

SQL> alter session set timed_statistics=true;

Session altered.

SQL> alter session set max_dump_file_size=unlimited;

Session altered.

SQL> alter session set tracefile_identifier='shall_trace_session';

Session altered.

4.打开10046事件

----开启/关闭trace功能

SQL> alter session set sql_trace=true;

Session altered.

SQL> alter session set sql_trace=false;

Session altered.

4.1使用set event打开10046事件

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

Session altered.

----关闭10046事件，所有级别

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

Session altered.

4.2使用oradebug工具

----先查找用户PID，然后使用oradebug工具

SQL> col username for a10

SQL> col OS_PROCESS_ID for a20

SQL> select s.username,p.spid os_process_id,p.pid oracle_process_id from v$session s,v$process p where s.paddr=p.addr and s.username is not null;

USERNAME OS_PROCESS_ID ORACLE_PROCESS_ID

---------- -------------------- -----------------

SCOTT 26520 19

SYS 26023 32

----设置os pid

SQL> oradebug setospid 26023;

Oracle pid: 32, Unix process pid: 26023, image: oracle@node1.test.com (TNS V1-V3)

----设置trace文件大小

SQL> oradebug unlimit;

Statement processed.

----开启级别为12的trace

SQL> oradebug event 10046 trace name context forever,level 12;

Statement processed.

----关闭trace

SQL> oradebug event 10046 trace name context off;

Statement processed.

4.3使用DBMS_SYSTEM包

----首先查看用户session

SQL> select sid,serial#,username from v$session ;

SID SERIAL# USERNAME

---------- ---------- ----------

40 61 SCOTT

41 157 SYS

----开启trace

SQL> exec sys.dbms_system.set_sql_trace_in_session(41,157,true);

PL/SQL procedure successfully completed.

----关闭trace

SQL> exec sys.dbms_system.set_sql_trace_in_session(41,157,false);

PL/SQL procedure successfully completed.

4.4使用DBMS_SYSTEM包2

----首先也需要查找用户session

SQL> select sid,serial#,username from v$session ;

SID SERIAL# USERNAME

---------- ---------- ----------

40 61 SCOTT

41 157 SYS

----开启级别为12的trace

SQL> exec sys.dbms_system.set_ev(40,61,10046,12,'scott')

PL/SQL procedure successfully completed.

----关闭trace

SQL> exec sys.dbms_system.set_ev(40,61,10046,0,'scott')

PL/SQL procedure successfully completed.

5.获取跟踪文件

----获取跟踪文件存放目录

SQL> select name, value from v$parameter where name = 'user_dump_dest';

SQL> show parameter user_dump_dest

----跟踪文件格式

{instance name}_{process name}_{process id}.trc

----获取跟踪文件（如果设置了tracefile_identifier参数，在查询结果中补全参数）

select

d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name

from

( select p.spid

from sys.v$mystat m,sys.v$session s,sys.v$process p

where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,

( select t.instance from sys.v$thread t,sys.v$parameter v

where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,

( select value from sys.v$parameter where name = 'user_dump_dest') d;

TRACE_FILE_NAME

---------------------------------------------------------

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26023.trc

----11G中可以直接获取跟踪文件

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

VALUE

------------------------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26023_shall_trace_session.trc

1 row selected.

6.获取当前session设置的event

6.1 通过dbms_system.read_ev获取

当我们通过alter session的方式设置了sql_trace,这个设置是不能通过show parameter的方式得到的,我们需要通过dbms_system.read_ev来获取：

SQL> set serveroutput on

declare

event_level number;

begin

for event_number in 10000..65096 loop

sys.dbms_system.read_ev(event_number,event_level);

if (event_level > 0) then

dbms_output.put_line('Event '||to_char(event_number)||' is set at level '||to_char

(event_level));

end if;

end loop;

end;

6.2 通过oradebug获取

SQL> select s.username,p.spid os_process_id,p.pid oracle_process_id from v$session s,v$process p where s.paddr=p.addr and s.username is not null;

SQL> oradebug setospid os_process_id;

SQL> oradebug eventdump session

6.3 实验验证

----（1）设置测试event

----session

SQL> alter session set events '10513 trace name context forever,level 2';

Session altered.

SQL> alter session set events '10510 trace name context forever,level 1';

Session altered.

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

Session altered.

----system

SQL> alter system set events '60025 trace name context forever';

System altered.

SQL> alter system set events '10010 trace name context forever,level 2';

System altered.

----（2）通过dbms_system.read_ev获取会话event

SQL> set serveroutput on

SQL> declare

2 event_level number;

3 begin

4 for event_number in 10000..65096 loop

5 sys.dbms_system.read_ev(event_number,event_level);

6 if (event_level > 0) then

7 dbms_output.put_line('Event '||to_char(event_number)||' is set at level '||to_char

8 (event_level));

9 end if;

10 end loop;

11 end;

12 /

Event 10010 is set at level 2

Event 10510 is set at level 1

Event 10513 is set at level 2

Event 60025 is set at level 1

PL/SQL procedure successfully completed.

----设置了5个参数？为什么没有10046的参数呢？

----（3）用oradebug获取

SQL> col username for a10

SQL> col OS_PROCESS_ID for a20

SQL> select s.username,p.spid os_process_id,p.pid oracle_process_id from v$session s,v$process p where s.paddr=p.addr and s.username is not null;

USERNAME OS_PROCESS_ID ORACLE_PROCESS_ID

---------- -------------------- -----------------

SCOTT 26520 19

SYS 32224 31

SQL> oradebug setospid 32224;

Oracle pid: 31, Unix process pid: 32224, image: oracle@node1.test.com (TNS V1-V3)

SQL> oradebug eventdump session

60025 trace name context forever

10010 trace name context forever,level 2

sql_trace level=12

10510 trace name context forever,level 1

10513 trace name context forever,level 2

SQL>

----（4）重新连接会话后，再查看

SQL> set serveroutput on

SQL> declare

2 event_level number;

3 begin

4 for event_number in 10000..65096 loop

5 sys.dbms_system.read_ev(event_number,event_level);

6 if (event_level > 0) then

7 dbms_output.put_line('Event '||to_char(event_number)||' is set at level '||to_char

8 (event_level));

9 end if;

10 end loop;

11 end;

12 /

Event 10010 is set at level 2

Event 60025 is set at level 1

PL/SQL procedure successfully completed.

SQL> col username for a10

SQL> col OS_PROCESS_ID for a20

SQL> select s.username,p.spid os_process_id,p.pid oracle_process_id from v$session s,v$process p where s.paddr=p.addr and s.username is not null;

USERNAME OS_PROCESS_ID ORACLE_PROCESS_ID

---------- -------------------- -----------------

SCOTT 26520 19

SYS 742 31

SQL> oradebug setospid 742;

Oracle pid: 31, Unix process pid: 742, image: oracle@node1.test.com (TNS V1-V3)

SQL> oradebug eventdump session

10010 trace name context forever,level 2

60025 trace name context forever

SQL>

----（5）关闭event

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

System altered.

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

System altered.

SQL> oradebug eventdump session

Statement processed.

6.4 dbms_system和oradebug获取event总结

使用dbms_system不能获取到10046，使用oradebug可以获取

7.tkprof对trace文件进行格式化

SQL_TRACE生成最原始的trace文件的可读性比较差，所以通常我们使用tkprof 工具来处理trace文件。tkprof工具是Oracle 自带的一个工具，用于处理原始的trace文件，它的作用主要是合并汇总trace文件中的一些项，规范化文件的格式，使文件更具有可读性。
注意：tkprof 工具只能用在处理SQL_TRACE和10046事件产生的trace，其他事件如10053不能处理。