10046 事件补充

Trace level:

* Level 0 Tracing is disabled. This is the same as setting SQL_TRACE = FALSE.

* Level 1 Standard SQL trace information (SQL_TRACE = TRUE). This is the default level.

* Level 4 SQL trace information plus bind variable values.

* Level 8 SQL trace information plus wait event information.

* Level 12 SQL trace information, wait event information, and bind variable values.

追踪用户会话时,追踪文件会记录在由参数USER_DUMP_DEST指定的目录中;追踪后台进程时,追踪文件会记录在由参数BACKGROUND_DUMP_DEST指定的目录中.

2.5.1. 启动trace event 10046

INSTANCE级:更改init.ora,添加EVENT参数后重启,不建议！

# This enables the trace event 10046 at level 8 for the instance.

# Restart the instance after this change is made to the init.ora file.

EVENT = “10046 trace name context forever, level 8″

推荐使用Session级的trace event 10046

2.5.2. 追踪自身会话

方法1

alter session set timed_statistics= true;

alter session set max_dump_file_size= unlimited;

— To enable the trace event 10046 in Oracle 7.3 onwards

alter session set events ‘10046 trace name context forever, level 8′;

— Run your SQL script. or program to trace wait event information

— To turn off the tracing:

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

方法2

$sqlplus ‘/ as sysdba’

SQL>start $ORACLE_HOME/rdbms/admin/dbmssupp.sql

Package created.

Package body created.

SQL> grant execute on DBMS_SUPPORT to username;

Grant succeeded.

SQL> connect username/pwd

SQL> — To include Wait Event data with SQL trace (default option)

SQL>exec sys.dbms_support.start_trace;

PL/SQL procedure successfully completed.

SQL> — To include Bind variable values, Wait Event data with SQL trace

SQL>exec sys.dbms_support.start_trace(waits => TRUE, binds=> TRUE)

PL/SQL procedure successfully completed.

SQL> — Run your SQL script. or program to trace wait event information

SQL> — To turn off the tracing:

SQL>exec sys.dbms_support.stop_trace;

PL/SQL procedure successfully completed.

2.5.3. 追踪他人的会话

先调整参数

— Set TIME_STATISTICS to TRUE for SID 1234, Serial# 56789

execsys.dbms_system.set_bool_param_in_session( -

sid => 1234, -

serial# => 56789, -

parnam => ‘TIMED_STATISTICS’, -

bval => true);

— Set MAX_DUMP_FILE_SIZE to 2147483647

— for SID 1234, Serial# 56789

execsys.dbms_system.set_int_param_in_session( -

sid => 1234, -

serial# => 56789, -

parnam => ‘MAX_DUMP_FILE_SIZE’, -

intval => 2147483647);

** 方法1:Use the DBMS_SUPPORT package procedures:

— Enable ‘level 12′ trace in session 1234 with serial# 56789

exec dbms_support.start_trace_in_session( -

sid => 1234, -

serial# => 56789, -

waits => true, -

binds => true);

— Let the session execute SQL script. or

— program for some amount of time

— To turn off the tracing:

exec dbms_support.stop_trace_in_session( -

sid => 1234, -

serial# => 56789);

** 方法2:Use the DBMS_SYSTEM package procedure(oracle不建议此用法)

— Enable trace at level 8 for session 1234 with serial# 56789

exec dbms_system.set_ev( 1234, 56789, 10046, 8, ”);

— Let the session execute SQL script. or

— program for some amount of time

— To turn off the tracing:

exec dbms_system.set_ev( 1234, 56789, 10046, 0, ”);

** 方法3:Use the oradebug facility.

You need to know the session’s OS process ID (SPID) or Oracle process ID (PID). You can look them up in the V$PROCESS view. Assuming you know the name of the user you want to trace:

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’);

Now use SQL*Plus to connect as sysdba and issue following commands:

alter system set timed_statistics = true;

oradebug setospid 12345;

— 12345 is the OS process id for the session

oradebug unlimit;

oradebug event 10046 trace name context forever, level 8;

— Let the session execute SQL script.

— or program for some amount of time

— To turn off the tracing:

oradebug event 10046 trace name context off;

10g you can use DBMS_MONITOR package procedures to enable tracing based on the SID, service name, module, or action. The action-based tracing empowers a DBA to trace a specific business function. There is a little catch to this: the procedure requires that the DBA know the module and action names.

** 方法4:Use the DBMS_MONITOR package to enable tracing for session 1234 and serial# 56789 as shown below:

这个方法和dbms_support很相像,在10g下,建议用dbms_monitor

exec dbms_monitor.session_trace_enable( -

session_id => 1234, -

serial_num => 56789, -

waits => true, -

binds => true);

— Let the session execute SQL script. or

— program for some amount of time

— To turn off the tracing:

exec dbms_monitor.session_trace_disable( -

session_id => 1234, -

serial_num => 56789);

** 方法5:Use the DBMS_MONITOR package for service, module, and action-based tracing:

— Enable Level 12 trace for known Service,

— Module and Action

exec dbms_monitor.serv_mod_act_trace_enable( -

service_name => ‘APPS1′, -

module_name => ‘GLEDGER’, -

action_name => ‘DEBIT_ENTRY’, -

waits => true, -

binds => true, -

instance_name => null);

— Let the session execute SQL script. or

— program for some amount of time

— To turn off the tracing:

exec dbms_monitor.serv_mod_act_trace_disable( -

service_name => ‘APPS1′, -

module_name => ‘GLEDGER’, -

action_name => ‘DEBIT_ENTRY’);

使用以上方法获取的trace文件在USER_DUMP_FILE目录下,可以使用以下方式来为trace文件添加前缀:

alter session set tracefile_identifier = ‘MyTrace';

如果是使用oradebug来获取trace文件的,还可以通过以下方式知道trace文件名:

SQL> oradebug setmypid

Statement processed.

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

Statement processed.

SQL> oradebug tracefile_name

d:\oracle\admin\or92\udump\or92_ora_171.trc

来自为知笔记(Wiz)