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

spendius_at_muchomail.com (Spendius) wrote in message 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) ?
>

Unfortunately both documentation and books forget to explain the limitations of CBO. Per my experience (and I expect to receive lots of angry responses, but my experience is confirmed by lots of other people's experiences) CBO plain doesn't work if you use more than 7 tables in your query. In case if you use connect by somewhere in the query, forget about CBO completely. In your case, there are definitely more than 7 tables in the query, so you have only next choices:

1. If RULE hint works for you, stop here.
2. If you can get decent performance using FIRST_ROWS hint, so be it.
3. If neither 1 nor 2 works for you, you need to tune statement manually, fixing the execution plan with hints.

Attention! Solutions 2 and 3 are instance specific, so if you have decent performance on the test instance it doesn't mean good performance on production instance.

Last but not least, increase optimizer_max_permutations parameter in init.ora to the highest value available (AFAIK 80000 for 8.1.7).

HTH
> Maybe should I afford Jonathan Lewis' tutorial regarding cost-based
> optimisation... (not kidding)
>
> Thanks a lot.
> Sp
Received on Thu Jan 30 2003 - 10:58:03 CST