Oracle 10046 Trace跟踪方法详解

来源：互联网发布：国家卫计委数据编辑：程序博客网时间：2024/05/30 04:45

10046概述

10046是一种特殊的EVENT。它可以让Oracle来进行类似于SQL_TRACE类型的动作。

Event 10046是为Oraclesession收集扩展的sql_trace信息的标准方。它就相当于把SQL_TRACE=true。使用这个事件的好处就是可能一些其他的数据可以输出到trace文件中，当然这也要取决于启动这个事件时指定的LEVEL。

在11G中你可以使用新的事件”sql_trace“来设置”10046”类型的trace。如：

Alter session set events ‘sql_tracewait=false, bind=true’;

当用户提出OracleSupport请求时，可以通过这个事件来收集特殊SQL问题的信息。

10046 EVENT levels

这些值都是bit值，所以它们可以通过或运算组合在一起，产生不同的值。下面[]中代表的是新特性SQL_TRACE中的值。

1 – 启动标准的SQL_TRACE或者（默认级别）

4 – 级别1加上绑定变量值 [bind=true]

8 – 级别1加上等待事件信息 [wait=true]

在确定latch wait的事件时特别有用。当然也可以用来确定full table scans和indexes scans.

对于11g来说这些bit 级别是可用的。

16 – 为每个执行生成STAT 行的dump信息 [plan_stat=all_executions]

32 – 不生成execution 的统计信息 [ plan_stat=never]

对于11.2.0.2来说这些bit级别是可用的：

64 – 自适应的STAT行信息。 [plan_stat=adaptive]

当一个SQL花费的时间超过1分钟时，才会生成dump信息，当然也会提供花费更长时间的sql的信息和这种SQL不同执行（计划或者其他）的信息。

eg: 一个通常的事件级别为12，它包含了标准的SQL_TRACE输出，绑定变量、等待事件和默认的STAT 行信息。

NOTE:在11g中STAT 的dump信息已经被调整，它不会把每次执行的信息进行汇总，而是在每次执行后显示。这可以用来游标没有被关闭的情况，这时stat不会被输出。现在我们可以确保在执行之后会捕捉到STAT信息，要更好的控制STAT信息的输出，参考上面的bit levels。

Trace相关的参数

在进行trace之前，有一些数据库参数需要设置，这样trace跟踪出来的信息才完整。

这些参数应该在initSID.ora中针对你要进行trace的实例进行设置，当然你可以在session级别进行设置。

TIMED_STATISTICS

这个参数可以用来启用\禁用timed statistics类别的统计信息，如CPU和elapsed times。

l TRUE – enable timing

l FALSE – Disable timing (Default value)

注意：如果参数TIMED_STATISTICS设置为FALSE，时间相关的参数没有设置，那么trace 出来的许多信息是没有用的。

MAX_DUMP_FILE_SIZE

这个参数用来指定trace文件占用操作系统块的最大值。在8i中默认值是10000个OS块。在9i中受最大可用空间的限制。如果你的trace文件被截断，那么你在trace文件中可以看到：

*** DUMP FILE SIZE IS LIMITED TO 12345BYTES***

说明，你的参数值需要增加。

USER_DUMP_DEST

指定trace文件的路径。

这个参数的默认值是你的操作系统中oracle dump的默认路径。

Note:大多数参数是可以使用altersystem/alter session命令来进行动态调整的。使用使用下面的语句：

ALTERSYSTEM/SESSION SET TIMED_STATISTICS = TRUE/FALSE;

USER_DUMP_DEST只能在系统级别进行设置。

修改session级别参数

alter session set tracefile_identifier='10046';

alter session set timed_statistics = true;

alter session set statistics_level=all;

alter session set max_dump_file_size = unlimited;

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

-- 执行需要被trace的SQL --

select * from dual;

exit;

如果不退出当前session,可以用以下命令关闭trace:

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

注意，如果session没有被彻底地关闭并且跟踪被停止了，某些重要的trace信息的可能会丢失。

注意：这里我们将"statistics_level"设置为all，这是因为有可能这个参数在系统级不是默认值"TYPICAL"（比如 BASIC）。为了收集性能相关问题的信息我们需要打开某个级别的statistics。我们推荐在 session 级将这个参数设置成 ALL 以便于收集更多的信息，尽管这不是必须的。

SQL_TRACE

数据库参数SQL_TRACE可以在实例或者session级别进行设置来进行trace。该参数已经过期，Oracle建议使用DBMS_MONITOR 或者DBMS_SESSION 包来进行session trace.

要修改SQL_TRACE的值使用下面的语句：

ALTERSYSTEM/SESSION SET SQL_TRACE = TRUE/FALSE;

Oracle 11g中sql_trace还是一个event，语法如下：

SQL>oradebug doc event name sql_trace

sql_trace:event for sql trace

Usage

-------

sql_trace

