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

Breadcrumb

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

Hi

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.

Thanks,
RG

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

https://oracle-base.com/articles/11g/plsql-hierarchical-profiler-11gr1

3) the utPLSQL framework

http://utplsql.org/

4) the plsql profiler DBMS_PROFILER

A nice presentation on that here

https://www.doag.org/formes/pubfiles/3019717/2011-K-DB-Bryn_Llewellyn-Using_the_PL_SQL_hierarchical_performance_profiler-Praesentation.pdf

and you rated our response

  (1 rating)

Reviews

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

DECLARE
stscur dbms_sqltune.sqlset_cursor;
BEGIN

OPEN stscur FOR
SELECT VALUE(P)
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);

end;
/

Connor McDonald

Followup  

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

https://connor-mcdonald.com/2016/01/20/problematic-sql-plsql-is-your-friend/

More to Explore

PL/SQL

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