Using Trace Event 10046

Many times, performance diagnostics on a database can be done using SQL tracking, and some information is then parsed in the trace file. In general, we can set up the SQL Trace by initializing the parameter sql_trace=true.















We can also set up 10046 events for SQL tracing, and we can set different levels of tracking to get more information than using Sql_trace.















Level 0 Deactivate SQL Trace, equivalent to Sql_trace=false







Level 1 standard SQL Trace, equivalent to Sql_trace=true







Level 4 increases the information of the binding variable on the basis 1







Level 8 increases information on wait events on the basis of 1







Level 12 increases the information of the binding variable and the wait event on the basis of 1















The 10046 event can track not only the user session (the trace file is located in User_dump_dest), but also the background process (the trace file is located in Background_dump_dest). The size of the trace file is determined by 4 factors: the tracking level, the length of the trace, the activity level of the session, and the max_dump_file_size parameters.















Enable Trace Event 10046















1. In the global settings















Modifying initialization parameters







EVENT = "10046 Trace name Context forever, Level 8"















2. In the current session settings















Alter session SET Events ' 10046 Trace name Context forever, Level 8 ';







Alter session SET Events ' 10046 Trace name context off ';















3. Session settings for other users















Get the session ID and serial number of the session you want to track first







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 the 10046 event by using the Oradebug tool















First, the session's OS process ID is obtained 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 forever, Level 8;







Oradebug Event 10046 Trace name context off;















Get trace file















1. Use of 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 = ' Mytrace ';







This will contain the Mytrace word in the generated trace file name







/opt/oracle/product/9.2.0/rdbms/log/uxdb_ora_9183_mytrace.trc















3. Through SQL query



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 for 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



/