Is there a way to get the stack trace of the SQL query that is given in awr, I mean a way to check the flow of the query from which package or procedure that it has been triggered? tkprof and awr both show only the SQL and the elapsed time, parse and fetches.
Stack trace of oracle sql execution
oracleoracle-11g-r2
Best Answer
You mentioned AWR, so you are licensed to use ASH also. You can find the session(s) executing the specific SQL in DBA_HIST_ACTIVE_SESS_HISTORY view based on the sql_id. The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID columns contain information about the PL/SQL call stack.
This however may not be complete if you have several levels of PL/SQL packages, procedures or functions built on top of each other.
If you know the sql_id and that the SQL statement will be executed in the future, you can set a trace event to dump an errorstack for this specific statement (which can cause quite big overhead and create huge trace files, but I could not come up with a more effective method).
Given the below example:
You can set the below event for the above SQL:
The next time this SQL statement executes, the trace and errorstack dump will be triggered:
The trace file created by this will contain the PL/SQL call stack like this:
Above shows that there was an anonymous block calling BP.MYPACKAGE at the 5th line, which is exactly the position of the SQL statement in procedure MYPROCEDURE.