Tag Archives: sql trace

Trace

How to Enable Trace

1. Trace levels
2. Trace own session
3. Trace other’s session
4. Service Level Trace
5. Module Level Trace
6. Database Level Trace
7. How to identify Trace file
8. trcsess Utility
9. TKPROF


Trace levels.

Level 0 - No trace. Just like switching sql_trace off.
Level 2 - The equivalent of regular sql_trace.
Level 4 - The same as level 2, but with the addition of bind variable values.
Level 8 - The same as level 2, but with the addition of wait events.
Level 12 - The same as level 2, but with the addition of both bind variable values and wait events.


Trace own session

set timing on timi on
ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET STATISTICS_LEVEL=ALL;
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’MY_TRACE’;

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- Execute the queries or operations to be traced here --
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

OR

SQL> EXEC DBMS_MONITOR.session_trace_enable; (OR)
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_MONITOR.session_trace_disable;

OR

SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();

OR

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

OR

SQL> ALTER SESSION SET sql_trace=TRUE;
-- Execute the queries or operations to be traced here --
SQL> ALTER SESSION SET sql_trace=FALSE;

OR

SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SUPPORT.stop_trace;

Note: Need to install before use 
SQL> connect / AS SYSDBA
SQL> @?\rdbms\admin\dbmssupp.sql
SQL> GRANT execute ON dbms_support TO schema_owner;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support; 


Trace other’s session

set timing on timi on
ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET STATISTICS_LEVEL=ALL;
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’MY_TRACE’;

-- Enable 10046 Trace on other's session

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>12, nm=>' ');
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

OR

SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234); (OR)
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);

OR

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

OR

SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);

OR

Tracing a process after it has started. Please find out PID, SPID using below query 

select p.PID,p.SPID,s.SID 
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = '&SESSION_ID'
/

SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid) 

Using SPID (os pid)

connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
-- Execute the queries or operations to be traced here --
oradebug tracefile_name; -- display the current trace file.
oradebug event 10046 trace name context off

OR

using PID (ora pid)

connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
-- Execute the queries or operations to be traced here --
oradebug tracefile_name;  -- display the current trace file.
oradebug event 10046 trace name context off


Service Level Trace

SELECT sid, serial#, client_identifier, service_name, action, module FROM V$SESSION where username=’&USERNAME’;
select * from DBA_ENABLED_TRACES;
select trace_type, primary_id, QUALIFIER_ID1, waits, binds from DBA_ENABLED_TRACES;
select * from sys.wri$_tracing_enabled;

-- Level 12 Trace on service_name (waits=>true,binds=>true)

SQL> EXEC dbms_monitor.serv_mod_act_trace_enable (service_name=>'orcl',waits=>true,binds=>true);
	-- Execute the queries or operations to be traced here --
SQL> EXEC dbms_monitor.serv_mod_act_trace_disable (service_name=>'orcl');


Module Level Trace

-- Level 12 Trace on Module (waits=>true,binds=>true)

SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'orcl', module_name=>'TOAD 9.5.0.31', waits => TRUE, binds => TRUE);
	-- Execute the queries or operations to be traced here --
SQL> exec dbms_monitor.serv_mod_act_trace_disable(service_name=>'orcl', module_name=>'TOAD 9.5.0.31');


Database Level Trace

For all sessions in the database

NOTE: Please be cautious when setting system wide, as this will impact performance due to every session being traced.
Warning: Setting the 10046 event for the entire database is not recommended. This can cause severe performance issues.

Existing sessions will not be traced. 
 
EXEC dbms_monitor.DATABASE_TRACE_ENABLE(TRUE,TRUE);
EXEC dbms_monitor.DATABASE_TRACE_DISABLE();

alter system set events '10046 trace name context forever,level 12';
alter system set events '10046 trace name context off';


How to identify Trace file

Trace file named with spid under user_dump_dest location
-- To find SPID
select p.PID,p.SPID,s.SID 
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = '&SESSION_ID'
/

eg: instancename_ora_spid.trc i.e orcl_ora_4393.trc

show parameter user_dump_dest
cd user_dump_dest location
ls -ltr *ora_spid*.trc
eg: ls -ltr *ora_4393*.trc


trcsess Utility

When using the DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure, tracing
information is present in multiple trace files and you must use the trcsess tool to collect it into a single file.
The trcsess utility is useful for consolidating the tracing of a particular session or service for performance or debugging purposes.

trcsess [output=output_file_name]
[session=session_id]
[clientid=client_identifier]
[service=service_name]
[action=action_name]
[module=module_name]
[]

eg: 
cd user_dump_dest
trcsess output=one_big.trc service=orcl *.trc
ls -ltr one_big.trc


TKPROF

The tkprof utility parses SQL trace files to produce more readable output.
tkprof does not report COMMITs and ROLLBACKs that are recorded in the trace file.
Note: Set the TIMED_STATISTICS parameter to TRUE when tracing sessions because no time-based comparisons can be made without this. TRUE is the default value with Oracle Database 11g.

eg: 
tkprof one_big.trc output=one_big.txt sys=no

[oracle@rac1 trace]$ tkprof one_big.trc output=one_big.txt sys=no

TKPROF: Release 11.2.0.3.0 - Development on Sun Sep 11 16:50:56 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


[oracle@rac1 trace]$ ls -ltr one_big.txt

Reference:

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)
https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm#CFAHBEAB
http://www.juliandyke.com/Diagnostics/Packages/DBMS_MONITOR/SERV_MOD_ACT_TRACE_ENABLE.php
https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.