V$SQL.PROGRAM_ID


It looks like Oracle are being more reliable in populating the PROGRAM_ID in V$SQL. Lets says we had a expensive SQL in our database picked out from V$SQL

SQL_TEXT                                                         BUFFER_GETS  EXECUTIONS SQL_ID
---------------------------------------------------------------- -----------  ---------- -------------
SELECT OBJ_NAME FROM IMS_OBJECT_OF_INTEREST IOOI WHERE IOOI.OBJ_  4190242763    21495111 g2pzk9bbcwj42
ID IN (SELECT GL.CHILD_ID FROM GEN_LINKS GL WHERE GL.RC_CHILD_TA
BLE_ID = DBPK_COMMON.DBF_ID('OOI') AND GL.RC_MAST_TABLE_ID = :B4
AND GL.ASS_ID = NVL(:B3 ,GL.ASS_ID) AND GL.MAST_ID = :B2 UNION
SELECT GL.MAST_ID FROM GEN_LINKS GL WHERE GL.RC_MAST_TABLE_ID =
DBPK_COMMON.DBF_ID('OOI') AND GL.RC_CHILD_TABLE_ID = :B4 AND GL.
ASS_ID = NVL(:B3 ,GL.ASS_ID) AND GL.CHILD_ID = :B2 ) AND IOOI.TD
_REC_TYPE = DBPK_COMMON.DBF_STRING('TEL') AND IOOI.RC_OBJ_CLASS
= NVL(:B1 ,IOOI.RC_OBJ_CLASS) AND IOOI.LOGICALLY_DELETED_IND = '
N' AND IOOI.CURRENT_IND = 'Y'

That its in upper case suggests its from a PL/SQL, so I issue:

SQL> select program_id
  2  from   v$sql
  3  where  sql_id = 'g2pzk9bbcwj42'
  4  /

PROGRAM_ID
----------
   26262

SQL> select object_name
  2  from   dba_objects
  3  where  object_id = 26262;

OBJECT_NAME
----------------------------------------
DBPK_COMMON

Voila! (Thanks to Dan Fink for leading me to this)