Oracle SQL Trace

I. Sql_trace description

1.1. In the global enable specified in the parameter file (pfile/spfile): Sql_trace =true 1.2. At the current session level, enable tracing for the current session: Alter session set Sql_trace    =true;    End Trace: Alter session set SQL_TRACE=FALSE; 1.3. Track other user processes to track other users ' processes, which can be dbms_system through the system package provided by Oracle.    Set_sql_trace_in_session to complete. Set_sql_trace_in_session program to provide three parameters: Sql> desc dbms_system ...     PROCEDURE set_sql_trace_in_session Argument Name Type in/out Default? -------------------------------------------------------------------SID N                        Umber in serial# number in Sql_trace        BOOLEAN in ... With V$session we can get information such as SID, Serial#, etc.: sql> select Sid,serial#,username 2 from V$session 3 where Usernam               E is not null;         SID serial# USERNAME---------------------------------8 2041 SYS    9 437 SCOTT Enable tracing: exec dbms_system.set_sql_trace_in_session (9,437,true) stop tracking: exec dbms_sy Stem.set_sql_trace_in_session (9,437,false)

Two. 10046 Event description

The 10046 event is an internal event provided by Oracle and is an enhancement to the Sql_trace. 10046 event can be set with the following four levels: 1-enable the standard Sql_trace function equivalent to Sql_trace4-level 1 plus bound value (BIND values ) 8-level 1 + wait Event Trace 12-level 1 + level 4 + level 8 similar to sql_trace,10046 events can be set at the global settings or at the session stage. 2.1. In the global settings in the parameter file add: event= "10046 Trace name Context forever,level 12" This setting takes effect for all processes of all users, including background processes. 2.2 to the current session Set the alter session's way to modify the system permissions required by ALTER SESSIONS: EXECUTE TRACE: sql> alter session SET events ' 10046 Trace name context F    Orever ';        Sql> alter session SET events ' 10046 Trace name Context forever, Level 8 '; End Trace: sql> alter session SET events ' 10046 Trace name context off '; 2.3 to other user session settings via Dbms_system.    Set_ev System Package to implement: Sql> desc dbms_system ...     PROCEDURE Set_ev Argument Name Type in/out Default?          -------------------------------------------------------------------SI Binary_integer    In SE Binary_integer      In EV Binary_integer in LE Binary_integer    In NM VARCHAR2 in ... The parameters si, se from the v$session view, the query gets the session information that needs to be tracked: Select Sid,serial#,username from v$session where username are not nu        ll SID serial# USERNAME--------------------------------------------------8 2041 SYS 9 437 SCOTT execution trace: sql& Gt        exec Dbms_system.set_ev (9,437,10046,8, ' SCOTT '); End trace: sql> exec Dbms_system.set_ev (9,437,10046,0, ' Scitt ');

Three. Parameters related to SQL Trace

Before you open the SQL Trace for 10046 times, set the following parameters first. Timed_statistics whether to collect time-related statistics, if this parameter is false, then the result of SQL Trace is basically not much use, by default this parameter is set to True. The size of the Max_dump_file_sizedump file can generate a lot of information if you do SQL Trace on a busy system, so it's a good idea to set this parameter to unlimited at the session level. 3.1 In the global setting these parameters can be loaded in spfile, so that the system starts automatically when the global settings. 3.2 to the current session set        ALTER session set Timed_statistics=truealter session set        max_dump_file_size=unlimited 3.3 Set Sys.dbms_system for other users session. Set_bool_param_in_session (: SID,: Serial, ' Timed_statistics ', true) SYS. Dbms_system. Set_int_param_in_session (: SID,: Serial, ' max_dump_file_size ', unlimited)    

Four. Get trace files

The trace files generated above are located in the User_dump_dest directory, and the location and file name can be obtained from the following SQL query:                        select D.value| | ' /' | | Lower (RTrim (i.instance, Chr (0))) | | _ora_ ' | | p.spid| | '. TRC ' Trace_file_namefrom (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 = ' Use R_dump_dest ') D; /

Organized from the Internet

Oracle SQL Trace