Oracle Tracing

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:

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 Levels

ALTER SYSTEM SET max_dump_file_size = unlimited;

ALTER SYSTEM SET timed_statistics = true;

Version

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

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 (SQL Trace)

Level

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 (PMON Trace)

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

Page updated

Google Sites

Report abuse