Re: ref cursor slow compared to sql query

From: Allan Streib <streib_at_cs.indiana.edu>
Date: 28 Jun 2004 08:06:28 -0700
Message-ID: <e334e4a7.0406280706.7ca5f97f@posting.google.com>

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