The optimizer makes me *desperate*...

From: Spendius <spendius_at_muchomail.com>
Date: 29 Jan 2003 07:34:11 -0800
Message-ID: <aba30b75.0301290734.3ae5894a@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:34:11 CST