There are several ways to generate SQL Trace: 1. Parameter setting: Very traditional method. System level: specified in the parameter file: Sql_trace=true or sql> alter system set sql_trace=true; Note: Sql_trace is enabled at the system level, resulting in a large number of trace files that can easily run out of disk space. Therefore, the session level is generally set and closed in a timely manner. Session level:sql> alter session set Sql_trace=true; Sql> Execute sqlsql> alter session set Sql_trace=false; When tracing is enabled, the trace file is saved under User_dump_dest you can use the following query to locate the generated trace file sql> Select 2 d.value| | ' /' | | Lower (RTrim (i.instance, 3 chr (0))) | | ' _ora_ ' | | p.spid| | '. TRC ' trace_file_name 4 from (select p.spid 5 from v$mystat m, 6 v$session s,v$process p 7 where m.statistic# = 1 and S.sid = M.sid and p.addr = s.paddr) p, 8 (select T.instance from V$thread t,v$parameter v 9 where v.name = ' thread ' and 10 (v.value = 0 or t.thread# = To_numb ER (v.value))) i, 11 (select value from v$parameter 12 where name = ' user_dump_dest ') d 13 &nbs P;/ trace_file_name--------------------------------------------------------------------------------/oracle/admin/rlzy/udump/rlzy_ora_721532.trc can also assign identifiers to the trace files to be generated, making it easier to find files sql> Alter session set tracefile_identifier= ' Jingyong '; 2, using 10046 event: 10046 event Level: Lv0 -disable sql_trace, equivalent to sql_trace= FALSELV1 -enable the standard Sql_trace function, equivalent to SQL_TRACE=TRUELV4 -level 1 + bound variable value (bind values) Lv8 -Level 1 + wait Event trace (WA Its) Lv12-level 1 + level 4 + level 8 Global Settings: Parameter file specified: event= "10046 Trace name context forever,level 12" or sql> alter System set Events ' 10046 Trace name Context forever, Level 12 '; Sql> alter system set events ' 10046 Trace name context off '; Note: Sql_trace is enabled at the system level, generates a large number of trace files and can easily run out of disk space, so the session level is generally set and shut down in time. Current session settings:sql> alter session set events ' 10046 Trace name Context forever, Level 12 '; Sql> executes sqlsql> alter session set events ' 10046 Trace name context off '; 3, dbms_session package: Only the current session can be traced and the session cannot be specified. Trace current session:sql> exec Dbms_session.set_sql_trace (true); Sql> Execute sqlsql> exec dbms_session.set_sql_trace (false);d bms_session.set_sql_tRace equivalent to alter session set Sql_trace, from the generated trace file can be explicitly seen in the ALTER session set SQL_TRACE statement. Using the dbms_session.session_trace_enable process, you can see not only the wait event information but also the binding variable information, equivalent to the ALTER session set events ' 10046 Trace name context Forever, Level 12 '; statement from the generated trace file can be confirmed. sql> exec dbms_session.session_trace_enable (waits=>true,binds=>true); Sql> executes sqlsql> exec dbms_session.session_trace_enable (); 4, Dbms_support Package: This method should not be used for unofficial support. The system does not install this package by default, you can manually execute the $oracle_home/rdbms/admin/bmssupp.sql script to create the package to track the current session:sql> exec dbms_support.start_tracesql> Perform sqlsql> exec dbms_support.stop_trace trace Other sessions: Wait for event + bound variable, equivalent to level 12 of 10046 event. Sql> Select Sid,serial#,username from V$session where ...; sql> exec dbms_support.start_trace_in_session (sid=>sid,serial=>serial#,waits=>true,binds=>true); sql> exec dbms_support.stop_trace_in_session (sid=>sid,serial=>serial#); 5, Dbms_ System Package: Track Other sessions: Use Dbms_system.set_ev to set 10046 event sql> Select Sid,serial#,username from V$session where ...; Sql> EXEC DBms_system.set_ev (sid,serial#,10046,12, "); sql> exec Dbms_system.set_ev (sid,serial#,10046,0, "), but tested using a level 8,12 trace in 10g does not produce wait event information in the trace file 6, Dbms_ Monitor Package: 10g is available, very powerful. Can be tracked at the module level, the action level, the client level, the database level, and the session level. Oracle official support. Track current session:sql> exec dbms_monitor.session_trace_enable; Sql> executes sqlsql> exec dbms_monitor.session_trace_disable; tracks other sessions:sql> exec dbms_monitor.session_trace_enable ( Session_id=>sid,serial_num=>serial#,waits=>true,binds=>true); sql> exec dbms_monitor.session_trace_disable (session_id=>sid,serial_num=>serial#); 7, Oradebug This is a sqlplus tool that needs to provide Ospid or Oracle PID. Track current session:sql> Oradebug Setmypid; Statement processed. Sql> Oradebug Unlimit; Statement processed. Sql> oradebug Event 10046 Trace name context Forever,level 12; Statement processed. Sql> Execute sqlsql> oradebug tracefile_namesql> oradebug event 10046 trace name context off; Statement processed. Track other sessions:sql> select Spid,pid2 from v$process 2 where addr in (select Paddr fromv$session where sid= (select distinct SID from V$mystat)); SPID pid----------------------1457 &NB Sp 313sql> oradebug setospid 1457; Statement processed. or sql> oradebug setorapid 313; Statement processed. Sql> Oradebug Unlimit; Statement processed. Sql> oradebug Event 10046 Trace name context Forever,level 12; Statement processed. sql> oradebug tracefile_namesql> oradebug event 10046 trace name context off; Statement processed.
