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:
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;
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 18.104.22.168
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.
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.
> 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
> tkprof TEST_TRC.trc TEST_tkprof.rpt sys=no record=TEST_sql.txt