Looking for something? Try here..

Monday, December 20, 2021

Oracle Application/session tracing methods - 2

 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>

For tracing sessions using Client_identifier:

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>

Database level tracing

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>

With this, we have looked at all the ways we can trace the application/DB sessions. 
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
Posted by at
Labels: , , , , ,

2 comments:

  1. AmuthanDecember 20, 2021 at 7:23 PM

    Hi Selva.. this is really interesting.
    Just a clarification. If a main package where we set client info calls another underlying procedure or function inside, still the tracing track them ?

    ReplyDelete
    Replies
    1. Selvakumar NagulanDecember 20, 2021 at 11:42 PM

      Hi Amuthan,

      Yes, it should trace the the function and procedure that comes under the main package. You can try creating another procedure as below

      create or replace procedure up_test3 as
      BEGIN

      INSERT INTO test1 (id,name)
      VALUES (102,'ORABLISS');

      UPDATE test1
      SET id = 101
      WHERE name = 'test';
      END;
      /

      Call this proc via another proc by adding client identifier and enable trace. You will find the sql executed captured in trace file.

      CREATE
      OR replace PROCEDURE up_test2 AS

      BEGIN
      dbms_application_info.set_client_info('client info test');
      dbms_session.set_identifier('client info test');

      up_test3;

      dbms_session.clear_identifier;
      END;
      /

      Thanks!

      Delete

Subscribe to: Post Comments (Atom)