Stack trace of oracle sql execution
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.