10046是一个Oracle的内部事件(event),通过设置这个事件可以得到Oracle内部执行系统解析、调用、等待、绑定变量等详细的trace信息,对于分析系统的性能有着非常重要的作用。
设置10046事件的不同级别能得到不同详细程度的trace信息,下面就列出各个不同级别的对应作用:
等级 | 二进制 | 作用 |
---|---|---|
0 | 0000 | 无输出 |
1 | 0001 | 输出 ****,APPNAME(应用程序名),PARSING IN CURSOR,PARSE ERROR(SQL解析),EXEC(执行),FETCH(获取数据),UNMAP,SORT UNMAP(排序,临时段),ERROR,STAT(执行计划),XCTEND(事务)等行 |
2 | 0011 | 与等级1完全一样 |
4 | 0101 | 包括等级1的输出,加上BIND行(绑定变量信息) |
8 | 1001 | 包括等级1的输出,加上WAIT行(等待事件信息) |
12 | 1101 | 输出等级1、等级4以及等级8的所有信息 |
等级1的10046 trace被视为是普通的SQL Trace,而等级4、等级8以及等级12则被称为Extended SQL Trace,Extended SQL Trace里面包括了最有用的WAIT信息,因此在实际中也是用的最多的。
与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文件。
要在当前会话修改上述参数很简单,只要使用下面的命令即可:
1
2
3
|
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这个包了,设置方法如下:
1
2
3
4
5
6
7
8
9
|
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
)
|
注意,Oracle并没有提供一个set_string_param_in_session的函数在dbms_system包中,因此tracefile_identifier是无法在别的会话中修改的(至少我到现在没有找到一个可以设置的方法)。
10046 Trace启动方法
开启当前会话的10046 Trace
使用sql_trace参数
sql_trace应该是简单快捷的开启Trace的方法了,不过通过sql_trace只能开启级别为1的Trace,而无法开启其他更高级的Trace。
1
2
3
4
5
|
-- 开启Trace
ALTER
SESSION
SET
sql_trace=
true
;
-- 关闭Trace
ALTER
SESSION
SET
sql_trace=
false
;
|
使用set event开启Trace
使用set event打开10046事件Trace是最常用的了。
1
2
3
4
5
|
-- 开启级别为12的Trace,level后面的数字设置了Trace的级别
ALTER
SESSION
SET
EVENTS
'10046 trace name context forever, level 12'
-- 关闭Trace,任何级别
ALTER
SESSION
SET
EVENTS
'10046 trace name context off'
|
开启其他会话的10046 Trace
使用登陆触发器开启Trace
我们可以通过编写登陆触发器来开启10046 Trace,使用这种方法开启Trace的代码和开启当前会话的是一样的,不同的就是这些开启代码是包含在一个after logon触发器里面的。
1
2
3
4
5
6
7
8
9
10
|
-- 代码来自《Optimazing Oracle Performance》 P116
CREATE
OR
REPLACE
TRIGGER
trace_test_user
AFTER
LOGON
ON
DATABASE
BEGIN
IF
USER
LIKE
'%\_test'
ESCAPE
'\' THEN
EXECUTE IMMEDIATE '
ALTER
SESSION
SET
timed_statistics=
true
';
EXECUTE IMMEDIATE '
ALTER
SESSION
SET
max_dump_file_size=unlimited
';
EXECUTE IMMEDIATE '
ALTER
SESSION
SET
EVENTS
''
10046 trace
name
context forever,
level
8
''
';
END
IF;
END
;
/
|
使用oradebug工具
使用oradebug工具必须要知道所要处理的进程的OS进程PID,OS PID可以使用下面的语句得到:
1
2
3
4
5
6
|
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 =
UPPER
(
'&USER_NAME'
);
|
得到PID之后就可以使用oradebug工具了,注意需要使用sysdba登陆到数据库:
1
2
3
4
5
6
7
8
|
-- 假设9999为会话的OS PID
oradebug setospid 9999;
-- 设置Trace文件大小
oradebug unlimit;
-- 开启级别为12的Trace
oradebug event 10046 trace
name
context forever ,
level
12;
--关闭trace
Oradebug event 10046 trace
name
context
off
;
|
使用DBMS_SYSTEM包
DBMS_SYSTEM包提供了两个开启10046 Trace的方法,一个是使用SET_SQL_TRACE_IN_SESSION过程,不过使用这个过程的效果和sql_trace是一样的:
1
2
3
4
5
|
-- 开启Trace
EXEC
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(:sid, :serial#,
true
);
-- 关闭Trace
EXEC
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(:sid, :serial#,
false
);
|
另一个方法是使用SET_EV过程,当然这个过程不仅仅用来设置10046事件,还能设置所有的其他的事件,使用方法为:
1
2
3
4
5
6
7
8
|
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
|
使用例子:
1
2
3
4
5
|
-- 开启level 12的Trace
EXEC
SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 12,
''
);
-- 关闭Trace
EXEC
SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 0,
''
);
|