Re: SQL in Sproc runs 10x longer than sql at a prompt
- From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
- To: Mark.Brady@xxxxxxxxxxxxxxxxx
- Date: Fri, 29 May 2009 12:13:00 -0500
Mark,
Trace them both, with
dbms_monitor.session_trace_enable(null,null,true,true). The resulting trace
file will contain all the differences in code path and timings. You can use
tkprof to summarize the results, but tkprof conceals a lot of relevant facts
you might need to understand. Mail me privately if you'd like a view of your
trace files through our Method R Profiler (
http://method-r.com/software/profiler-info).
Cary Millsap
Method R Corporation
http://method-r.com
http://carymillsap.blogspot.com
http://twitter.com/cary_millsap
On Fri, May 29, 2009 at 11:17 AM, Brady, Mark
<Mark.Brady@xxxxxxxxxxxxxxxxx>wrote:
> Ok,
>
> 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.
>
> However,
>
> 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
>
>
Other related posts:
