Tom noted on his blog recently that DBMS_APPLICATION_INFO is something we should be using in our code to provide instrumentation and monitoring information – something I fully agree with and have been doing for some time now.

The post showed how, if we’ve called DBMS_APPLICATION_INFO in our code, we can retrieve the values of the MODULE/ACTION/PROGRAM_ID by querying V$SQL with a nice example to illustrate the point.

After reading it I recalled that some of this kind of information is available on V$SESSION from 10.2.0.2 (Thanks to Yas for identifying the specific version) so I created a simple supplementary example to illustrate this:

First I created a package – I’m using a package in order to demonstrate a secondary point which will become clearer as we progress…

create or replace package pk1 as
  procedure p3;
  procedure p4;
end pk1;
/

create or replace package body pk1 as
procedure p3 is
begin
  dbms_application_info.set_module( 'PK1 module', 'Startup' );
  dbms_application_info.set_action( 'Running P3' );
  for x in ( select * from dual look_for_me_1 ) loop
    null;
  end loop;
  for x in ( select * from dual look_for_me_2 ) loop
    null;
  end loop;
  dbms_lock.sleep(20);
end p3;

procedure p4 is
begin
  dbms_application_info.set_module( 'PK1 module', 'Startup' );
  dbms_application_info.set_action( 'Running P4' );
  for x in ( select * from dual look_for_me_2 ) loop
    null;
  end loop;
  dbms_lock.sleep(20);
end p4;

end pk1;
/

I’ve slightly changed the calls to DBMS_APPLICATION_INFO to show how the SET_ACTION works and also because if I don’t do it that way, the module name gets set by the first subprogram called in PK1 and should therefore not be specific to a subprogram.

Now lets get back the object name and ID – I’ve specifically excluded public synonyms in order to not cloud things:

select object_name
,      object_type
,      object_id
from   all_objects
where  object_name IN('PK1','DBMS_LOCK')
and    owner != 'PUBLIC' -- don't get public synonyms
/

This gives us something like:

OBJECT_NAME  OBJECT_TYPE   OBJECT_ID
------------ ------------- ---------
DBMS_LOCK    PACKAGE BODY  4368    
DBMS_LOCK    PACKAGE       4265    
PK1          PACKAGE BODY  76404   
PK1          PACKAGE       76403   

Now in another sql*plus session I can run the P4 procedure in package PK1 followed by the P3 procedure:

exec pk1.p4;
exec pk1.p3;

After both calls complete I switch to my main session and issue a slightly modified version of the query Tom wrote:

select sql_text
,      action
,      module
,      program_id
,      program_line#
from   v$sql
where  sql_text like '% LOOK_FOR_ME_%' escape ''
/

SQL_TEXT                         ACTION     MODULE     PROGRAM_ID PROGRAM_LINE#
-------------------------------- ---------- ---------- ---------- -------------
SELECT * FROM DUAL LOOK_FOR_ME_1 Running P3 PK1 module 76404      7          
SELECT * FROM DUAL LOOK_FOR_ME_2 Running P4 PK1 module 76404      20        

Which gives us the two cursors that have been run and as Tom indicated would happen, in my example, the LOOK_FOR_ME_2 cursor is first issued in the call to P4 so the Action is “Running P4″ even though the cursor is also issued by P3 subsequently.

Note that the PROGRAM_ID has the OBJECT_ID of the PK1 Package Body in V$SQL.

Now I can rerun the procedures again in the same order and because there is a small wait during the processing I can switch back to my main session to issue the following query against V$SESSION:

select plsql_entry_object_id
,      plsql_entry_subprogram_id
,      plsql_object_id
,      plsql_subprogram_id
,      module
,      action
from   v$session
where  sid = 150  -- this was the SID of my other session!
/

During the call to P4 I see this:

PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID MODULE     ACTION  
--------------------- ------------------------- --------------- ------------------- ---------- ----------
76403                 2                         4265            8                   PK1 module Running P4

…and then whilst P3 is running I see this:

PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID MODULE     ACTION  
--------------------- ------------------------- --------------- ------------------- ---------- ----------
76403                 1                         4265            8                   PK1 module Running P3

So, the PLSQL_ENTRY* columns are showing that we’re running the second subprogram of the PLSQL package with OBJECT_ID 76403 – which is the PK1 Package Spec – so slightly different to the example from Tom with V$SQL – but understandable information nevertheless.

From the other two columns, we can also see the currently executing subprogram – 8 in the package with OBJECT_ID 4265 – which is the DBMS_LOCK Package Spec. If we DESCribe DBMS_LOCK we would see that the 8th subprogram is SLEEP – which tallies up with what we’re executing.

We can also see these subprogram_id values in the ALL_PROCEDURES view:

select object_name
,      object_type
,      subprogram_id
,      procedure_name
from   all_procedures
where  object_name IN('PK1','DBMS_LOCK')
order by object_name
,        subprogram_id
/

OBJECT_NAME OBJECT_TYPE SUBPROGRAM_ID PROCEDURE_NAME
----------- ----------- ------------- ---------------
DBMS_LOCK   PACKAGE     0           
DBMS_LOCK   PACKAGE     1             ALLOCATE_UNIQUE
DBMS_LOCK   PACKAGE     2             REQUEST
DBMS_LOCK   PACKAGE     3             REQUEST
DBMS_LOCK   PACKAGE     4             CONVERT
DBMS_LOCK   PACKAGE     5             CONVERT
DBMS_LOCK   PACKAGE     6             RELEASE
DBMS_LOCK   PACKAGE     7             RELEASE
DBMS_LOCK   PACKAGE     8             SLEEP
PK1         PACKAGE     0           
PK1         PACKAGE     1             P3
PK1         PACKAGE     2             P4

So, V$SQL gives us useful information but we can also use V$SESSION in addition to get information by session including the subprogram within a package that we are executing.

As I said at the start, I’ve been using this for years and I’ve no idea why I don’t see it’s use more often. Hopefully the “Tom” effect will help to resolve this!