wait < false | true >,

bind < false | true >,

plan_stat < never | first_execution |all_executions | adaptive >,

level <ub4>

所以你可以使用下面的语句来开启包含绑定变量的trace：

altersession set events 'sql_trace bind=true';

下面的语句包含绑定变量和等待事件：

altersession set events 'sql_trace bind=true, wait=true';

你还可以只针对特定的sql进行trace:

altersession set events 'sql_trace [sql: sql_id=g3yc1js3g2689 |sql_id=7ujay4u33g337]bind=true, wait=true';

使用下面的语句查看等待事件的帮助信息：

SQL> oradebug doc event

EventHelp:

***********

...

DBMS_MONITOR

To start tracing:

execsys.dbms_monitor.session_trace_enable(session_id=>18,serial_num=>226,waits=>true, binds=>true);

/*execute your selects to be traced */

To stop tracing:

execsys.dbms_monitor.session_trace_disable(session_id=>18,serial_num=>226);

DBMS_SESSION

DBMS_MONITOR只能被DBA权限的用户调用，任何一个用户都可以调用DBMS_SESSION来对他们自己的session来进行跟踪。 SESSION_TRACE_ENABLE可以被任何用户调用来跟踪他们自己的session.

EXECUTEDBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);

参数TRUE表示等待事件将会显示在跟踪文件中。参数FALSE表示跟踪文件中不会显示绑定变量信息。

SESSION_TRACE_DISABLE过程将会禁用调用它的session的trace跟踪。

EXECUTEDBMS_SESSION.SESSION_TRACE_DISABLE();

Oradebug

你可以使用ORADEBUG工具为其他用户session 或者MTS(Multi-Threaded Server)session开启/关闭 SQL tracing。要为其他session开启trace，Oracle process identifier (PID)或者Operating System processesidentifier (SPID)在v$process中必须是确定的。这是捕捉已经正在运行的进程的SQL trace的高效的方法。它的输出可以用来分析SQL相关的性能问题。

ORADEBUG dump会在user_dump_dest下生成一个跟踪文件，这个跟踪文件可以通过TKRPOF来进行格式化。ORADEBUG是一个在Server Manager line mode(svrmgrl)下可用的一个工具。这个工具在Oraclev7.3及以后是可用的。

NOTE:如果你正在使用的是一个老版本的Oracle(prior to Oracle9),使用’SVRMGRL’ 而不是SQL*PLUS，使用方法如下：

e.g.

>svrmgrl

SVRMGR>connect internal

1、在非MTS环境中使用下面的方法在V$PROCESS中确定PID或者OPID

$qlplus/nolog

SQL>nect/ as sysdba

SQL>select pid, spid, username from v$process;

PID SPID USERNAME

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

8 25807 oracle

在MTS环境中使用下面的方法确定PID和SPID

sqlplus/nolog

SQL>connect / as sysdba

SQL>select pid, spid from v$process p, v$shared_server s

2 where p.addr = s.paddr;

PID SPID

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

14 6976

2、使用ORADEBUG连接到进程

使用Oracle process identifier:

SQL>oradebug setorapid 8

或者使用Operating System process identifier:

SQL>oradebug setospid 25807

3、为session 开启SQL trace

SQL>oradebug unlimit

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

4、取得trace 文件的文件名

SQL>oradebug tracefile_name

/opt/oracle/admin/db92/udump/db92_ora_16921.trc

5、为session 关闭trace 跟踪

SQL>oradebug event 10046 trace name context off

6、使用TKRPOF格式化trace

DBMS_SUPPORT

DBMS_SUPPORT包默认是不安装的。如果你要使用安需要单独安装：

conn / assysdba

@?rdbms\admin\dbmssupp.sql

GRANTexecute ON dbms_support TO uwclass;

CREATEPUBLIC SYNONYM dbms_support FOR dbms_support;

开始跟踪：

execsys.dbms_support.start_trace ;

或者

dbms_support.start_trace(waitsIN BOOLEAN DEFAULT TRUE,binds IN BOOLEAN DEFAULT FALSE);

停止跟踪：

execsys.dbms_support.stop_trace ;

或者

dbms_support.stop_trace_in_session(idIN NUMBER, serial IN NUMBER);

DBMS_SYSTEM

dbms_system.set_sql_trace_in_session

To start tracing:

execdbms_system.set_sql_trace_in_session(18,226,TRUE);

/*execute your selects to be traced */

To stop tracing:

execdbms_system.set_sql_trace_in_session(18,226,FALSE);

dbms_system.set_ev

To start tracing:

execdbms_system.set_ev(18, 226, 10046, 12, '');

To stop tracing:

exec dbms_system.set_ev(18, 226, 10046, 0,'');

dbms_system.SET_BOOL_PARAM_IN_SESSION

To start tracing:

execsys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', TRUE);

/*execute your selects to be traced */

