Database Reference
In-Depth Information
The
dbms_session
package contains two procedures,
session_trace_enable
and
session_trace_disable
, that
have the same functionality as those with the same names but provided by
dbms_monitor
. The only difference is that
the ones in
dbms_session
can only enable and disable SQL trace for the session you're connected with. As a result,
any user having the
ALTER SESSION
privilege can use them.
The following example illustrates how to enable and disable SQL trace with
dbms_session
. Notice that the
v$session
view provides output indicating that SQL trace has been enabled:
SQL> BEGIN
2 dbms_session.session_trace_enable(waits => TRUE,
3 binds => TRUE,
4 plan_stat => 'all_executions');
5 END;
6 /
SQL> SELECT sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats
2 FROM v$session
3 WHERE sid = sys_context('userenv','sid');
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS SQL_TRACE_PLAN_STATS
--------- --------------- --------------- --------------------
ENABLED TRUE TRUE ALL EXEC
SQL> BEGIN
2 dbms_session.session_trace_disable;
3 END;
4 /
Triggering SQL Trace
In the previous sections, you saw different methods of enabling and disabling SQL trace. In the simplest case, you
manually execute the shown SQL statements or PL/SQL calls in SQL*Plus. Sometimes, however, it's necessary to
automatically trigger SQL trace.
Automatically
here means that code must be added somewhere.
The simplest approach is to create a logon trigger at the database level. To avoid enabling SQL trace for all users,
I usually suggest creating a role (named
sql_trace
in the following example) and temporarily granting it only to the
user utilized for the measurement. The following example is an excerpt of the
sql_trace_trigger.sql
script:
CREATE ROLE sql_trace;
CREATE OR REPLACE TRIGGER enable_sql_trace AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('SQL_TRACE'))
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
dbms_session.session_trace_enable;
END IF;
END;
Naturally, it's also possible to define the trigger for a single schema or perform other checks based, for example,
