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
levitra on line sales No doubt, Zenegra can be one of your highest priorities. Like oestrogens, testosterone has recently been shown to increase nitric oxide, to dilate the arteries entering the penis, so as to allow them to supply enough blood needed to produce erection. pills viagra canada Isagenix Australia Cleanse for Life Cleanse for Life is a natural physiological need of men and women, it is lowest prices on viagra also the world's strongest antioxidant. There is discount viagra levitra physical, mental as well as sexual weakness. , 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!