Oracle Tracing
AUTOTRACE
Setup (SQL*Plus)
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@utlxplan
CREATE PUBLIC SYNONYM plan_table FOR plan_table;
GRANT ALL ON plan_table TO public;
exit
cd $ORACLE_HOME/sqlplus/admin
sqlplus / as sysdba
@plustrce
GRANT plustrace TO public;
Testing (SQL*Plus)
SET AUTOTRACE traceonly
SELECT SYSDATE FROM dual;
-----------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 || 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |-----------------------------------------------------------------
Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 519 bytes sent via SQL*Net to client 464 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SET AUTOTRACE OFF
Usage
set autotrace on
set autotrace on explain
set autotrace on statistics
set autotrace traceonly
set autotrace off
Shows the execution plan as well as statistics of the statement.
Displays the execution plan only.
Displays the statistics only.
Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
Disables all autotrace
If autotrace is enabled with statistics, then the following statistics are displayed:
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
SQL Trace / Oracle Trace
Enable
See later for Trace Events and their LevelsALTER SYSTEM SET max_dump_file_size = unlimited;
ALTER SYSTEM SET timed_statistics = true;
Version
5
6+
7+
7+
7+
8+
8+
10+
Command
SELECT trace('sql',1) FROM dual;
ALTER SESSION SET sql_trace=true;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';
EXECUTE DBMS_SYSTEM.SET_EV(&sid, &serial, 10046, 12, '');
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(&sid, &serial, true)
EXECUTE DBMS_SUPPORT.START_TRACE
EXECUTE DBMS_SUPPORT.START_TRACE_IN_SESSION(&sid, &serial, true, true);
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(&service, &module, &action, true, true);
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id=>&sid,serial_num=>&serial,binds=>true,waits=>true);
See later for instructions on how to install DBMS_SUPPORTNote
39817.1
62294.1
Trace output will go to your USER_DUMP_DEST location (e.g. /u01/app/oracle/admin/MYSID/udump).
Disable
See later for Trace Events and their LevelsVersion
5
6+
7+
7+
7+
8+
8+
10+
Command
SELECT trace('sql',0) FROM dual;
ALTER SESSION SET sql_trace=false;
ALTER SESSION SET EVENTS '10046 trace name context off';
ALTER SYSTEM SET EVENTS '10046 trace name context off';
EXECUTE DBMS_SYSTEM.SET_EV(&sid, &serial, 10046, 0, '');
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(&sid, &serial, false)
EXECUTE DBMS_SUPPORT.STOP_TRACE
EXECUTE DBMS_SUPPORT.STOP_TRACE_IN_SESSION(&sid, &serial);
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(&service, &module, &action);
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id=>&sid,serial_num=>&serial);
Note
39817.1
62294.1
Enable/Disable using Triggers
Trace Levels
Event 10046
Level
0
1
4
8
12
Description
No tracing
Standard SQL Trace
Standard SQL Trace plus bind variable information
Standard SQL Trace plus timed event information
Standard SQL Trace plus bind variable and timed event information
Event 10257
event="10257 trace name context forever, level 16"
event="10257 trace name context off"
DBMS_SUPPORT
Install
sqlplus / as sysdba
@?/rdbms/admin/dbmssupp.sql
Bibliography
https://support.oracle.com29012.1 TKPROF Usage - Quick Reference33838.1 Determining the execution plan for a distributed query39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output41634.1 TKPROF and Problem Solving61998.1 Introduction to Tuning Oracle7 / Oracle8 / 8i / 9i62160.1 Tracing Sessions in Oracle Using the DBMS_SUPPORT Package62294.1 The DBMS_SUPPORT Package179848.1224270.1 TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046263599.1 Understanding and Troubleshooting Instance Load Balancing (includes PMON trace info)1012416.6 How do you use TKPROF and Trace (With Applications)
http://database.ittoolbox.com/groups/technical-functional/sql-l/sp20618-cannot-find-the-session-identifier-check-plustrace-role-is-enabled-1516953http://database.ittoolbox.com/groups/technical-functional/sql-l/sp20618-cannot-find-the-session-identifier-check-plustrace-role-is-enabled-470402http://www.psoug.org/reference/dbms_support.htmlhttp://www.orafaq.com/wiki/SQL_Tracehttp://www.dbspecialists.com/presentations/wait_events_10g.htmlhttp://tinky2jed.wordpress.com/technical-stuff/oracle-stuff/what-is-the-correct-way-to-trace-a-session-in-oracle/
tkprofhttps://www.orafaq.com/wiki/TKProfhttps://docs.oracle.com/cd/B19306_01/server.102/b14211/sqltrace.htm#i4640