Re: The optimizer makes me *desperate*...

Hi Spendius,

we have seen a similar problem when we migrated from 8.0.6 to 8.1.7 R3 last year.

Among those many possible reasons, two might be *too old* statistics or missing optimizer hints ...
maybe You would have to rebuild some indexes and/or (only) analyze them.

When we migrated, we saw then most of the apps going normal, only a few became
much slower - we then set some optimizer hints, and in three cases we rewrote the queries,
since we could not find out exactly why this or that was going so badly, just because of migration and having nothing else changed. So we came to the conclusion that there must have been a change inside the Oracle parser and/or the optimizer.
So we have to deal with it anyway.

I too would be much interested into some details from the gurus knowlegde ...

hth a little ...

Jan

"Spendius" <spendius_at_muchomail.com> schrieb im Newsbeitrag news:aba30b75.0301290734.3ae5894a_at_posting.google.com...
> Hi,
> We had a DB on an NT server, in 8.0.6. I've just migrated
> it to 8.1.7.1 on Sun/Solaris. Since then, the users do not
> cease from complaining about the slowlyness of the app etc
> etc.
> The whole stuff behaves slightly better if I remove the
> statistics from the dictionary, and this is precisely what
> I do not understand. One of the users gave me a huge query
> to investigate, and I noticed the following: if in my SQL*Plus
> session I set AUTOT TRACE EXP and run it and spool it in
> b2exp.lst, here is what I get:
>
> $ grep "TABLE ACCESS (FULL" b2exp.lst
> 11 10 TABLE ACCESS (FULL) OF 'VEHICLE' (Cost=13 Card=1 Bytes=26)
> 20 19 TABLE ACCESS (FULL) OF 'ELEMENT_INTERVENTION'
> (Cost=238 Card=254 Bytes=2032)
> 21 19 TABLE ACCESS (FULL) OF 'DOSSIER' (Cost=149
> Card=54966 Bytes=384762)
> 22 16 TABLE ACCESS (FULL) OF 'DOSSIER' (Cost=149
> Card=54966 Bytes=384762)
> 29 28 TABLE ACCESS (FULL) OF 'RENTING' ( Cost=8 Card=249
Bytes=3735)
> 30 28 TABLE ACCESS (FULL) OF 'NOTE' (Cost=7 Card=4493 Bytes=49423)
> 31 27 TABLE ACCESS (FULL) OF 'VEHICLE_REQUEST' (Cost=3
> Card=3216 Bytes=35376)
> 32 26 TABLE ACCESS (FULL) OF 'CONTACT' (Cost=5 Card=3079 Bytes=200135)
> 35 34 TABLE ACCESS (FULL) OF 'KM_TYPE' (Cost=1 Card=3 Bytes=18)
> 39 38 TABLE ACCESS (FULL) OF 'KM' (Cost=183 Card=284693
Bytes=1992851)
> 40 36 TABLE ACCESS (FULL) OF 'KM' (Cost=183 Card=14235 Bytes=398580)
> 51 50 LE ACCESS (FULL) OF 'PLATE_NUMBER_VEH' (Cost=3 Card=1 Bytes=9)
>
> If I delete the statistics from the schema, and run it under
> AUTOTRACE TRACE ONLY EXP once again by spooling it in b2exp2.lst (i.e.
> if I place myself under a RULE-based optimizer), here is what I get:
> $ grep "TABLE ACCESS (FULL" b2exp2.lst
> 12 11 TABLE ACCESS (FULL) OF 'VEHICLE_CATE GORY'
> 24 23 TABLE ACCESS (FULL) OF 'CONTACT'
> 37 36 TABLE ACCESS (FULL) OF 'KM'
> 62 61 TABLE ACCESS (FULL) OF 'VEHICLE'
> 66 65 TABLE ACCESS (FULL) OF 'PLATE_NUMBER_V EH'
>
> So without statistics, i.e. when I'm in RULE, the performances are
> HUNDREDS of times better, but I keep reading that customers should
> give up RULE and that it's being phased out etc etc.
>
> Can you *please* help me ?
>
> My questions:
> 1/ can someone explain me *what makes* the optimizer not use the
> indexes when the statistics are present;
> 2/ what could I read to understand the optimizer's behaviour (though in
> this case I should talk about the optimizer's demeanour) ?
>
> Maybe should I afford Jonathan Lewis' tutorial regarding cost-based
> optimisation... (not kidding)
>
> Thanks a lot.
> Sp
Received on Wed Jan 29 2003 - 09:50:04 CST