Many times, performing performance diagnostics for databases to use SQL tracking methods, and record some information in the trace file. Under normal circumstances we can set SQL trace by initializing parameters SQL_TRACE = TRUE.
We can also perform SQL tracking by setting 10046 events, and you can set different tracking levels, more information to get more information using SQL_Trace.
Level 0 disables SQL tracking, equivalent to SQL_TRACE = FALSE
Level 1 Standard SQL Tracking, equivalent to SQL_TRACE = TRUE
Level 4 adds information about binding variables on the basis of Level 1
Level 8 Adds information on the event on the basis of Level 1
Level 12 adds information that binds variables and waiting events on the basis of Level 1
10046 Events can not only track the user session (the Trace file is located in user_dump_dest), or track the Background process (the Trace file is located on background_dump_dest). The size of the TRACE file determines 4 factors: tracking level, tracking time, session activity level and max_dump_file_size parameter.
Enable tracking event 10046
In the global setting
Modify initialization parameters
Event = "10046 TRACE NAME CONTEXT Forever, Level 8"
2. Setting in the current session
Alter Session Set Events '10046 Trace Name Context Forever, Level 8';
Alter Session Set Events '10046 Trace Name Context Off';
3. Setting up other users session
First get the session ID and Serial Number to track the session to track
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, 56789, 10046, 8, ');
EXEC DBMS_SYSTEM.SET_EV (1234, 56789, 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 set up 10046 events by using an ORADEBUG tool.
First get the SESSION OS Process ID 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;
Get tracking files
1. Using ORADEBUG
SQL> ORADebug setMypid
Statement processed.
SQL> ORADebug tracefile_name
/ OPT / ORACLE / PRODUCT /
9.2.0 / rdbms / log / uxdb_ora_9183.tc
2. Set the initial parameter TRACEFILE_IDENTIFIER
ALTER session set tracefile_identifier = 'mytrace';
This will contain a MyTrace word in the generated TRACE file name.
/ OPT / ORACLE / PRODUCT /
9.2.0 / rdbms / log / uxdb_ora_9183_mytrace.trc
3. Inquiry 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 $ mystatm, 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 current session tracking level
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;
/
