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 -> SQL Performance Problem

SQL Performance Problem

From: crs_stat <matt.southcott_at_gmail.com>
Date: 2 Feb 2006 09:57:43 -0800
Message-ID: <1138903063.138112.75440@g49g2000cwa.googlegroups.com>


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 -

)
Received on Thu Feb 02 2006 - 11:57:43 CST

Original text of this message

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