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)