Dave Webster's Blog

Tinkering with Oracle

Archive for December, 2014

SUBPROGRAM IDs for private procedures

Posted by anotherdavewebster on December 22, 2014

*** SPOILER ALERT : This Blog post ends with a question (it’s a story of failure) ***

I’ve been looking into some Active Session History data for a problem we encountered in some PL/SQL code in our production environment.  ASH is wonderful at telling us what SQL is running but not great at showing us which PL/SQL code is running.  There is a little hope by way of the data in PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, PLSQL_ENTRY_OBJECT_ID, PLSQL_OBJECT_ID and PLSQL_SUBPROGRAM_ID.

The first two, the “ENTRY” values show the initial PL/SQL call, the top of the call stack.  They’re easy to interpret:

select * from dba_objects
where object_id = <PLSQL_ENTRY_OBJECT_ID>

shows the package (specification, not body) and:

select * from dba_procedures
where object_id = <PLSQL_ENTRY_OBJECT_ID> and subprogram_id = <PLSQL_ENTRY_SUBPROGRAM_ID>

shows the procedure.  Obviously the second query shows you the package and the procedure, so, for the ENTRY columns, the former query isn’t really necessary.

My problem came when I wanted to find out which procedures were being referenced by PLSQL_SUBPROGRAM_ID as these could be private procedures and THEY DON”T EXIST IN DBA_PROCEDURES.  So the question is, how do I find out which private procedure is being executed?

Read the rest of this entry »

Posted in Uncategorized | 2 Comments »