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: 443
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

Report message to a moderator

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: 985
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.

Report message to a moderator

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: 443
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

Report message to a moderator

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: 985
Registered: November 2010
Location: Bristol, UK
Senior Member
How will you know which part each procedure is waiting on?

Report message to a moderator

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: 13265
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.

Report message to a moderator

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: 65840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can query V$SQL_PLAN.

Report message to a moderator

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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Oct 16 06:51:21 CDT 2018
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.