Oracle 10046 events

In many cases, you can use SQL tracking to diagnose the performance of a database and record some information in the trace file for further analysis. In general, we can set SQL tracking by initializing the parameter SQL _trace = true.

We can also set 10046 events for SQL tracking, and set different trace levels to obtain more information than SQL _trace.

Level 0 disables SQL tracing, which is equivalent to SQL _trace = false

Level 1 standard SQL trace, equivalent to SQL _trace = true

Level 4 adds variable binding information based on Level 1.

Level 8 adds wait event information based on Level 1.

Level 12 adds variable binding and wait event information based on Level 1.

10046 events not only track user sessions (the trace file is in user_dump_dest), but also the background process (the trace file is in background_dump_dest ). The size of the trace file depends on four factors: Trace Level, trace duration, session activity level, and max_dump_file_size.

Enable tracking event 10046

1. Global settings

Modify initialization parameters

Event = "10046 trace name context forever, Level 8"

2. Set in the current session

Alter session set events '2017 trace name context forever, level 8 ';

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

3. Set sessions for other users

First, obtain the session ID and serial number of the session to be tracked.

Select Sid, serial #, username from V $ session where username = 'trace _ username ';

Exec dbms_support.start_trace_in_session (SID => 1234, serial # => 56789, Waits => true, binds => true );

Exec dbms_support.stop_trace_in_session (SID => 1234, serial #=> 56789 );

Or

Exec dbms_system.set_ev (1234,567 89, 10046, 8 ,'');

Exec dbms_system.set_ev (1234,567 89, 10046, 0 ,'');

Or

Exec dbms_monitor.session_trace_enable (session_id => 1234, serial_num => 56789, Waits => true, binds => true );

Exec dbms_monitor.session_trace_disable (session_id => 1234, serial_num => 56789 );

You can also use the oradebug tool to set the 10046 event.

First, obtain the OS process ID of the session through V $ process.

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 ('trace _ username ');

Oradebug setospid 12345;

Oradebug unlimit;

Oradebug event 10046 trace name context forever, Level 8;

Oradebug event 10046 trace name context off;

Obtain a trail File

1. Use oradebug

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/Opt/Oracle/product/9.2.0/rdbms/log/uxdb_ora_9183.trc

2. Set the initial parameter tracefile_identifier.

Alter session set tracefile_identifier = 'mytrack ';

In this way, the generated trace file name will contain the words mytrace.

/Opt/Oracle/product/9.2.0/rdbms/log/uxdb_ora_9183_mytrace.trc

3. query through 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;

? /Rdbms/log/uxdb_ora_9183.trc

View the tracking level of the current session

SQL> set serveroutput on

SQL> declare

Event_level number;

Begin

Dbms_system.read_ev (10046, event_level );

Dbms_output.put_line (to_char (event_level ));

End;

/

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/Moodys_Sean/archive/2009/09/10/4537833.aspx