Re: INLIST ITERATOR vs. CONCATENATION

On Mon, 17 Mar 2003 19:12:30 +0000, NetComrade wrote:

> I have a heavily used query that has an IN clause. Recently we have analyzed
> tables and started using CBO The query is using a function, and a function
> runs 2 queries and sums up the results. The execution paths of those queries
> have improved (5 gets/exec instead of 15 on average), but the query itself has
> gotten worse (100 gets/exec instead of 35-40 on avg) Since the underlying
> queries have improved (in function), I would assume that the query calling the
> function would improve as well. But obviously, it didn't. The only difference
> is that under CBO the query uses INLIST OPERATOR and under RBO it used
> CONCATENATION. I've looked around on groups and metalink, and it seems that u
> can disable using INLIST via some parameter in init.ora
> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=76141.1
> or via USE_CONCAT hint (not doable in our case, this query is all over the
> place).
> However, when I ran the query with sticking some values in it, the CBO way
> seemed better than RBO way
> 13 recursive calls/114 consistent gets/ 1 sorts (memory) (CBO)vs 185
> recursive calls/163 consistent gets/ 4 sorts (memory) (RBO)
>
> I am puzzled.. what should I do? This query is 40-50% of all load on the
> database server (replacing that function with a table is a separate issue)
>
> Here's the query:
> select
> NVL(sum(get_fulfillable_count(:var1,p.short_name)),0) from products p
> where
> p.short_name = 'p_ubb03_1' or
> p.short_name = 'p_ubb03_2' or
> p.short_name = 'p_ubb03_3'
>
> under RBO:
> 0 SELECT STATEMENTCost=
> 1 SORT
> 2 CONCATENATION
> 3 INDEX PRODUCTS_U1
> 4 INDEX PRODUCTS_U1
> 5 INDEX PRODUCTS_U1
>
> under CBO:
> 0 SELECT STATEMENTCost=1
> 1 SORT
> 2 INLIST ITERATOR
> 3 INDEX PRODUCTS_U1
>
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email

Inlist iterator was supposed to be faster and better optimized then the concatenation but there are some versions of oracle which have problems with the INLIST ITERATOR. A version that I know that had such a problem was 8.1.7.3 on HP-UX 11/64bit. As I am not using Solaris , I cannot tell much about your version. In case that oracle support confirms problems with INLIST, you can disable it. The way to do it is to put the following event into your init.ora:

 event="10157 trace name context forever, level 1"

THE STRONGEST POSSIBLE DISCLAIMER (and even a bit stronger then that):

Do not put events in your init.ora except if that was advised by oracle support. The only harmless event is 10046.

-- 
Mladen Gogala

Light travels faster than sound.
That is why some people appear bright
until you hear them speak.