Re: ref cursor slow compared to sql query

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. Received on Mon Jun 28 2004 - 10:56:51 CDT