Dave Webster's Blog

Tinkering with Oracle

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?

I tried a number of things:

  • DBA_ARGUMENTS, like DBA_PROCEDURES, only shows public procedures.
  • I tried searching the data dictionary for any columns containing SUBPROGRAM but didn’t find anything useful.
  • I dug into the data structures underlying DBA_PROCEDURES and DBA_ARGUMENTS in the vain hope that the private procedures were being filtered out.  Unfortunately not.
  • I tried reading through the package body and counting the position of each procedure or function but that clearly wasn’t correct.
  • I Googled quite a bit.
  • I thought I’d struck gold when I found the “Top PL/SQL Procedures” section in the ASH report.  I could trace the report generation to find the SQL call made to resolve the subprogram ID.  No such luck: the report uses DBA_PROCEDURES (which, as we know, doesn’t include private procedures) and so private procedures show as an entirely useless “UNKNOWN_PLSQL_ID <4787694,4>”
  • I Asked Tom (but he didn’t answer – probably because it’s in the manual somewhere and I’m too stupid to find it)
  • I rolled up my sleeves and wrote a little test code to at least understand how it works…….

My experiments suggest that the procedures are numbered as follows:

  • All public procedures are numbered in the order in which they appear in the SPEC (consistent with what you see in DBA_PROCEDURES)
  • The remaining PRIVATE procedures from the BODY are number in the order in which they appear in the BODY, not counting any forward declarations i.e. the position in the BODY where the actual logic is declared is what counts.

Now this makes a lot of sense as it allows the numbering of the SUBPROGRAMs to be consistent whether they’re public or private.  Thinking about it, nothing else would have made sense.  What is a little disappointing is that I can’t find anywhere where the private procedures are given SUBPROGAM IDs.  At least I now have a way of converting them, even if it’s not pretty.  If I had to do it on a regular basis, I guess I could write some code to parse the package body and combine that with data from DBA_PROCEDURES to come up with what I want.

If you’ve found out where Oracle exposes this information, please tell.

2 Responses to “SUBPROGRAM IDs for private procedures”

  1. Dom Brooks said

    Slight tangent – is this not a suitable use for module/action via dbms_application_info?

    • That would certainly do the job, providing we’d planned ahead and implemented it of course. Unfortunately, in this precise case, those fields have actually been used for another, not altogether suitable purpose. In principle, I agree although I’m still sure that Oracle must have this data in the dictionary somewhere to report it through ASH.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: