Oracle SQL Trace 和 10046 事件跟踪
- WBOY原创
- 2016-06-07 17:04:401196浏览
很多时候，对数据库进行性能诊断可以使用SQL跟踪的方法，把一些信息记录在trace文件里以后分析。一般情况下我们可以通过初始化参
很多时候，对数据库进行性能诊断可以使用SQL跟踪的方法，把一些信息记录在trace文件里以后分析。一般情况下我们可以通过初始化参数SQL_TRACE=TRUE来设置SQL跟踪。我们也可以通过设置10046事件来进行SQL跟踪，并且可以设置不同的跟踪级别，比使用SQL_TRACE获得更多的信息。
Level 0 停用SQL跟踪，相当于SQL_TRACE=FALSE
Level 1 标准SQL跟踪，相当于SQL_TRACE=TRUE
Level 4 在level 1的基础上增加绑定变量的信息
level 8 在level 1的基础上增加等待事件的信息
Level 12 在level 1的基础上增加绑定变量和等待事件的信息
10046事件不但可以跟踪用户会话(trace文件位于USER_DUMP_DEST)，也可以跟踪background进程(trace文件位于BACKGROUND_DUMP_DEST)。
trace文件的大小决定于4个因素：
跟踪级别，跟踪时长，会话的活动级别和MAX_DUMP_FILE_SIZE参数
让我们从头说起：
[b]一、Oracle跟踪文件[/b]
Oracle跟踪文件分为三种类型：
一种是后台报警日志文件：
记录数据库在启动、关闭和运行期间后台进程的活动情况,如表空间创建、回滚段创建、某些alter命令、日志切换、错误消息等。
在数据库出现故障时，应首先查看该文件，但文件中的信息与任何错误状态没有必然的联系。后台报警日志文件保存BACKGROUND_DUMP_DEST参数指定的目录中，文件格式为SIDALRT.LOG。
另一种类型是DBWR、LGWR、SMON等后台进程创建的后台跟踪文件：
后台跟踪文件根据后台进程运行情况产生，后台跟踪文件也保存在BACKGROUND_DUMP_DEST参数指定的目录中，文件格式为siddbwr.trc、sidsmon.trc等。
还有一种类型是由连接到Oracle的用户进程(ServerProcesses)生成的用户跟踪文件。
这些文件仅在用户会话期间遇到错误时产生。此外，用户可以通过执行oracle跟踪事件（见后面）来生成该类文件，用户跟踪文件保存在USER_DUMP_DEST参数指定的目录中，文件格式为oraxxxxx.trc，xxxxx为创建文件的进程号（或线程号）。
[b]二、Oracle跟踪事件[/b]
Oracle提供了一类命令，可以将Oracle各类内部结构中所包含的信息转储(dump)到跟踪文件中，以便用户能根据文件内容来解决各种故障。
设置跟踪事件有两种方法：
一种是在init.ora文件中设置事件。
这样open数据库后，将影响到所有的会话。设置格式如下：
EVENT="eventnumber trace name eventname [forever,] [level levelnumber] : ......."
通过:符号，可以连续设置多个事件，也可以通过连续使用event来设置多个事件。
另一种方法是在会话过程中使用alter session set events命令。
只对当前会话有影响。设置格式如下：
alter session set events '[eventnumber|immediate] trace name eventname [forever] [, level levelnumber] : .......'
通过:符号，可以连续设置多个事件，也可以通过连续使用alter session set events 来设置多个事件。
格式说明：event number指触发dump的事件号，事件号可以是Oracle错误号（出现相应错误时跟踪指定的事件）或oralce内部事件号，内部事件号在10000到10999之间，不能与immediate关键字同用。
immediate关键字表示命令发出后，立即将指定的结构dump到跟踪文件中，这个关键字只用在alter session语句中，并且不能与 eventnumber、forever关键字同用。
trace name 是关键字。
eventname指事件名称（见后面），即要进行dump的实际结构名。若eventname为context，则指根据内部事件号进行跟踪。
forever关键字表示事件在实例或会话的周期内保持有效状态，不能与immediate同用。
level为事件级别关键字。但在dump错误栈(errorstack)时不存在级别。
levelnumber表示事件级别号，一般从1到10，1表示只dump结构头部信息，10表示dump结构的所有信息。
1、buffers事件：dump SGA缓冲区中的db buffer结构
alter session set events 'immediate trace name buffers level 1'; --表示dump缓冲区的头部。
2、blockdump事件：dump数据文件、索引文件、回滚段文件结构
alter session set events 'immediate trace name blockdump level 66666'; --表示dump块地址为6666的数据块。
在Oracle 8以后该命令已改为：
alter system dump datafile 11 block 9; --表示dump数据文件号为11中的第9个数据块。
3、controlf事件：dump控制文件结构
alter session set events 'immediate trace name controlf level 10'; --表示dump控制文件的所有内容。
4、locks事件：dump LCK进程的锁信息
alter session set events 'immediate trace name locks level 5';
5、redohdr事件：dump redo日志的头部信息
alter session set events 'immediate trace name redohdr level 1'; --表示dump redo日志头部的控制文件项。
alter session set events 'immediate trace name redohdr level 2'; --表示dump redo日志的通用文件头。
alter session set events 'immediate trace name redohdr level 10'; --表示dump redo日志的完整文件头。
注意：redo日志的内容dump可以采用下面的语句:
alter system dump logfile 'logfilename';
6、loghist事件：dump控制文件中的日志历史项
alter session set events 'immediate trace name loghist level 1'; --表示只dump最早和最迟的日志历史项。
levelnumber大于等于2时，表示2的levelnumber次方个日志历史项。
alter session set events 'immediate trace name loghist level 4'; --表示dump 16个日志历史项。
7、file_hdrs事件：dump 所有数据文件的头部信息
alter session set events 'immediate trace name file_hdrs level 1'; --表示dump 所有数据文件头部的控制文件项。
alter session set events 'immediate trace name file_hdrs level 2'; --表示dump 所有数据文件的通用文件头。
alter session set events 'immediate trace name file_hdrs level 10'; --表示dump 所有数据文件的完整文件头。
8、errorstack事件：dump 错误栈信息，通常Oracle发生错误时前台进程将得到一条错误信息，但某些情况下得不到错误信息，可以采用这种方式得到Oracle错误。
alter session set events '604 trace name errorstack forever'; --表示当出现604错误时，dump 错误栈和进程栈。
9、systemstate事件：dump所有系统状态和进程状态
alter session set events 'immediate trace name systemstate level 10'; --表示dump 所有系统状态和进程状态。
10、coalesec事件：dump指定表空间中的自由区间
levelnumber以十六进制表示时，两个高位字节表示自由区间数目，两个低位字节表示表空间号，如0x00050000表示dump系统表空间中的5个自由区间，转换成十进制就是327680，即：
alter session set events 'immediate trace name coalesec level 327680';
11、processsate事件：dump进程状态
alter session set events 'immediate trace name processsate level 10';
12、library_cache事件：dump library cache信息
alter session set events 'immediate trace name library_cache level 10';
13、heapdump事件：dump PGA、SGA、UGA中的信息
alter session set events 'immediate trace name heapdump level 1';
14、row_cache事件：dump数据字典缓冲区中的信息
alter session set events 'immediate trace name row_cache level 1';
[b]三、内部事件号[/b]
1、10013：用于监视事务
这些文件仅在用户会话期间遇到错误时产生。此外，用户可以通过执行oracle跟踪事件（见后面）来生成该类文件，用户跟踪文件保存在USER_DUMP_DEST参数指定的目录中，文件格式为oraxxxxx.trc，xxxxx为创建文件的进程号（或线程号）。
[b]二、Oracle跟踪事件[/b]
Oracle提供了一类命令，可以将Oracle各类内部结构中所包含的信息转储(dump)到跟踪文件中，以便用户能根据文件内容来解决各种故障。
设置跟踪事件有两种方法：
一种是在init.ora文件中设置事件。
这样open数据库后，将影响到所有的会话。设置格式如下：
EVENT="eventnumber trace name eventname [forever,] [level levelnumber] : ......."
通过:符号，可以连续设置多个事件，也可以通过连续使用event来设置多个事件。
另一种方法是在会话过程中使用alter session set events命令。
只对当前会话有影响。设置格式如下：
alter session set events '[eventnumber|immediate] trace name eventname [forever] [, level levelnumber] : .......'
通过:符号，可以连续设置多个事件，也可以通过连续使用alter session set events 来设置多个事件。
格式说明：event number指触发dump的事件号，事件号可以是Oracle错误号（出现相应错误时跟踪指定的事件）或oralce内部事件号，内部事件号在10000到10999之间，不能与immediate关键字同用。
immediate关键字表示命令发出后，立即将指定的结构dump到跟踪文件中，这个关键字只用在alter session语句中，并且不能与 eventnumber、forever关键字同用。
trace name 是关键字。
eventname指事件名称（见后面），即要进行dump的实际结构名。若eventname为context，则指根据内部事件号进行跟踪。
forever关键字表示事件在实例或会话的周期内保持有效状态，不能与immediate同用。
level为事件级别关键字。但在dump错误栈(errorstack)时不存在级别。
levelnumber表示事件级别号，一般从1到10，1表示只dump结构头部信息，10表示dump结构的所有信息。
1、buffers事件：dump SGA缓冲区中的db buffer结构
alter session set events 'immediate trace name buffers level 1'; --表示dump缓冲区的头部。
2、blockdump事件：dump数据文件、索引文件、回滚段文件结构
alter session set events 'immediate trace name blockdump level 66666'; --表示dump块地址为6666的数据块。
在Oracle 8以后该命令已改为：
alter system dump datafile 11 block 9; --表示dump数据文件号为11中的第9个数据块。
3、controlf事件：dump控制文件结构
alter session set events 'immediate trace name controlf level 10'; --表示dump控制文件的所有内容。
4、locks事件：dump LCK进程的锁信息
alter session set events 'immediate trace name locks level 5';
5、redohdr事件：dump redo日志的头部信息
alter session set events 'immediate trace name redohdr level 1'; --表示dump redo日志头部的控制文件项。
alter session set events 'immediate trace name redohdr level 2'; --表示dump redo日志的通用文件头。
alter session set events 'immediate trace name redohdr level 10'; --表示dump redo日志的完整文件头。
注意：redo日志的内容dump可以采用下面的语句:
alter system dump logfile 'logfilename';
6、loghist事件：dump控制文件中的日志历史项
alter session set events 'immediate trace name loghist level 1'; --表示只dump最早和最迟的日志历史项。
levelnumber大于等于2时，表示2的levelnumber次方个日志历史项。
alter session set events 'immediate trace name loghist level 4'; --表示dump 16个日志历史项。
7、file_hdrs事件：dump 所有数据文件的头部信息
alter session set events 'immediate trace name file_hdrs level 1'; --表示dump 所有数据文件头部的控制文件项。
alter session set events 'immediate trace name file_hdrs level 2'; --表示dump 所有数据文件的通用文件头。
alter session set events 'immediate trace name file_hdrs level 10'; --表示dump 所有数据文件的完整文件头。
8、errorstack事件：dump 错误栈信息，通常Oracle发生错误时前台进程将得到一条错误信息，但某些情况下得不到错误信息，可以采用这种方式得到Oracle错误。
alter session set events '604 trace name errorstack forever'; --表示当出现604错误时，dump 错误栈和进程栈。
9、systemstate事件：dump所有系统状态和进程状态
alter session set events 'immediate trace name systemstate level 10'; --表示dump 所有系统状态和进程状态。
10、coalesec事件：dump指定表空间中的自由区间
levelnumber以十六进制表示时，两个高位字节表示自由区间数目，两个低位字节表示表空间号，如0x00050000表示dump系统表空间中的5个自由区间，转换成十进制就是327680，即：
alter session set events 'immediate trace name coalesec level 327680';
11、processsate事件：dump进程状态
alter session set events 'immediate trace name processsate level 10';
12、library_cache事件：dump library cache信息
alter session set events 'immediate trace name library_cache level 10';
13、heapdump事件：dump PGA、SGA、UGA中的信息
alter session set events 'immediate trace name heapdump level 1';
14、row_cache事件：dump数据字典缓冲区中的信息
alter session set events 'immediate trace name row_cache level 1';
[b]三、内部事件号[/b]
1、10013：用于监视事务恢复
2、10015：转储UNDO SEGMENT头部
event = "10015 trace name context forever"
3、10029：用于给出会话期间的登陆信息
4、10030：用于给出会话期间的注销信息
5、10032：转储排序的统计信息
6、10033：转储排序增长的统计信息
7、10045：跟踪Freelist管理操作
8、10046：跟踪SQL语句
alter session set events '10046 trace name context forever, level 4'; --跟踪SQL语句并显示绑定变量
alter session set events '10046 trace name context forever, level 8'; --跟踪SQL语句并显示等待事件
9、10053：转储优化策略
10、10059：模拟redo日志中的创建和清除错误
11、10061：阻止SMON进程在启动时清除临时段
12、10079：转储 SQL*NET统计信息
13、10081：转储高水标记变化
14、10104：转储Hash连接统计信息
15、10128：转储分区休整信息
16、10200：转储一致性读信息
17、10201：转储一致性读中Undo应用
18、10209：允许在控制文件中模拟错误
19、10210：触发数据块检查事件
event = "10210 trace name context forever, level 10"
20、10211：触发索引检查事件
21、10213：模拟在写控制文件后崩溃
22、10214：模拟在控制文件中的写错误
levelnumber从1-9表示产生错误的块号，大于等于10则每个控制文件将出错
23、10215：模拟在控制文件中的读错误
24、10220：转储Undo头部变化
25、10221；转储Undo变化
26、10224：转储索引的分隔与删除
27、10225：转储基于字典管理的区间的变化
28、10229：模拟在数据文件上的I/O错误
29、10231：设置在全表扫描时忽略损坏的数据块
alter session set events '10231 trace name context off'; -- 关闭会话期间的数据块检查
event = "10231 trace name context forever, level 10" -- 对任何进程读入SGA的数据块进行检查
30、10232：将设置为软损坏（DBMS_REPAIR包设置或DB_BLOCK_CHECKING为TRUE时设置）的数据块dump到跟踪文件
31、10235：用于内存堆检查
alter session set events '10235 trace name context forever, level 1';
32、10241：转储远程SQL执行
33、10246：跟踪PMON进程
34、10248：跟踪dispatch进程
35、10249：跟踪MTS进程
36、10252：模拟写数据文件头部错误
37、10253：模拟写redo日志文件错误
38、10262：允许连接时存在内存泄漏
alter session set events '10262 trace name context forever, level 300'; -- 允许存在300个字节的内存泄漏
39、10270：转储共享游标
40、10285：模拟控制文件头部损坏
41、10286：模拟控制文件打开错误
42、10287：模拟归档出错
43、10357：调试直接路径机制
44、10500：跟踪SMON进程
45、10608：跟踪位图索引的创建
46、10704：跟踪enqueues
47、10706：跟踪全局enqueues
48、10708：跟踪
28、10229：模拟在数据文件上的I/O错误
29、10231：设置在全表扫描时忽略损坏的数据块
alter session set events '10231 trace name context off'; -- 关闭会话期间的数据块检查
event = "10231 trace name context forever, level 10" -- 对任何进程读入SGA的数据块进行检查
30、10232：将设置为软损坏（DBMS_REPAIR包设置或DB_BLOCK_CHECKING为TRUE时设置）的数据块dump到跟踪文件
31、10235：用于内存堆检查
alter session set events '10235 trace name context forever, level 1';
32、10241：转储远程SQL执行
33、10246：跟踪PMON进程
34、10248：跟踪dispatch进程
35、10249：跟踪MTS进程
36、10252：模拟写数据文件头部错误
37、10253：模拟写redo日志文件错误
38、10262：允许连接时存在内存泄漏
alter session set events '10262 trace name context forever, level 300'; -- 允许存在300个字节的内存泄漏
39、10270：转储共享游标
40、10285：模拟控制文件头部损坏
41、10286：模拟控制文件打开错误
42、10287：模拟归档出错
43、10357：调试直接路径机制
44、10500：跟踪SMON进程
45、10608：跟踪位图索引的创建
46、10704：跟踪enqueues
47、10706：跟踪全局enqueues
48、10708：跟踪RAC的buffer cache
49、10710：跟踪对位图索引的访问
50、10711：跟踪位图索引合并操作
51、10712：跟踪位图索引OR操作
52、10713：跟踪位图索引AND操作
53、10714：跟踪位图索引MINUS操作
54、10715：跟踪位图索引向ROWID的转化
55、10716：跟踪位图索引的压缩与解压
56、10719：跟踪位图索引的修改
57、10731：跟踪游标声明
58、10928：跟踪PL/SQL执行
59、10938：转储PL/SQL执行统计信息
最后要说明的是，由于版本不同以上语法可能有些变化，但大多数还是可用的。
附完整的跟踪事件列表，event No.10000 to 10999
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/
1.select sid,serial#,username,oSUSEr,machine from v$session;查询session的sid,serial#
2.exec dbms_system.set_sql_trace_in_session(141,6,true);开始跟踪
3.exec dbms_system.set_sql_trace_in_session(141,6,true) 停止跟踪
4.tkprof *.trc *.txt
1.select sid,serial#,username,osuser,machine from v$session;查询session的sid,serial#
2.exec dbms_system.set_sql_trace_in_session(141,6,true);开始跟踪
3.exec dbms_system.set_sql_trace_in_session(141,6,true) 停止跟踪
4.tkprof *.trc *.txt
5.查看文件
[b]四、跟踪事件10046[/b]
一、使用跟踪事件10046
很多时候，对数据库进行性能诊断可以使用SQL跟踪的方法，把一些信息记录在trace文件里以后分析。一般情况下我们可以通过初始化参数SQL_TRACE=TRUE来设置SQL跟踪。我们也可以通过设置10046事件来进行SQL跟踪，并且可以设置不同的跟踪级别，比使用SQL_TRACE获得更多的信息。
Level 0 停用SQL跟踪，相当于SQL_TRACE=FALSE
Level 1 标准SQL跟踪，相当于SQL_TRACE=TRUE
Level 4 在level 1的基础上增加绑定变量的信息
level 8 在level 1的基础上增加等待事件的信息
Level 12 在level 1的基础上增加绑定变量和等待事件的信息
10046事件不但可以跟踪用户会话(trace文件位于USER_DUMP_DEST)，也可以跟踪background进程(trace文件位于BACKGROUND_DUMP_DEST)。
trace文件的大小决定于4个因素：
跟踪级别，跟踪时长，会话的活动级别和MAX_DUMP_FILE_SIZE参数。
二、启用跟踪事件10046
0.准备工作
1)Init.ORA参数
timed_statistics 设置为true(也可以在session上设置),否则不会有CPU时间信息
user_dump_dest 指定trace文件生成的目录
max_dump_file_size trace文件的最大尺寸(单位为操作系统块),UMLIMITED表示没有限制，Oracle8以后可以在后面加上K或M来表示文件大小
optimizer_mode 定义缺省的查询优化器。虽然可以用alter session来设置，但在格式化trace文件里optimizer_mode会回复到原来的设置(一个新的session来分析SQL的执行计划)，这样会产生不准确的执行计划，所以建议不要通过session来修改这个参数。
注：在运行tkprof时不要加explain参数，就不存在这个问题，执行计划是Oracle在运行时所用的计划
2) 确定是以"dedicated"方式连接到数据库
通过tnsping service_name查看
1.在全局设置
在初始化参数init.ora中加入：
EVENT = "10046 trace name context forever, level 8"
或
SQL_TRACE = TRUE
或
SQL> exec dbms_monitor.database_trace_enable('waits=>true,binds=>false,instance_name=>'wending');
全局设置会对系统性能造成明显的影响，建议不要使用。
2.跟踪当前session
SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;
或
SQL> alter session set events '10046 trace name context forever, level 8';
SQL> alter session set events '10046 trace name context off';
或
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);
或
SQL> @?/rdbms/admin/dbmssupp.sql --DBMS_SUPPORT包需要单独
49、10710：跟踪对位图索引的访问
50、10711：跟踪位图索引合并操作
51、10712：跟踪位图索引OR操作
52、10713：跟踪位图索引AND操作
53、10714：跟踪位图索引MINUS操作
54、10715：跟踪位图索引向ROWID的转化
55、10716：跟踪位图索引的压缩与解压
56、10719：跟踪位图索引的修改
57、10731：跟踪游标声明
58、10928：跟踪PL/SQL执行
59、10938：转储PL/SQL执行统计信息
最后要说明的是，由于版本不同以上语法可能有些变化，但大多数还是可用的。
附完整的跟踪事件列表，event No.10000 to 10999
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/
1.select sid,serial#,username,osuser,machine from v$session;查询session的sid,serial#
2.exec dbms_system.set_sql_trace_in_session(141,6,true);开始跟踪
3.exec dbms_system.set_sql_trace_in_session(141,6,true) 停止跟踪
4.tkprof *.trc *.txt
1.select sid,serial#,username,osuser,machine from v$session;查询session的sid,serial#
2.exec dbms_system.set_sql_trace_in_session(141,6,true);开始跟踪
3.exec dbms_system.set_sql_trace_in_session(141,6,true) 停止跟踪
4.tkprof *.trc *.txt
5.查看文件
[b]四、跟踪事件10046[/b]
一、使用跟踪事件10046
很多时候，对数据库进行性能诊断可以使用SQL跟踪的方法，把一些信息记录在trace文件里以后分析。一般情况下我们可以通过初始化参数SQL_TRACE=TRUE来设置SQL跟踪。我们也可以通过设置10046事件来进行SQL跟踪，并且可以设置不同的跟踪级别，比使用SQL_TRACE获得更多的信息。
Level 0 停用SQL跟踪，相当于SQL_TRACE=FALSE
Level 1 标准SQL跟踪，相当于SQL_TRACE=TRUE
Level 4 在level 1的基础上增加绑定变量的信息
level 8 在level 1的基础上增加等待事件的信息
Level 12 在level 1的基础上增加绑定变量和等待事件的信息
10046事件不但可以跟踪用户会话(trace文件位于USER_DUMP_DEST)，也可以跟踪background进程(trace文件位于BACKGROUND_DUMP_DEST)。
trace文件的大小决定于4个因素：
跟踪级别，跟踪时长，会话的活动级别和MAX_DUMP_FILE_SIZE参数。
二、启用跟踪事件10046
0.准备工作
1)Init.ORA参数
timed_statistics 设置为true(也可以在session上设置),否则不会有CPU时间信息
user_dump_dest 指定trace文件生成的目录
max_dump_file_size trace文件的最大尺寸(单位为操作系统块),UMLIMITED表示没有限制，Oracle8以后可以在后面加上K或M来表示文件大小
optimizer_mode 定义缺省的查询优化器。虽然可以用alter session来设置，但在格式化trace文件里optimizer_mode会回复到原来的设置(一个新的session来分析SQL的执行计划)，这样会产生不准确的执行计划，所以建议不要通过session来修改这个参数。
注：在运行tkprof时不要加explain参数，就不存在这个问题，执行计划是Oracle在运行时所用的计划
2) 确定是以"dedicated"方式连接到数据库
通过tnsping service_name查看
1.在全局设置
在初始化参数init.ora中加入：
EVENT = "10046 trace name context forever, level 8"
或
SQL_TRACE = TRUE
或
SQL> exec dbms_monitor.database_trace_enable('waits=>true,binds=>false,instance_name=>'wending');
全局设置会对系统性能造成明显的影响，建议不要使用。
2.跟踪当前session
SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;
或
SQL> alter session set events '10046 trace name context forever, level 8';
SQL> alter session set events '10046 trace name context off';
或
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);
或
SQL> @?/rdbms/admin/dbmssupp.sql --DBMS_SUPPORT包需要单独安装,用sys用户安装
SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace;
在Oracle10g里推荐使用DBMS_MONITOR:
SQL> EXEC DBMS_MONITOR.session_trace_enable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable;
3.对其他用户session跟踪
首先从v$session中获得sid和serial#, 然后:
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
或
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');
或
SQL> @?/rdbms/admin/dbmssupp.sql --DBMS_SUPPORT包需要单独安装,用sys用户安装
SQL> EXEC DBMS_SUPPORT.start_trace(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace(sid=>123, serial=>1234);
也可以通过使用oradebug工具来设置10046事件, 首先通过V$PROCESS获得该session的spid, 然后:
SQL> oradebug setospid 12345;
SQL> oradebug unlimit;
SQL> oradebug event 10046 trace name context forever, level 8;
SQL> oradebug event 10046 trace name context off;
在Oracle10g里推荐使用DBMS_MONITOR:
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);
以下还能同时跟踪多个session, client_id通过 DBMS_SESSION 包设置在 v$session 里:
SQL> exec dbms_session.set_identifier('tim_hall');
SQL> select sid,serial#,username,client_identifier from v$session where client_identifier is not null;
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>'tim_hall');
以下还能同时跟踪多个session, service_name, module, action columns通过 DBMS_APPLICATION_INFO 包设置在 v$session 里:
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'wending.lk', module_name=>'test_api', action_name=>'running');
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'wending.lk', module_name=>'test_api', action_name=>'running', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_disable(service_name=>'wending.lk', module_name=>'test_api', action_name=>'running');
三、获取跟踪文件
trace文件名是SID_ora_xxxx.trc，其中xxxx是与Oracle连接的shadow进程的PID，SID是Oracle实例的ORACLE_SID。
文件生成在Init.ORA参数user_dump_dest指定的目录下。
1.使用oradebug
SQL> oradebug setmypid
SQL> oradebug tracefile_name
/opt/oracle/product/9.2.0/rdbms/log/uxdb_ora_9183.trc
2.设置初始参数TRACEFILE_IDENTIFIER
SQL> alter session set tracefile_identifier = 'MyTrace';
这样在生成的trace文件名中会包含有MyTrace字样:
/opt/oracle/product/9.2.0/rdbms/log/uxdb_ora_9183_MyTrace.trc
3.通过SQL查询
SQL> 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;
/opt/oracle/db01/app/oracle/admin/ORCL/udump/orcl_ora_8066.trc
4. 修改生成跟踪的文件名称
激活跟踪后，就可以用新工具trcsess来生成trace文件，先跳转到udump目录
$ cd $ORACLE_BASE/admin/$ORACLE_SID/udump
$ trcsess utput="test.trc" session=123.1234
其中session必须是SID.SERIAL#的格式, 打开产生的test.trc文件，，会发现内面的内容实际上相当于10046和10053事件产生的trace文件内容的组合。
四、用tkprof格式化trace文件
常用：tkprof ORCL_ora_xxxx.trc ORCL_ora_yyyy.trc report.txt sys=no sort=fchela
tkprof是用来解释trace文件内容，把原始的trace文件转化为容易理解的文件。使用方法为:
tkprof trace文件名 报告文件名 [sort=option]
首先解释输出文件中列的含义：
? CALL：每次SQL语句的处理都分成三个部分
Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。
Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。
Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。
? COUNT:这个语句被parse、execute、fetch的次数。
? CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。
? ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。
? DISK：从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。
? QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。
? CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。
? ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。
sort参数是用来指定输出的SQL是按什么数据来排序(如cpu时间或elapsed时间,详见tkprof的使用参数说明)
在report.txt中有关于每个SQL的parse/execute/fetch/disk read/buffer get/cpu time/执行计划(包括每一步运行时的行数),样例如下：
********************************************************************************
select owner#
from
obj$ o where obj# = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 time=24 us)
1 INDEX UNIQUE SCAN I_OBJ1 (cr=2 r=0 w=0 time=12 us)(object id 33)
********************************************************************************
在report.txt文件头有各个数据的解释，根据以下一些指标可以分析一下SQL的执行性能:
(query+current)/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
Parse count/Execute count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse。要检查Pro*C程序的MAXOPENCURSORS是不是太低了，或不适当的使用的RELEASE_CURSOR选项
rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，增加了数据在客户端和
SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace;
在Oracle10g里推荐使用DBMS_MONITOR:
SQL> EXEC DBMS_MONITOR.session_trace_enable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable;
3.对其他用户session跟踪
首先从v$session中获得sid和serial#, 然后:
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
或
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');
或
SQL> @?/rdbms/admin/dbmssupp.sql --DBMS_SUPPORT包需要单独安装,用sys用户安装
SQL> EXEC DBMS_SUPPORT.start_trace(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace(sid=>123, serial=>1234);
也可以通过使用oradebug工具来设置10046事件, 首先通过V$PROCESS获得该session的spid, 然后:
SQL> oradebug setospid 12345;
SQL> oradebug unlimit;
SQL> oradebug event 10046 trace name context forever, level 8;
SQL> oradebug event 10046 trace name context off;
在Oracle10g里推荐使用DBMS_MONITOR:
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);
以下还能同时跟踪多个session, client_id通过 DBMS_SESSION 包设置在 v$session 里:
SQL> exec dbms_session.set_identifier('tim_hall');
SQL> select sid,serial#,username,client_identifier from v$session where client_identifier is not null;
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>'tim_hall');
以下还能同时跟踪多个session, service_name, module, action columns通过 DBMS_APPLICATION_INFO 包设置在 v$session 里:
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'wending.lk', module_name=>'test_api', action_name=>'running');
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'wending.lk', module_name=>'test_api', action_name=>'running', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_disable(service_name=>'wending.lk', module_name=>'test_api', action_name=>'running');
三、获取跟踪文件
trace文件名是SID_ora_xxxx.trc，其中xxxx是与Oracle连接的shadow进程的PID，SID是Oracle实例的ORACLE_SID。
文件生成在Init.ORA参数user_dump_dest指定的目录下。
1.使用oradebug
SQL> oradebug setmypid
SQL> oradebug tracefile_name
/opt/oracle/product/9.2.0/rdbms/log/uxdb_ora_9183.trc
2.设置初始参数TRACEFILE_IDENTIFIER
SQL> alter session set tracefile_identifier = 'MyTrace';
这样在生成的trace文件名中会包含有MyTrace字样:
/opt/oracle/product/9.2.0/rdbms/log/uxdb_ora_9183_MyTrace.trc
3.通过SQL查询
SQL> 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;
/opt/oracle/db01/app/oracle/admin/ORCL/udump/orcl_ora_8066.trc
4. 修改生成跟踪的文件名称
激活跟踪后，就可以用新工具trcsess来生成trace文件，先跳转到udump目录
$ cd $ORACLE_BASE/admin/$ORACLE_SID/udump
$ trcsess utput="test.trc" session=123.1234
其中session必须是SID.SERIAL#的格式, 打开产生的test.trc文件，会发现内面的内容实际上相当于10046和10053事件产生的trace文件内容的组合。
四、用tkprof格式化trace文件
常用：tkprof ORCL_ora_xxxx.trc ORCL_ora_yyyy.trc report.txt sys=no sort=fchela
tkprof是用来解释trace文件内容，把原始的trace文件转化为容易理解的文件。使用方法为:
tkprof trace文件名 报告文件名 [sort=option]
首先解释输出文件中列的含义：
? CALL：每次SQL语句的处理都分成三个部分
Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。
Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。
Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。
? COUNT:这个语句被parse、execute、fetch的次数。
? CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。
? ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。
? DISK：从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。
? QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。
? CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。
? ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。
sort参数是用来指定输出的SQL是按什么数据来排序(如cpu时间或elapsed时间,详见tkprof的使用参数说明)
在report.txt中有关于每个SQL的parse/execute/fetch/disk read/buffer get/cpu time/执行计划(包括每一步运行时的行数),样例如下：
********************************************************************************
select owner#
from
obj$ o where obj# = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 time=24 us)
1 INDEX UNIQUE SCAN I_OBJ1 (cr=2 r=0 w=0 time=12 us)(object id 33)
********************************************************************************
在report.txt文件头有各个数据的解释，根据以下一些指标可以分析一下SQL的执行性能:
(query+current)/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
Parse count/Execute count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse。要检查Pro*C程序的MAXOPENCURSORS是不是太低了，或不适当的使用的RELEASE_CURSOR选项
rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，增加了数据在客户端和服务器之间的往返次数。在Pro*C中可以用prefetch=NN,Java/JDBC中可调用SETROWPREFETCH,在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)
disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
cpu OR elapsed 太大表示执行时间过长，或消耗了大量的CPU时间，应该考虑优化
执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少
整合.trc文件:
tkprof *.trc /file/ora_trc/ora_trc.txt explain=username/pwd sys=no insert=/file/ora_trc/insert.sql record=/file/ora_trc/record.txt aggregate=no waits=yes
tkprof的参数有下面几个:
explain=username/password -> connect to oracle and issue explain plain
talbe=schema.tablename -> use'schema.table' with explain option
aggregate=yes/no
insert=filename -> list sql statements and
disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
cpu OR elapsed 太大表示执行时间过长，或消耗了大量的CPU时间，应该考虑优化
执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少
整合.trc文件:
tkprof *.trc /file/ora_trc/ora_trc.txt explain=username/pwd sys=no insert=/file/ora_trc/insert.sql record=/file/ora_trc/record.txt aggregate=no waits=yes
tkprof的参数有下面几个:
explain=username/password -> connect to oracle and issue explain plain
talbe=schema.tablename -> use'schema.table' with explain option
aggregate=yes/no
insert=filename -> list sql statements anddatainside insert statements
sys=no -> tkprof does not list sql statements run as user sys.
record=filename -> record non-recursive statements found in the trace file
print=integer -> list only the first 'integer' sql statements
sort=option -> set zero or more of the following sort options
option详细参数：
Sort Option Description
-------------- ------------------------------------------------
execnt number of execute was called
execpu cpu time spent executing
execu number of buffers for current read during execute
exedsk number of disk reads during execute
exeela elapsed time executing
exemis number of library cache misses during execute
exeqry number of buffers for consistent read during execute
exerow number of rows processed during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchcu number of buffers for current read during fetch
fchdsk number of disk reads during fetch
fchela elapsed time fetching
fchqry number of buffers for consistent read during fetch
fchrow number of rows fetched
prscnt number of times parse was called
prscpu cpu time parsing
prscu number of buffers for current read during parse
prsdsk number of disk reads during parse
prsela elapsed time parsing
prsmis number of misses in library cache during parse
prsqry number of buffers for consistent read during parse
userid userid of user that parsed the cursor
sys=no -> tkprof does not list sql statements run as user sys.
record=filename -> record non-recursive statements found in the trace file
print=integer -> list only the first 'integer' sql statements
sort=option -> set zero or more of the following sort options
option详细参数：
Sort Option Description
-------------- ------------------------------------------------
execnt number of execute was called
execpu cpu time spent executing
execu number of buffers for current read during execute
exedsk number of disk reads during execute
exeela elapsed time executing
exemis number of library cache misses during execute
exeqry number of buffers for consistent read during execute
exerow number of rows processed during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchcu number of buffers for current read during fetch
fchdsk number of disk reads during fetch
fchela elapsed time fetching
fchqry number of buffers for consistent read during fetch
fchrow number of rows fetched
prscnt number of times parse was called
prscpu cpu time parsing
prscu number of buffers for current read during parse
prsdsk number of disk reads during parse
prsela elapsed time parsing
prsmis number of misses in library cache during parse
prsqry number of buffers for consistent read during parse
userid userid of user that parsed the cursor
