OraFAQ Forum: Performance Tuning » compare two procedures for performance

Home » RDBMS Server » Performance Tuning » compare two procedures for performance (Oracle 11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

compare two procedures for performance [message #656242]

Fri, 30 September 2016 02:15

pointers
Messages: 451
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

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, 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

pointers
Messages: 451
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

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, 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

cookiemonster
Messages: 13966
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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 17:45:36 CDT 2025