To stop tracing:

execsys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', FALSE);

Logon Trigger

To start tracing:

create orreplace trigger user_logon_trg

afterlogon on database

begin

if USER ='xxxx' then

executeimmediate

'Altersession set events ''10046 trace name context forever, level 8''';

end if;

end;

/* Logina new session as User 'xxxx' and execute your selects to be traced */

To stop tracing: via LogOff Trigger (needsto be created before logging off)

create orreplace trigger user_logoff_trg

beforelogoff on database

begin

if USER ='xxxx' then

executeimmediate

'Altersession set events ''10046 trace name context off''';

end if;

end;

为了能打开跟踪session, 执行trigger的用户需要被显式地授予'altersession' 权限. 例如，

grant alter session to <USERNAME> ;

实例层的跟踪

注意: 在实例层设置跟踪需要非常小心，这是因为整体性能会由于所有session都被跟踪而受到影响。

这个设置将会跟踪在这个参数设置“以后”创建的每个session。已经存在的session不会被跟踪。

系统层的10046跟踪适用于当我们知道问题session会出现，但是不能预先识别它的时候。

在这种情况下，可以打开系统层跟踪一小段时间，当问题被重现以后立即将其关闭，然后从已经生成的trace中查找需要的信息。

用以下命令打开系统层的跟踪：

alter system set events '10046 trace namecontext forever,level 12';

用以下命令关闭在所有session中的10046跟踪：

alter system set events '10046 trace namecontext off';

初始化参数设置

设置以下参数并重新启动实例后，实例上所有的session都会打开跟踪。

event="10046 trace name contextforever,level 12"

移除这个参数并且重启实例，或者使用下面的alter system命令可以关闭跟踪。

alter system set events '10046 trace namecontext off';

通过logontrigger设置跟踪

有的时候当需要跟踪某个特定用户的操作时，可以使用logontrigger来打开跟踪

下面是一个例子:

CREATE OR REPLACE TRIGGER SYS.set_trace

AFTER LOGON ON DATABASE

WHEN (USER like '&USERNAME')

DECLARE

lcommand varchar(200);

BEGIN

EXECUTE IMMEDIATE 'alter session settracefile_identifier=''From_Trigger''';

EXECUTE IMMEDIATE 'alter session setstatistics_level=ALL';

EXECUTE IMMEDIATE 'alter session setmax_dump_file_size=UNLIMITED';

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

END set_trace;

用SQLT收集trace

SQLTXPLAIN的Xecute方法生成的诊断文件中会包含10046 trace。正如XECUTE这个名字所示，SQLT会执行被分析的SQL语句，然后生成一个诊断文件集（包括10046trace）。详见：

Note:215187.1 SQLT (SQLTXPLAIN) - Tool that helpsto diagnose SQL statements performing poorly (Doc ID 215187.1)

用这种方法生成的trace文件会被包含在SQLT输出包中，格式为：

sqlt_s12345_10046_execute.trc

12345是SQLT报告ID。

跟踪pl/sql的资源消耗情况

可以使用PL/SQLProfiler

Note:243755.1 Implementing and Using the PL/SQL Profiler

其他

会话级别:

altersession set events '10046 trace name context forever';

altersession set events '10046 trace name context forever, level 8';

altersession set events '10046 trace name context off';

11g 中也可以使用sql_trace事件:

altersession set events 'sql_trace';

altersession set events 'sql_trace wait=true';

altersession set events 'sql_trace off'';

参数文件中:

event="10046trace name context forever,level 4"

注意这样会为数据库的所有会话开启10046事件。

在 oradebug中使用 (7.3+):

oradebug event 10046 trace name context forever, level 4

11g 中可以使用sql_trace事件:

oradebug event sql_trace bind=true

在 oradbx中使用10046 (pre7.3):

event10046 trace name context forever, level 4

EXAMPLES:

~~~~~~~~~

Wait Information:

nam is what is being waited for

ela is the elapsed time for the operation

In Oracle7 / 8 this is in units of hundredths of a second

In Oracle9i onwards this is in microseconds

p1 is the file number

p2 is the block number

p3 is the number of blocks read by the operation

Waiting for a Full Table Scan:

WAIT #1: nam="db file scattered read" ela= 5 p1=4 p2=1435p3=25

WAIT #1: nam="db file scattered read" ela= 7 p1=4 p2=1461p3=32

WAIT #1: nam="db file scattered read" ela= 7 p1=4 p2=1493p3=32

The first wait indicates a mutliblock read was issued for 25 blocks

starting at file 4 block 1435. This took 5/100th of a second.

The next 2 lines also show multiblock reads of 32 blocks (which is

probably the value of <Parameter:db_file_multiblock_read_count> )

Waiting for an Index Scan:

WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1224p3=1

WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1788p3=1

在index scan 或者单独的数据块访问时你可以看到这种类型的输出。

0 0