Generating Oracle trace files in a web application

Using Oracle trace files is the best way to investigate random performance issues. In a real world web application, it is not practical to enable database trace for entire database as it will result in performance degradation and generates huge trace files. Also it is difficult to dig out useful information from a large file. So the most efficient way is to enable trace for particular connections. Web applications use connection pools and there is no guarantee on which connection is used to serve each request. This makes it difficult to turn on trace for a particular connection. Here comes the Oracle client identifier (CLIENT_IDENTIFIER) to rescue.

What is client identifier and how to set it?

 Client identifier is a token which can be set by the application. This can be reset/changed by the client application. This identifier can be used to uniquely identify users associated with a connection. I suggest every serious application to set this identifier as this will come handy sometime in future. Two uses that I know is to use it to generate trace files and to use it in auditing done by a trigger. Since this is obtained from connection, a trigger can get it from environment.

In Oracle 10g, new ways are introduced to use CLIENT_IDENTIFIER to turn on trace and to consolidate multiple trace files into one which uses same CLIENT_IDENTIFIER.

Use the procedure DBMS_SESSION.SET_IDENTIFIER() to set the identifier. This identifier is then visible in the V$SESSION view as CLIENT_IDENTIFIER column.

An example usage will be to set the users login id as the client id. Using this DBA can enable trace for the particular connection.

EXEC DBMS_SESSION.SET_IDENTIFIER('');

This can be reset using DBMS_SESSION.CLEAR_IDENTIFIER().

EXEC DBMS_SESSION.CLEAR_IDENTIFIER();

To view client identifier one can look at V$SESSION view.

SELECT * FROM V$SESSION WHERE CLIENT_IDENTIFIER = '';

To get client id for the current session use

SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') AS CLIENT_IDENTIFIER FROM DUAL;

In an application, client id should be set when connection is obtained from the connection pool and it should be reset when it is released. Normally every application will have utility class to retrieve connection. Setting client id can be added to this code. Adding code for resetting client identifier can be done by using a wrapper class for the connection and implementing close() method.

Enable trace for a client id

To enable/disable trace for a particular client id, one needs to have DBA role to invoke the required packages. Also trace files are generated in Oracle server and one needs to access to server to get the files. In a web environment where connection pool is used, generating trace file using client id may create multiple trace files as Oracle generates new files for each connection. These files can be consolidated into single file using TRCSESS command line tool. This is also installed in server and is not installed as part of Oracle client. Frankly speaking I haven’t done following section on enabling and disabling trace as these are done by the DBA.

To start tracing for a client id

EXEC DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('', WAITS=> TRUE, BINDS=> TRUE);

To stop tracing for a client id

EXEC DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('');

It is possible to set the name identifier for trace files so that it is easy to identify who and why it is generated. To set the identifier, run following SQL once for each connection.

ALTER SESSION SET TRACEFILE_IDENTIFIER = "";

If there are multiple connections involved, Oracle generates many files. These will be located in the Oracle server at /admin//udump directory. To consolidate use following command

trcsess output=.trc clientid='' *.trc

This generates a single file by consolidating all trace information from files of pattern *.trc for the client id .

You can use TKPROF to generate report from the trace file. The raw trace file content is not in a form that can be easily understood. TKPROF generates a text report and we can easily locate SQL which takes time. Also this contains details on network traffic.

I did not like the text report and used OraSRP which generates and HTML report. This is a tool written by individual and is available at http://oracledba.ru/orasrp/.

There are other ways to generate trace files which can be done by based on demand and does not need client identifier. This will be explained in another installment.