Re: ref cursor slow compared to sql query

It is possible that the problem is related to an error in the optimizer to do with IN LIST calculations. (This was "fixed" in newer versions, possibly only 9.2)

The selectivity, hence cardinality, of an in-list can be seriously underestimated - which often leads to Oracle using an indexed access path where (in theory) it shouldn't. Then, when you switch to the 'temporary table' (or table()) option the error goes away, and the calculated selectivity and cardinality go up so the plan is likely to change. (This is one of several reasons why people upgrading from 8 to 9 had performance problems).

There is no easy solution - Oracle 8 does it wrong for in-lists. One dirty option is to use the 'selectivity' hint, e.g. /*+ selectivity (t1 0.01) */ to tell the optimizer that the single-table predicates (in this case your list) will typically return a fixed fraction of the data, or the cardinality hint e.g. /*+ cardinality(t1 50) */ to tell the optimizer how many rows the single-table predicates will return. It's not a nice solution (and selectivity() seems to be broken in 10g anyway).

An alternative is to check the execution path with the literal list (and this will probably include an 'ITERATOR' line) and use more conventional hints to emulate the important parts of the path when you use a temp table.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"Allan Streib" <streib_at_cs.indiana.edu> wrote in message
news:e334e4a7.0406291306.7040a7ee_at_posting.google.com...


> OK I have narrowed this down -- it has nothing to do with the ref

> cursor after all.  I have duplicated the performance problem with

> straight SQL.

>

> The bottleneck is a piece of the overall query where a view is joined

> to an inline view.  This piece of the query takes about 10 seconds to

> execute.  If I replace the inline view with an "in (values)" predicate

> then the whole thing completes in a fraction of a second.  The inline

> view only returns about 40 values.

>