Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ref cursor slow compared to sql query

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US