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不能处理。
[oracle@node1 ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26023_shall_trace_session.trc 10046.txt
TKPROF: Release 11.2.0.1.0 - Development on Sat May 21 16:15:44 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
[oracle@node1 ~]$
[oracle@node1 ~]$ more 10046.txt
TKPROF: Release 11.2.0.1.0 - Development on Sat May 21 16:15:44 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26023_shall_trace_session
.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
reference http://blog.csdn.net/rulev5/article/details/7075390 tkprof参数详解
reference http://blog.itpub.net/17252115/viewspace-751541/ 打开10046trace各种方法
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30130773/viewspace-2104641/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2104641/