In my previous post, we saw how to trace individual sessions. The challenge involved in real world performance issues is that mostly there won't be any single session that would run in the DB but the application would spawn multiple processes/sessions. So in this case if we want to trace a session, it will be tedious task to identify which session to trace. Many time there would be run away sessions and a few times even the developers wouldn't be knowing the entire work flow which will again add complexity to the existing issue.
During these times, instead of tracing individual session we can go ahead and trace set of sessions using application tracing methods. In this post, we will look at the methods on how to do the application tracing
Application level tracing
Application tracing can be performed by using
- DBMS_SQL_MONITOR
- DBMS_MONITOR
We will have a separate post on DBMS_SQL_MONITOR later and now will look into the usage of DBMS_MONITOR
DBMS_MONITOR
Information that we need to proceed with application tracing using dbms_monitor are either of the 3 below
- Client info
- Module
- Action
All of the 3 might or might not be obtained from v$session as v$session could also contain null for these fields. For eg:
SQL> set sqlformat ansiconsole
SQL> select sid, serial#, username, client_info, module, action from v$session where username not in ('SYS','SYSTEM')
SID SERIAL# USERNAME CLIENT_INFO MODULE ACTION
---------------------------------------------------------------------------------------------
17 63073 SOE Swingbench Load Generator JDBC Thin Client
401 47594 SOE Swingbench Load Generator JDBC Thin Client
778 11025 SOE SQL*Plus
780 55219 SYSRAC oraagent.bin@linux75-2.selvapc.com (TNS V1-V3)
1151 4268 SOE Swingbench Load Generator JDBC Thin Client
1157 61639 SYSRAC oraagent.bin@linux75-2.selvapc.com (TNS V1-V3)
1158 10873 TEST SQL Developer
1166 11417 SOE Swingbench Load Generator JDBC Thin Client
8 rows selected.
SQL>
You can see a few session has CLIENT_INFO and a few doesn't and none of sessions has info on ACTION field. We can use DBMS_APPLICATION_INFO package to extract the details needed or to set them as well by using READ_CLIENT_INFO, READ_MODULE, SET_ACTION, SET_CLIENT_INFO or SET_MODULE procedures which will be explained below
READ_CLIENT_INFO and READ_MODULE can be used to read the details from the specific SID using v$session view
SET_ACTION, SET_CLIENT_INFO or SET_MODULE can be used to set the names for the specific program or piece of code which makes tracing easy.
The below code provides an example of how to insert the DBMS_APPLICATION_INFO package in application program code making way for us to trace as desired.
CREATE OR replace PROCEDURE up_test1 AS BEGIN dbms_application_info.set_module(module_name=> 'Change name', action_name=> 'insert id'); INSERT INTO test1 (id,name) VALUES (102,'ORABLISS'); dbms_application_info.set_action(action_name=> 'update id'); UPDATE test1 SET id = 101 WHERE name = 'test'; dbms_application_info.set_module(NULL, NULL); END; /You can see I have inserted DBMS_APPLICATION_INFO.SET_MODULE in 2 places one with module_name and action_name and another with only action_name. This way we can trace the parts only we need in code with large number of lines.
Once we introduced the module_name or action_name we can trace the sessions running this piece of code without even knowing the session id of the sessions executing them.
Use DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure to trace sessions as desired for investigation
For tracing sessions using module_name:
SQL> exec dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'ORCL',module_name=>'Change name'); PL/SQL procedure successfully completed. -- Run the procedure using other session which will trigger the tracing -- Once the procedure is completed we can disable trace as follows. SQL> exec dbms_monitor.SERV_MOD_ACT_TRACE_disable(service_name=>'ORCL',module_name=>'Change name'); PL/SQL procedure successfully completed. SQL>
For tracing sessions performing specific action:
we can also specify the action_name which will trigger tracing only when the specific action name is started in the program
SQL> exec dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'ORCL',module_name=>'Change name',action_name=> 'update id',waits=>true,binds=>true); PL/SQL procedure successfully completed. -- Run the procedure using other session which will trigger the tracing -- This time tracing will capture only the update statement in the procedure as the action is named as 'update id' -- Once the procedure is completed we can disable trace as follows. SQL> exec dbms_monitor.SERV_MOD_ACT_TRACE_disable(service_name=>'ORCL',module_name=>'Change name',action_name=> 'update id'); PL/SQL procedure successfully completed. SQL>The above SERV_MOD_ACT_TRACE_ENABLE procedure follows strict hierarchy, meaning - service_name is mandatory followed by module_name and action_name. If no module_name is provided, we can't provide action_name and if no action_anme is provided all the action under the module will be traced.
For tracing sessions using specific service_name:
If we don't specify any module_name, all the sessions connecting to the DB using the service_name will be traced. Different applications can connect to same database using different service names as desired by the database admin to segregate them.
SQL> exec dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'ORCL'); PL/SQL procedure successfully completed. -- This time tracing will capture all the sessions that connect to DB using ORCL service -- We can disable trace as follows. SQL> exec dbms_monitor.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'ORCL'); PL/SQL procedure successfully completed. SQL>
v$session will have client_info filed populated for most of the application. If it is null like the example I gave in this post, we can use the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure to set in the program/main procedure similar to below
CREATE
OR replace PROCEDURE up_test2 AS
BEGIN
dbms_application_info.set_client_info('client info test');
dbms_session.set_identifier('client info test');
INSERT INTO test1 (id,name)
VALUES (102,'ORABLISS');
UPDATE test1
SET id = 101
WHERE name = 'test';
dbms_session.clear_identifier;
END;
/
As I have now included the DBMS_SESSION.SET_IDENTIFIER, we can trace the session without searching for the session id. Setting only CLIENT_INFO will be useful to find the session details to enable tracing and for automatic tracing of session with client id, we need the dbms_session.set_identifier
SQL> exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('client info test');
PL/SQL procedure successfully completed.
-- Run the procedure up_test2 using other session which will trigger the tracing using client identifier
-- Once the procedure is completed we can disable trace as follows (optional)
SQL> exec DBMS_MONITOR.CLIENT_ID_TRACE_disable('client info test');
PL/SQL procedure successfully completed.
SQL>
The above methods explained regarding the options of tracing parts of database and its sessions.
If a need arise to trace the entire database (I don't think any one would do this on a production database), we can do so by DBMS_MONITOR.DATABASE_TRACE_ENABLE procedure
SQL> exec DBMS_MONITOR.DATABASE_TRACE_ENABLE; PL/SQL procedure successfully completed. -- All the sessions in the database will be traced -- waits, binds, instance_name and plan_stat can be provided as arguments -- To disable tracing SQL> exec DBMS_MONITOR.DATABASE_TRACE_DISABLE; PL/SQL procedure successfully completed. SQL>
All the trace files generated will be in Oracle proprietary format. To convert the trace files generated and make use of the trace files, check this link on tkprof
Happy Tracing
