Enabling Tracing in Your Application

If you own the source code to theapplication you wish to trace, it might be feasible for you to modify the codedirectly to enable SQL Trace. This can be advantageous because you can enableSQL Trace at precisely the point in your application where you have a doubt.This can greatly narrow the scope of your tracing exercise, allowing you tofocus efforts on the bottleneck itself.

Depending on the tools used to build yourapplication, you may be able to enable SQL Trace with the simple command:

ALTER SESSION SET SQL_TRACE = TRUE;

If you need to enable SQL Trace from withina PL/SQL block, you can call:

dbms_session.set_sql_trace (TRUE);

SQL Trace output is most valuable when timedstatistics are enabled. You can instantly enable timed statistics for allsessions by logging on to the database as a DBA and executing the command:

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

If you are concerned about the overhead thiscauses, you may instead modify the application code to enable timed statisticsfor the current session instead of the entire instance. You can do this withthe command:

ALTER SESSION SET TIMED_STATISTICS = TRUE;

To enable timed statistics from within aPL/SQL block, you must use the dbms_sql package to build and execute a dynamicSQL statement:

DECLARE
  c INTEGER; i INTEGER;
BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse (c, 'ALTER SESSION SET TIMED_STATISTICS = TRUE',
                  dbms_sql.native);
  i := dbms_sql.execute (c);
  dbms_sql.close_cursor (c);
END;