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
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.
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;
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
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');
-- 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');
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';
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
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
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.