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;

Execution Plan----------------------------------------------------------Plan hash value: 1546270724
-----------------------------------------------------------------| 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:

SQL Trace / Oracle Trace

Enable

See later for Trace Events and their Levels

ALTER 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_SUPPORT

Note





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 Levels

Version

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

CREATE OR REPLACE TRIGGER logon_start_trace    AFTER LOGON ON DATABASEDECLARE    li_sid    NUMBER(10);    li_serial NUMBER(10);BEGIN    IF user = 'MYUSER' THEN        --http://www.orafaq.com/wiki/SQL_Trace        SELECT sid, serial#          INTO li_sid, li_serial         FROM sys.v$session      --WHERE username = user;        WHERE audsid = sys_context('USERENV', 'SESSIONID');        BEGIN            dbms_system.set_sql_trace_in_session(li_sid, li_serial, true);        END;    END IF;END;
CREATE OR REPLACE TRIGGER logoff_stop_trace    BEFORE LOGOFF ON DATABASEDECLARE    li_sid number(10);    li_serial number(10);BEGIN    IF user = 'MYUSER' THEN        --http://www.orafaq.com/wiki/SQL_Trace        SELECT sid, serial#          INTO li_sid, li_serial         FROM sys.v$session      --WHERE username = user;        WHERE audsid = sys_context('USERENV', 'SESSIONID');        BEGIN            dbms_system.set_sql_trace_in_session(li_sid, li_serial, false);        END;    END IF;END;

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://blog.ora-600.pl/2021/01/08/what-to-do-with-5gb-of-trace-files/https://github.com/ora600pl/tracefile_parsers/tree/master/parse_trace_owi
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
1.0.0 09-AUG-2005 Created1.1.0 23-NOV-2006 Expanded1.1.1 24-DEC-2007 Added SET AUTOTRACE ON2.0.0 11-APR-2008 Converted to HTML2.0.1 29-APR-2008 Added WAIT reference link2.0.2 01-MAY-2008 Expanded Bibliography. Added additional hyperlinks2.1.0 26-MAY-2008 Added PMON Trace Information2.2.0 15-OCT-2008 Added enable/disable using triggers2.3.0 21-DEC-2010 Added AUTOTRACE section2.4.0 13-FEB-2012 Added extra autotrace options2.4.1 29-NOV-2012 Added extra DBMS_MONITOR information3.0.0 24-MAR-2025 Converted to Google Docs