Many people need to understand how to
set and use the 10053 trace event (SQL trace) to get detailed execution
information about a SQL statement.
The following text is an excerpt from the bestselling book
"Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim
Hall, Oracle ACE of the year, 2006.
The command to dump the optimizer statistics whenever a SQL
statement is parsed can be done at many levels:
ALTER
SESSION SET sql_trace=TRUE;
EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
In Oracle 10g we
see new ways to enable SQL tracing:
SQL> EXEC DBMS_MONITOR.session_trace_enable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
SQL> EXEC
DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234,
waits=>TRUE,
binds=>FALSE);
SQL> EXEC
DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall',
waits=>TRUE,
binds=>FALSE);
SQL> EXEC
DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g',
module_name=>'test_api', action_name=>'running');
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g',
module_name=>'test_api', action_name=>'running', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_disable(service_name=>'db10g',
module_name=>'test_api', action_name=>'running');
Note that the 10053 trace event output files can get huge,
especially for SQL statements that touch thousands of rows. Here are
step-by-step details for using the 10053 SQL trace event:
Alternatives to SQL trace with
10053
Oracle has an more detailed script
alternative to the 10053 trace called
SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for one
SQL statement.
and include the URL for the page.
