Skip to Main Content
  • Questions
  • Compare execution performance stats between two PL/SQL procedures


Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 25, 2020 - 10:49 pm UTC

Answered by: Connor McDonald - Last updated: March 03, 2020 - 6:33 am UTC

Category: PL/SQL - Version: 12.2

Viewed 100+ times

You Asked


From the past I am only aware of Tom Kyte's Runstats package, which allows comparing some performance metrics across two procedures. I also found that the DBMS_SQLPA package also provides a feature that could help in this kind of scenario.

For example once you change procedure or have alternative approach , how to compare performance metric's across those options. Impact of the changes.

Are there any more methods to do the above comparison using Oracle provided features ?

I am on Oracle 12.2 EE.


and we said...

Other options to look at:

1) The debugger DBMS_DEBUG (although that is now deprecated) but similar options exist in DBMS_DEBUG_JDWP

2) The hierarchical profiler DBMS_HPROF

Some examples here on Tim's site

3) the utPLSQL framework

4) the plsql profiler DBMS_PROFILER

A nice presentation on that here

and you rated our response

  (1 rating)


Compare execution performance stats between two PL/SQL procedures

February 26, 2020 - 4:08 am UTC

Reviewer: RG from Toronto,ON

Thanks Connor, appreciate your quick response.

Do you happen to know how to load a PL/SQL procedure to a dbms_sqltune.load_sqlset ? so it can be analyzed from DBMS_SQLPA.

instead of the sql statement , I want to pass in a package procedure

stscur dbms_sqltune.sqlset_cursor;

OPEN stscur FOR
FROM TABLE(dbms_sqltune.select_cursor_cache(
'sql_text like ''SELECT /*+ my_query%''',
null, null, null, null, null, null, 'ALL')) P;

-- populate the sqlset
dbms_sqltune.load_sqlset(:sts_name, stscur);


Connor McDonald


March 03, 2020 - 6:33 am UTC

You can't but you can find the queries that came out of your procedure by querying v$sql.

I blogged about there here

More to Explore


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