Find which PL/SQL Package called a SQL_ID

Question

We have a certain SQL_ID that has performance issues (ex. cd123812jedjdu). This was shown from the AWR report to be called from PKG1. However, PKG1 calls so many packages inside, like below:

PKG1.MAIN
-> PKG1.PROC1
-> -> PKG2.PROC1
-> -> PKG2.PROC2
-> -> PKG2.PROC3
-> PKG1.PROC2
-> -> PKG3.PROC1
-> -> PKG3.PROC2
-> -> PKG3.PROC3
-> PKG1.PROC3
-> -> PKG4.PROC1
-> -> PKG4.PROC2
-> -> PKG4.PROC3
-> PKG1.PROC4
-> -> PKG5.PROC1
-> -> PKG5.PROC2
-> -> PKG5.PROC3

After hours of tracing, I found out it was being called from PKG4.PROC3. Is there any way I can find out the actual PL/SQL package that calls the SQL_ID, perhaps using the v$ views?

Note: I know the SQL_TEXT can be queries from v$ views, but there are multiple similar queries with the same SQL_TEXT.

Answer 1

You don't need to use PL/Scope, the information you need is already stored in v$sql when the query is hard parsed (of course, if you have the same query appearing in multiple pieces of code, only the one that it was hard parsed from will appear, unless there are sufficient differences to trigger an additional child cursor).

The column you care about is program_id it matches up to dba_objects.object_id. It will also tell you the line number the SQL appears in under program_line#.

Here's a quick LiveSQL demo. The columns have existed since at least Oracle 10.2.