Forum Stats

  • 3,780,462 Users
  • 2,254,398 Discussions
  • 7,879,337 Comments

Discussions

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

3703006
3703006 Member Posts: 35
edited Jul 13, 2018 3:17AM in SQL & PL/SQL

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

Tagged:
3703006

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jul 12, 2018 11:21PM
    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
    Billy Verreynne Software Engineer Member Posts: 28,638 Red Diamond
    edited Jul 13, 2018 12:47AM

    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.

    3703006
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jul 13, 2018 3:17AM
    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.

    3703006
This discussion has been closed.