End to End Application Tracing

The following needs to be done by someone having SYSDBA credentials, This will most likely be your DBA.

 

What is End to End Application Tracing

End to End Application Tracing becomes possible with the use of the CLIENT_IDENTIFIER attribute in V$SESSION dynamic performance view.  Once the system is set up to capture the login information by way of a trigger, this column can identify the client session through all application tiers to the database server.  Tracing based on the CLIENT_IDENTIFIER solves the problem of knowing which session to trace, especially if there are multiple applications accessing the same database instance.

 

Where to look for trace files

select value from v$parameter where name = 'user_dump_dest';

 

What to include in trace files

alter system set statistics_level = typical;

alter system set timed_statistics = true;

 

How to update the CLIENT_IDENTIFIER attribute in V$SESSION

 

The CLIENT_IDENTIFIER column in the V$SESSION dynamic performance view must to be updated with the client information attribute before it can be used for tracing and the best way to do this is to use a logon trigger.  The person having SYSDBA credentials has to create a logon trigger at the system level so that all subsequent logons are identified at the time connection is made to the database.  In the logon trigger the following function and stored procedures are utilized:

•             SYS_CONTEXT – Function returns the value of parameter associated with the context namespace in the current instance

•             DBMS_APPLICATION_INFO.SET_MODULE – Procedure sets the name of the module that is currently running to a new module

•             DBMS_SESSION.SET_IDENTIFIER – Procedure sets the client ID in the session

 

Here is an example of a logon trigger that identifies the Operating System user account (os_user), the user account on the database, and the name of the host from where the connection was made:

 

create or replace trigger database_logon_trigger
after logon
on database
    declare
        adtClientIdentifier varchar2(64);
    begin
        dbms_application_info.set_module(module_name => 'database_logon_trigger', action_name => 'select from dual');
        select sys_context('userenv', 'os_user') ||':'|| user ||':'|| sys_context('userenv', 'host') into adtClientIdentifier from dual;
        dbms_session.set_identifier(adtClientIdentifier );
        dbms_application_info.set_module(null,null);
    end;
/

 

Once the logon trigger is created, all subsequent logons will be identified using the above information. 

Note: There is no need to restart the database server as the database trigger is active upon creation.  If the database trigger was created while the application server was connected, the application server will have to be restarted so that the Client Identifier information can be captured upon connection.  As long as the database trigger is not dropped, this feature will be enabled indefinitely, and it poses no impact to performance.

The following query will show current connections with the required information to begin tracing:

select sid, serial#, username, client_identifier, service_name, module from v$session where username is not null order by 3,4,1;

 

SID         SERIAL#                USERNAME CLIENT_IDENTIFIER                SERVICE_NAME                     MODULE

515         33,469   TEST       TEST:TEST:NA\L1234      TESTDB.TESTORA.com  

1,265     22,825   TEST       TEST:TEST:NA\L1234      TESTDB.TESTORA.com   TOAD background query session

2,778     53,565   TEST       TEST:TEST:NA\L1234      TESTDB.TESTORA.com   TOAD 11.5.0.56

How to enable tracing

Tracing is enabled by using the CLIENT_ID_TRACE_ENABLE procedure in the DBMS_MONITOR package.  The value for the CLIENT_ID parameter is obtained from the V$SESSION view indicated above. 

execute dbms_monitor.client_id_trace_enable (client_id => '<client_identifier>', waits => TRUE, binds => TRUE);

 

Note: Tracing should be left on only long enough to capture the SQL needed for identifying the problem.  Do not leave it on for extended period of time as the trace file will become too large and cumbersome to use.

For example:

SQL> execute dbms_monitor.client_id_trace_enable (client_id => ' TEST:TEST:NA\L1234’, waits => TRUE, binds => TRUE);

PL/SQL procedure successfully completed

How to verify if tracing is enabled

      select trace_type, primary_id, waits, binds from dba_enabled_traces;

How to disable tracing

execute dbms_monitor.client_id_trace_disable (client_id => '<client_identifier>');

How does one merge all the trace files into a single file

    Oracle provides a utility called trcsess written in Java which merges the trace information into a single output file based on criteria.  The trcsess utility consolidates trace output from selected trace files based on several criteria:

•             Session ID – Consolidates the trace information for the session specified.  The session identifier is a combination of session index and session serial number, such as 21.2371.

 

•             Client ID – Consolidates the trace information given client ID.

•             Service name – Consolidates the trace information for the given service name.

Note: You can locate these values in the V$SESSION view.

Using a command prompt on Windows or a terminal window on UNIX/Linux, navigate to the user_dump_dest directory and issue the following command:

trcsess output=<output file name> clientid=<client_identifier> service=<service name> *.trc

Note:  The utility will look through all files having .trc file extension and extract only the content that is relevant to the client_identifier using the service name.

  For example:

> trcsess output=TEST_TRC.trc clientid= TEST:TEST:NA\L1234 service=SYS$USERS *.trc

How does one extract the SQL statements from the output file

Oracle provides a utility called TKPROF which formats the contents of the trace file and places the output into a readable output file.  The various formatting options available within TKPROF is beyond the scope of this document.  To extract the SQL statements, issue the following command using a command prompt on Windows or a terminal window on UNIX/Linux:

tkprof tracefile outputfile sys=no record=filename

For example:

> tkprof TEST_TRC.trc  TEST_tkprof.rpt sys=no record=TEST_sql.txt