SQL in Sproc runs 10x longer than sql at a prompt

From: Brady, Mark <Mark.Brady_at_Constellation.Com>
Date: Fri, 29 May 2009 12:17:58 -0400
Message-ID: <46732D8B0755664E87B4276E3C2FF60593BE2AFEB6_at_EXM-OMF-06.Ceg.Corp.Net>


So we run a proc from SQL*PLUS or Toad and we see it finish in about 100 seconds.

The same SQL in a Stored Proc runs for 1000 seconds.

Anticipating, they aren't "actually" the same... that's true, they have 2 different hash values.


They both have the same SQL PLAN HASH value.

Doesn't that mean they have the same plan? This isn't an issue of plan instability between the stored procedure environment and the SQL*PLUS environment, right? Knowing Oracle, that's surely not the end-of-the-story.

What should I be looking at?

>>> This e-mail and any attachments are confidential, may contain legal, professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP1

Received on Fri May 29 2009 - 11:17:58 CDT

Original text of this message