Stack trace of oracle sql execution

Question

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.

Answer 1

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:

create or replace package mypackage as
 procedure myprocedure;
end;
/
create or replace package body mypackage as
 procedure myprocedure as
 x number;
 begin
 select count(*) into x from user_objects;
 end;
end;
/
execute mypackage.myprocedure;
select distinct sql_id, sql_text from v$sql where lower(sql_text) like '%user_objects%' and lower(sql_text) not like '%v$%';
SQL_ID SQL_TEXT
------------- ---------------------------------
4fjk3rakk6x48 SELECT COUNT(*) FROM USER_OBJECTS

You can set the below event for the above SQL:

alter system set events 'sql_trace[SQL: 4fjk3rakk6x48] errorstack(1)';

The next time this SQL statement executes, the trace and errorstack dump will be triggered:

execute mypackage.myprocedure;

The trace file created by this will contain the PL/SQL call stack like this:

----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=4fjk3rakk6x48) -----
SELECT COUNT(*) FROM USER_OBJECTS
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
 object line object
 handle number name
0x870c5950 5 package body BP.MYPACKAGE
0x8706e1b8 1 anonymous block

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.

• anudeepks: Thank you, that was new information for me and learnt from it, our programs run in batches and it would be difficult to set traces for each sql id, and re-running the batch wouldn't be possible. Host of new information that I have gathered from your post.