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

Alex Filonov wrote:

> 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

1. Of what value will RULE be after 9i?
2. Of what value is FIRST_ROWS in 9i?
3. Ok

Daniel Morgan Received on Thu Jan 30 2003 - 13:00:29 CST