About SQL Tracing methods

Oracle SQL Tracing method:
1, Sql_trace
Open trace: Alter session set Sql_trace=true;
Mark the trace file: Alter session set tracefile_identifier= ' Look_for_me ';
Stop tracking: Alter session set SQL_TRACE=FALSE;
The last generated trace file can be viewed using the Tkprof tool generation.

2, 10046 tracking code
Tracking Sector Description:
Level 0 Disables SQL tracing, equivalent to Sql_trace=false
Level 1 standard SQL Trace, equivalent to Sql_trace=true
Level 4 increases the information on the binding variable based
Level 8 adds information on the wait event based on the 1
Level 12 adds information about binding variables and waiting events on the basis of 1

To turn on tracing:
GRANT ALTER SESSION to LTTFM; --Must have ALTER session permission
Alter session SET Events ' 10046 Trace name context forever,level 12 ';--Current user settings
or insert the following line in the Init.ora file: event = 10046 Trace name context Forever,level 12;--for global settings

To turn off tracing:
Alter session SET Events ' 10046 Trace name context off ';--Turn off user tracking

Note: Sql_trace and 10046 set code tracking can only be used for this session or the system level for session tracking, the specific setting of a non-session tracking needs to use Oradebug or
Dbms_system.set_ev or dbms_monitor.session_trace_enable.

3. Using Oradebug
--Use the following statement to find the SPID to track
SELECT * from V$session a WHERE audsid = Userenv (' SessionID '); --Query the current SessionID
Select S.username,
S.osuser,
S.sid,
S.PADDR,
S.process,
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 (' Ltwebgis ')
and s.sid = 145;

--Set the tracking process ID.
Sql> Connect/as SYSDBA
sql> oradebug Setospid 5672; This statement tracks other sessions (5672 is a v$process spid).

If you follow this session, perform Oradebug setmypid.
Or
Sql> Connect/as SYSDBA
sql> Oradebug setorapid 15--Input of 15 for v$process PID

--Set trace file size Unlimited
Sql> Oradebug Unlimit;
--Set trace, Level 8
Sql> oradebug Event 10046 Trace name context Forever,level 8;
Processed statements

--Turn off tracking
Sql> oradebug Event 10046 trace name context off;


When you execute the statement (sql> oradebug setospid 5672;), the Prompt "ORA-01031: Insufficient Permissions",
Cause: Oradebug is a SYSDBA command (the general user does not have sufficient prompt permission) and is a sqlplus specific command that cannot be executed in the Plsql tool (otherwise, invalid SQL is not indicated).
You can use the Oradebug help command to view the Oradebug tool description.


4, Dbms_system (must be executed with SYS user)
exec Dbms_system.set_ev (si = 159,se = 254,ev = 10046,le =>8, NM = ");--the session ID and other parameters must be set correctly, otherwise trace cannot be generated
SQL statements can be used to view trace file locations after build.

--Stop tracking
exec Dbms_system.set_ev (si = 159,se = 254,ev = 10046,le =>0, NM = ");

5, Dbms_monitor
exec dbms_monitor.session_trace_enable (session_id = 136,serial_num = 4,waits = True,binds = True);
exec dbms_monitor.session_trace_disable (session_id = 136,serial_num = 4);
If session_id is not set or is set to NULL, the current session is tracked

6, Dbms_support
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);
Dbms_support by default, the system does not install this package. If you need to use them, you need to set them separately. Should exist under the $oracle_home/rdbms/admin/directory
DBMSSUPP.SQL,PRVTSUPP.PLB These two files, execute these two files before you can use, if other users want to use, need to do the appropriate authorization, and create synonyms.

Note: 3,4,5,6 These tracking methods are performed by the user of SYS and can be traced to any session.

To get the trace file:
1) oradebug get trace file
--use Oradebug to get this session trace file location
Sql> Oradebug Setmypid
Sql> Oradebug Tracefile_name

--Get any session trace file location
sql> Oradebug setospid 5392
Processed statements
Sql> Oradebug Tracefile_name
D:\oracle\product\10.2.0\admin\fgisdb\udump\fgisdb_ora_5600.trc


2) SQL get trace file
--sql View current session trace file location
SELECT D.value | | ' \ ' | | Lower (RTrim (i.instance_name, Chr (0))) | | ' _ora_ ' | |
P.spid | | '. TRC ' Trace_file_name
From (SELECT p.spid
From V$mystat m, v$session s, v$process p
WHERE m.statistic# = 1
and S.sid = M.sid
and p.addr = s.paddr) p,
(SELECT instance_name from v$instance) I,
(SELECT VALUE from v$parameter WHERE NAME = ' user_dump_dest ') D;

--The following SQL is the session trace file for the query input spid
Select D.value | | ' \ ' | | Lower (RTrim (i.instance_name, Chr (0))) | | ' _ora_ ' | |
&spid | | '. TRC ' Trace_file_name
From (SELECT instance_name from v$instance) I,
(select value from v$parameter where name = ' user_dump_dest ') D;

View trace Levels
1) View the tracking level of the current session (must be performed under SYS user)
Declare
Event_level number;
Begin
Dbms_system.read_ev (10046,event_level);
Dbms_output.put_line (To_char (event_level));
End

2) Execute the following statement to view the trace level of the trace event
sql> oradebug setospid spid--Specify the SPID to view the trace level first
Sql> Oradebug Eventdump Session
10046 Trace name CONTEXT level 8, forever

Note: If a trace session is turned on, but the database is closed after that, the trace is automatically closed.

Transferred from: http://www.cnblogs.com/lanzi/archive/2012/07/31/2616606.html

About SQL Tracing methods