Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> SQL Performance Problem
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.
The costs are very similar which is also a mystery since one is very
good and one is obvisouly very bad. Anyone have any ideas?
Thanks
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)
Stats Gathering Script
exec dbms_stats.gather_schema_stats( -
ownname => 'psdata1', - options => 'GATHER AUTO', - estimate_percent => dbms_stats.auto_sample_size, - method_opt => 'for all columns size auto', - degree => 15 -