Tracing SQL in Oracle Database 10g

New tools help you better understand the performance of your applications.

In a busy production environment with many active users, tracing a SQL session is time-consuming and complicated, because processing SQL statements in any multitier system that uses a connection pool can span multiple processes, or even different instances.

With Oracle Database 10g, Oracle rationalizes SQL tracing through a new built-in package, DBMS_MONITOR, which encompasses the functionality of previously undocumented trace tools, such as the DBMS_SUPPORT package. Now you can easily trace any user's session from beginning to end—from client machine to middle tier to back end—and generate trace files based on specific client ID, module, or action.

In addition, Oracle Database 10g includes a new utility, trcsess, that lets you selectively extract trace data from numerous trace files and save them into a single file, based on criteria such as session ID or module name. This utility is especially useful in a shared server configuration, since a dispatcher may route each user request to a different shared server process, resulting in multiple trace files for any given session. Rather than digging through numerous trace files, Oracle Database 10g's trcsess lets you obtain consolidated trace information pertaining to a single user session.

Getting Started

As with prior Oracle database releases, trace files are output to the directory specified by the user_dump_dest parameter of the server's initialization file (or spfile). The default location depends on the operating system; for example, for Microsoft Windows platforms using DBCA, the default is $ORACLE_BASE\instance_name\admin\udump, where instance_ name is the name of the Oracle instance. You can dynamically change this parameter by using the alter system command:

alter system set user_dump_dest="c:\kflosstrace";

You can also add your own marker to the trace file names so you can more easily find the generated files. To do so, set the tracefile_identifier initialization parameter before starting a trace:

alter session set 

tracefile_identifier ="kfloss_test";

Trace files generated by this command have the string value you set appended to the filenames. Although neither of these alter commands is necessary, both make it easier to find the results of a tracing session.

Now that we've set these parameters, let's look at the new tracing package and the Oracle Enterprise Manager interface. 

Let's set up a trace by module name and client name, using the new DBMS_MONITOR package.

Setting Up Tracing with DBMS_MONITOR

The DBMS_MONITOR package has routines for enabling and disabling statistics aggregation as well as for tracing by session ID, or tracing based upon a combination of service name, module name, and action name. (These three are associated hierarchically: you can't specify an action without specifying the module and the service name, but you can specify only the service name, or only the service name and module name.) The module and action names, if available, come from within the application code. For example, Oracle E-Business Suite applications provide module and action names in the code, so you can identify these by name in any of the Oracle Enterprise Manager pages. (PL/SQL developers can embed calls into their applications by using the DBMS_APPLICATION_INFO package to set module and action names.)

Note that setting the module, action, and other paramters such as client_id no longer causes a round-trip to the database—these routines now piggyback on all calls from the application.

The service name is determined by the connect string used to connect to a service. User sessions not associated with a specific service are handled by sys$users (sys$background is the default service for the background processes). Since we have a service and a module name, we can turn on tracing for this module as follows:

SQL> exec dbms_monitor.serv_mod_act_trace_enable

(service_name=>'testenv', module_name=>'product_update');

PL/SQL procedure successfully completed.

We can turn on tracing for the client:

SQL> exec dbms_monitor.client_id_trace_enable

(client_id=>'kimberly');

PL/SQL procedure successfully completed.

Note that all of these settings are persistent—all sessions associated with the service and module will be traced, not just the current sessions.

To trace the SQL based on the session ID, look at the Oracle Enter-prise Manager Top Sessions page, or query the V$SESSION view as you likely currently do.

SQL> select sid, serial#, username 

from v$session;

       SID     SERIAL#  USERNAME

     ------    -------  ------------

       133       4152  SYS

       137       2418  SYSMAN

       139         53  KIMBERLY

       140        561  DBSNMP

       141          4  DBSNMP

. . .

       168          1

       169          1

       170          1

28 rows selected.

With the session ID (SID) and serial number, you can use DBMS_MONITOR to enable tracing for just this session:

SQL> exec dbms_monitor.session_trace_enable(139); 

PL/SQL procedure successfully completed.

The serial number defaults to the current serial number for the SID (unless otherwise specified), so if that's the session and serial number you want to trace, you need not look any further. Also, by default, WAITS are set to true and BINDS to false, so the syntax above is effectively the same as the following:

SQL> exec dbms_monitor.session_trace_enable

(session_id=>139, serial_num=>53, waits=>true, binds=>false);

Note that WAITS and BINDS are the same parameters that you might have set in the past using DBMS_SUPPORT and the 10046 event.

If you're working in a production environment, at this point you'd rerun the errant SQL or application, and the trace files would be created accordingly.

Setting Up Tracing with Enterprise Manager

Setting up tracing through Oracle Enterprise Manager starts on the Top Consumers page (available from the Performance page in the Additional Monitoring Links section.  This page shows the system's current resource usage by service, module, client, and action.

You can click on the Top Services, Top Modules, Top Actions, Top Clients, or Top Sessions tabs to see the detail for each of these categories of top consumers, and then you can easily enable (or disable) SQL tracing from each of these pages. Simply select the item from the list on the page and then click on Enable SQL Trace to begin the trace (and click on 

Disable when you're finished).

You can enable (or disable) statistics aggregation for any items listed on these pages as well. (DBMS_MONITOR also provides routines for enabling and disabling aggregation.)

Analyzing Trace Results

Whether you use DBMS_MONITOR or Oracle Enterprise Manager to set up tracing, you'll use the trcsess command line tool to consolidate trace files. Click on the View SQL Trace button in Oracle Enterprise Manager to display a page that shows the syntax you'll use to consolidate all trace files.

Be sure to double-quote the strings, and add a ".trc" extension to the filename; otherwise, TKPROF won't accept it as a filename. Before executing the command, navigate to the directory specified in the user_dump_dest (or \udump, if you didn't change this parameter name).

C:\...\udump> trcsess output="kfloss.trc" service="testenv" 

module="product update" 

action="batch insert"

You can then run TKPROF against the consolidated trace file to generate a report.

C:\...\udump> tkprof kfloss.trc 

output=kfloss_trace_report SORT=(EXEELA, PRSELA, FCHELA)

If you don't disable tracing, every session that runs that service and module will be traced. Thus, when you're finished, be sure to disable tracing by using either Oracle Enterprise Manager or the DBMS_MONITOR package.

REFERENCES

Tracing SQL in Oracle Database 10g By Kimberly Floss