Home » RDBMS Server » Performance Tuning » compare two procedures for performance (Oracle 11.2.0.3)
compare two procedures for performance [message #656242] Fri, 30 September 2016 02:15 Go to next message
pointers
Messages: 439
Registered: May 2008
Senior Member
Hi,

Could you please help me how can I compare two plsql procedures for performance.

What I am looking is execution plan of each sql inside the procedures, time taken for each sql etc.

I can make use of plsql profiler, but not sure if it gives me execution plan. what utilities we should use for such things.

Thank you in advance.

Regards,
Pointers
Re: compare two procedures for performance [message #656244 is a reply to message #656242] Fri, 30 September 2016 03:02 Go to previous messageGo to next message
gazzag
Messages: 909
Registered: November 2010
Location: Bristol, UK
Senior Member
Look at what the procedures do. Break them down into their constituent statements/queries and use EXPLAIN PLAN to see where the waits are.
Re: compare two procedures for performance [message #656246 is a reply to message #656244] Fri, 30 September 2016 03:15 Go to previous messageGo to next message
pointers
Messages: 439
Registered: May 2008
Senior Member

Is there not a way to do it automatically instead of running each sql separately taking out from plsql.

Regards,
Pointers
Re: compare two procedures for performance [message #656247 is a reply to message #656246] Fri, 30 September 2016 03:43 Go to previous messageGo to next message
gazzag
Messages: 909
Registered: November 2010
Location: Bristol, UK
Senior Member
How will you know which part each procedure is waiting on?
Re: compare two procedures for performance [message #656248 is a reply to message #656247] Fri, 30 September 2016 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you just trace the session for each?
The tkprof output gives execution plans and all waits.
Re: compare two procedures for performance [message #656250 is a reply to message #656246] Fri, 30 September 2016 04:40 Go to previous message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can query V$SQL_PLAN.

Previous Topic: Do we need to sit down with custom about determining which statspack snapshot to be used as baseline
Next Topic: Partitioning huge table
Goto Forum:
  


Current Time: Thu Feb 22 03:40:52 CST 2018

Total time taken to generate the page: 0.03695 seconds