Find which PL/SQL Package called a SQL_ID

user_6859002 0
Viewed 642

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.

plsql oracle sql
3 Answers

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.

user_98561760
user_11564520

You can join from a SQL_ID in the SGA to the originating package name and code line as shown in the DBA AWR SQL Performance Summary and DBA SGA SQL Performance Summary blitz reports (Excel columns F+G):

select
(select so.name from sys.obj$ so where gsa.program_id=so.obj#) code,
case when gsa.program_line#>0 then gsa.program_line# end code_line#,
gsa.sql_id,
...
from
gv$sqlarea gsa
user_63064300
Related