Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, B.

Asked: April 14, 2026 - 4:52 pm UTC

Last updated: April 20, 2026 - 11:23 am UTC

Version: 19.1

You Asked

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 ?


and Connor said...

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


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library