We have PL/SQL stored procedures that perform poorly, Using DBA_HIST or any other AWR / ASH metrics, is it possible to determine the runtimes of PL/SQL Procedures ?
Here's an example of what you could do.
I've set this running in one session (sid=1328)
SQL> declare
2 x int;
3 begin
4 loop
5 select count(*) into x from dba_objects;
6 end loop;
7 end;
8 /
So it will run slowly and most of the time is SQL.
If you look in v$active_session_history, you'll typically see:
SQL_ID - the 'select' above
However, TOP_LEVEL_SQL_ID will be the PL/SQL block, eg
SQL> @text 9z78t8v5gav2g
CHILD_NUMBER SQL_FULLTEXT
------------ --------------------------------------------------------------------------------
0 declare
x int;
begin
loop
select count(*) into x from dba_objects;
end loop;
end;
and the OP_CODE will be 47 (PLSQL).
So you can use ASH to dig into slow running PLSQL by exploring the combination of SQL_ID, TOP_LEVEL_SQL_ID, OP_CODE and the like