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

Gerold,
The difference between the slow and the fast query is that the driving
table has changed.
This is because of the difference in cardinality estimates between the
clauses,
m.vi_k_nr like '45481812%' and m.vi_k_nr like '4%'.
If you dont have column level statistics, the optimizer estimates the
selectivity of like 'A%' (NDV/72) is more than that of like 'AB%'
(NDV/9342).
Likewise the selectivity of like 'AB%' is more than that of like
'ABC%' (NDV/19044).
I have seen that the selectivity remains the same after 3 characters
or more.
i.e selectivity of like 'ABC%' is same as like 'ABCD%'

Also if you dont have column level statistics, the clauses like 'A%'
and like 'B%' are considered the same.
So in your case the optimizer is changing the driving table due to the
selectivity and hence cardinality changes for the table VIS_P501_MLT
due to the clause m.vi_k_nr like '45481812%.

Try getting column level statistics for the column vi_k_nr in the
table VIS_P501_MLT and see if the plan changes.
regards
Srivenu

I agree with Srivenu.

The plan has changed because the computed cardinality estimate will be
much more attractive with the m.vi_k_nr like '45481812%' predicate.
It has nothing to do with upgrading your instance.

I would like to add one more thought...

Because the selectivity of the slower query is much better now, the
access plan may actually be correct. Your problem may be that your
are running out of hash memory. (Check to make sure you have enough
memory allocated to hash_area_size.)

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

srivenu@hotmail.com (srivenu) wrote in message news:<1a68177.0403040105.68bf83b2@posting.google.c om>...
> Gerold,
> The difference between the slow and the fast query is that the driving
> table has changed.
> This is because of the difference in cardinality estimates between the
> clauses,
> m.vi_k_nr like '45481812%' and m.vi_k_nr like '4%'.
> If you dont have column level statistics, the optimizer estimates the
> selectivity of like 'A%' (NDV/72) is more than that of like 'AB%'
> (NDV/9342).
> Likewise the selectivity of like 'AB%' is more than that of like
> 'ABC%' (NDV/19044).
> I have seen that the selectivity remains the same after 3 characters
> or more.
> i.e selectivity of like 'ABC%' is same as like 'ABCD%'
>
> Also if you dont have column level statistics, the clauses like 'A%'
> and like 'B%' are considered the same.
> So in your case the optimizer is changing the driving table due to the
> selectivity and hence cardinality changes for the table VIS_P501_MLT
> due to the clause m.vi_k_nr like '45481812%.
>
> Try getting column level statistics for the column vi_k_nr in the
> table VIS_P501_MLT and see if the plan changes.
> regards
> Srivenu