On Mon, 28 Jun 2004 10:56:51 -0500, Turkbear <john.g_at_dot.spamfree.com>
wrote:
>streib_at_cs.indiana.edu (Allan Streib) wrote:
>
>>streib_at_cs.indiana.edu (Allan Streib) wrote in message news:<e334e4a7.0406240554.50eac03f_at_posting.google.com>...
>>> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<cbcrbm$i57$1_at_sparta.btinternet.com>...
>>>
>>[...]
>>> > Alter session set sql_trace = true;
>>> > Call the proc
>>> > Run the straight sql
>>> > exit from SQL*Plus
>>> >
>>
>>Comparing the two query plans, the stored proc is doing an index full
>>scan whereas the straight SQL is doing an index skip scan (same
>>index). There is an order of magnitude difference in the number of
>>rows (66,672 vs. 5,880) in this step. This seems to be the glaring
>>difference between the two plans.
>>
>>Thanks for the help so far -- if anyone has suggestions on how to
>>resolve this please follow up.
>>
>>Allan
>
>If you are running the Sql immediately after the proc, the data is likely in the cache so it would have a different
>execution plan.
>
Are you sure about that? I've never seen anything to indicate that
the optimizer looked to see what was in the buffer cache in developing
a plan.
Received on Mon Jun 28 2004 - 12:02:07 CDT
