Effective Tracing of Oracle Sessions & Problematic SQL!!

 Author: Tim Mladen

Oracle provides a number of options that can help you trace Oracle sessions and find problematic SQL statements. In this article we will discuss a number of such options that you will find useful for tuning your Oracle application.

Oracle DBMS_MONITOR Package:

Oracle 10g has blessed us with DBMS_MONITOR package that allow us to trace and gather statistics of Oracle sessions via a PL/SQL interface. You can control additional tracing and statistics gathering with the help of Oracle DBMS_MONITOR package.

Below are the useful procedures of Oracle DBMS_MONITOR package that can be used to enable and disable additional statistics aggregation.

You can use below commands to enable and disable the Oracle session tracing based on a client id.

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE ('client id');
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE ('client id');

Oracle DBMS_TRACE Package:

Oracle DBMS_TRACE package allows us to trace the actions of PL/SQL programs where the scope and volume of the tracing is user configurable.

Oracle Data Dictionary Views:

You can trace Oracle sessions and resource intensive SQL by using Oracle data dictionary views such as V$SESSION, V$SQL, V$PROCESS.

Oracle AWR Tables:

Oracle AWR tables provides many useful information about time series execution plans for SQL statements. You can use below AWR tables for SQL tuning.

• DBA_HIST_SQLSTAT
• DBA_HIST_SQL_PLAN
• DBA_HIST_SQL_SUMMARY
• DBA_HIST_SQL_WORKAREA
• DBA_HIST_SQL_WORKAREA_HISTOGRAM

Oracle ASH Tables:

Oracle 10g Active Session History ( ASH ) stores the history of a recent session activity and helps you analyze system performance and trace consumption of individual sessions. You can analyze ASH data with the help of V$ACTIVE_SESSION_HISTORY view and the WRH$ACTIVE_ SESSION _HISTORY tables.

Oracle TKPROF:

Oracle TKPROF has been around for a long time. If your database or a particular session is having problem then you can use Oracle TKPROF and session tracing. This can help you determine exactly what is happening on the database. SQL trace must be enabled in order to use TKPROF.

ALTER SESSION SET SQL_TRACE=TRUE;

SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SESSION_ID, SERIAL#, TRUE);

Oracle Trace Utilities :

Oracle provides a number of trace files that can help you display specific consumption of specific SQL statement execution. Oracle 10046 trace file and Oracle 10053 trace file are important tracing utilities that will display all details of SQL and session execution. However you should be aware of the fact that these tracing utilities exhibits high overhead and therefore must be used with great caution. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Performing Oracle Automated Tablespace Point-in-Time Recovery!!
   Tricky Password Features in Oracle 11g!!
   Effective Tips on Oracle Flashback!!
   Beware of Oracle Automatic Memory Management Pitfalls!!