Tools comparing performance of process using stored procedure(SP) and without SP

3703006

Dear Experts,

    Anybody knows if there is a third-party tool for comparing the performance of a stored procedure written in PL/SQL and the processing without stored procedure?  (running time, network traffic and processor costs etc..    )

   Best regards,

   cc

John Thorton

3703006 wrote:Dear Experts, Anybody knows if there is a third-party tool for comparing the performance of a stored procedure written in PL/SQL and the processing without stored procedure? (running time, network traffic and processor costs etc.. ) Best regards, cc

What is process without any Stored Procedure actually running?

Please provide working example for both alternatives

Billy Verreynne

The easiest and most effective approach is to use V$SESSTAT.

Create a new dedicated server session, run code, and when completed, then capture the stats for that session into a table. Repeat.

You have now detailed stats for both runs, which you can analyse, and compare, using SQL.

AndrewSayer

3703006 wrote:Dear Experts, Anybody knows if there is a third-party tool for comparing the performance of a stored procedure written in PL/SQL and the processing without stored procedure? (running time, network traffic and processor costs etc.. ) Best regards, cc

All that really matters is the response time for the user call, So benchmark it using your own application instrumentation.

If you want some numbers to give you warm fuzzy feelings then see Tom Kytes runstats package, this is a modified version I found with a quick google https://github.com/oracle-developer/runstats  there’s probably a more original version available somewhere.

Or just do the business process in sql*plus with autotrace stats on, it’s not a perfect representstion of how your app would do the call (the immediate differences would be statement caching, fetch sizes, lob treatment) but it should be good enough. The same problems would occur in any third party tool.