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 Connor 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

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Compare execution performance stats between two PL/SQL procedures

RG, February 26, 2020 - 4:08 am UTC

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;
/

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/

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Compare execution performance stats between two PL/SQL procedures

More to Explore

PL/SQL demos

PL/SQL docs