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

From: Adam Musch <ahmusch_at_gmail.com>
Date: Fri, 29 May 2009 12:22:19 -0500
Message-ID: <516d05a0905291022i7c9ae47eye34f40798d623eae_at_mail.gmail.com>

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_at_spammenot.gmail.com
On Fri, May 29, 2009 at 12:13 PM, Cary Millsap <cary.millsap_at_method-r.com>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_at_constellation.com> 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
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 29 2009 - 12:22:19 CDT