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

One other thing to do is to make sure to have both run against a similar
cache, whether empty or populated.  So you'd either:

Run the query, then
Enable Tracing and run stored proc; then
disconnect/reconnect
Enable Tracing and run the query again.

Alternatively:
alter system clear buffer_cache;
 Enable Tracing and run stored proc; then
disconnect/reconnect
 alter system clear buffer_cache;
Enable Tracing and run the query

This would eliminate any PIO penalties one of the two would potentially
have.

Adam Musch
ahmusch@xxxxxxxxxxxxxxxxxxx
On Fri, May 29, 2009 at 12:13 PM, Cary Millsap <cary.millsap@xxxxxxxxxxxx>wrote:

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