Re: SQL Performance Problem

"crs_stat" <matt.southcott_at_gmail.com> wrote:
> I have 2 sql statements S1 and S2. S1 never actually finishes at least
> it exceeds our time slot so I never wiat for it to finish which is 30
> minutes. S2 runs in under a second usually in the area of .2 ish. So
> the problem has been overcome but I was wondering why. Why was it a
> problem in the first place. Stats were gathered on the objects in
> question. I just can't seem to understand why Oracle chose to access
> the table via xif26NC_PARAMS when the XIF12 index was far superior.

From your explain plans, it seems like the XIF26.. and the XIF12... indices aren't even on the same table, so I don't see how they could choose one over the other to access the table.

...
>
> S1S1S1S1S1S1S1S1S1S1S1S1S1S1S1
> select count(*)
> from EI$NC_PARAMS tab
> where exists (select object_id
> from nc_params
> where object_id = tab.object_id and
> attr_id = tab.attr_id
> )
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=44)
> 1 0 SORT (AGGREGATE)
> 2 1 NESTED LOOPS (SEMI) (Cost=2 Card=1 Bytes=44)
> 3 2 INDEX (FULL SCAN) OF 'XIF12NC_PARAMS_EI' (NON-UNIQUE)
> 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'NC_PARAMS' (Cost=1 C
> ard=6049584 Bytes=108892512)
>
> 5 4 INDEX (RANGE SCAN) OF 'XIF26NC_PARAMS' (NON-UNIQUE)
>
> S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2
> select count(*)
> from EI$NC_PARAMS tab
> where exists (select /*+ index(nc_params XIF12NC_PARAMS) */ object_id
> from nc_params
> where object_id = tab.object_id and
> attr_id = tab.attr_id
> )
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=19)
> 1 0 SORT (AGGREGATE)
> 2 1 NESTED LOOPS (SEMI) (Cost=2 Card=1 Bytes=19)
> 3 2 INDEX (FULL SCAN) OF 'XIF12NC_PARAMS_EI' (NON-UNIQUE)
> 4 2 INDEX (RANGE SCAN) OF 'XIF26NC_PARAMS' (NON-UNIQUE)
Your hint does not seem to be in force. You told it to use XIF12NC_PARAMS but it is not doing so, it is still using XIF26... to access the inner table. In fact, the only difference between plans is whether it has to visit the second table after going to the second table's index. It is hard to see how your hint would cause this difference.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB