Strange optimizer problem

Hi all,

Since our 8.0.4 to 8.1.7.4 upgrade (HPUX) we are seeing strange slow downs in some queries that I have no explanation for with my modest Oracle Server knowledge. Some of the queries went from a few seconds to 20 mins. I have done a fresh ANALYZE (with 10%) on both tables involved, which improved some queries, but by no means all.

I do not have an exact DESC of the tables, but can get it if needed.

Fast Query (f_docnumber is primary key on doctaba, m.dok_is is a float. DB was not designed by us)

select m.dok_id from doctaba d, vis.vis_p501_mlt m where (d.f_docnumber = m.dok_id) and (m.vi_k_nr like '4%') and d.a35 = 12474 and d.a33<>'HISTORIE'

Gives the following (correct and efficient) strategy: Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15741 Card=3917 Byte
          s=113593)

   1    0   NESTED LOOPS (Cost=15741 Card=3917 Bytes=113593)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DOCTABA' (Cost=73 Card


          =3917 Bytes=39170)


   3    2       INDEX (RANGE SCAN) OF 'DA_A35' (NON-UNIQUE) (Cost=10 C
          ard=3917)

   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'VIS_P501_MLT' (Cost=4
          Card=126875 Bytes=2410625)

   5    4       INDEX (RANGE SCAN) OF 'VIS_P501_IX_MLT_DOK_ID' (NON-UN
          IQUE) (Cost=3 Card=126875)

Slow Query (note that the ONLY difference to above query is the number of digits specified in m.vi_k_nr like '45481812%)

select m.dok_id from doctaba d, vis.vis_p501_mlt m where (d.f_docnumber = m.dok_id) and (m.vi_k_nr like '45481812%') and d.a35 = 12474 and d.a33<>'HISTORIE'

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=593 Card=679 Bytes=1
          9691)

   1    0   HASH JOIN (Cost=593 Card=679 Bytes=19691)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'VIS_P501_MLT' (Cost=51
          9 Card=679 Bytes=12901)

   3    2       INDEX (RANGE SCAN) OF 'VIS_P501_IX_MLT_VI_K_NR' (NON-U
          NIQUE) (Cost=18 Card=679)

   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'DOCTABA' (Cost=73 Card


          =3917 Bytes=39170)


   5    4       INDEX (RANGE SCAN) OF 'DA_A35' (NON-UNIQUE) (Cost=10 C
          ard=3917)

So instead of making an index access on doctaba and then joining with VIS_P501_MLT, it does an squential index scan on VIS_P501_MLT and then do the join on doctaba. Cardinality of doctaba is approx. 70 mill rows, VIS_P501_MLT is approx. 6 mill rows.

I'm totally stumped, but am sure there is such a simple explanation that I will slap on my forhead. And then the question: How to fix this ?

Thanks ver much

/Gerold Received on Wed Mar 03 2004 - 07:46:50 CST