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

"Spendius" <spendius_at_muchomail.com> 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) ?
>
> Maybe should I afford Jonathan Lewis' tutorial regarding cost-based
> optimisation... (not kidding)
>
> Thanks a lot.
> Sp

Hi Spendius,

OK, yeah attending a Jonathan Lewis seminar can only help (me, I'm still trying to work out how I can attend one for free ;)

Firstly, it might prove to be a bit more useful if you can post both your query and full execution plan.

Secondly, you say you have stats on these tables. How recent and how were they calculated (did you use analyze, did you use estimate, etc.)

"Classic" reasons for Oracle not using an index when it's sitting there lonely and looking for action include:

• The stats are either non existent, way too old, or calculated with an inappropriate sample (or estimate) value. If the stats don't accurately describe the current state of the table, what the optimizer does can best be described as unpredictable.
• The stats suggest the table is so "small" that it's simply more efficient to read it all in than use an index. This could be an incorrect assumption if the stats are old and what was a 100 row table is now a 10,000,000 row table. Also if the parameter DB_FILE_MULTIBLOCK_READ_COUNT is set too high, Oracle might consider it overly "cheap" to perform a full table scan
• The stats suggest that the selectively of the required rows is such that too many rows need to be retrieved via the index for it to be the cheapest option. This can be an incorrect assumption if Oracle incorrectly determines the selectively of the required rows. This is possible if the distribution of data is not evenly spread across all it's possible values or across it's range of values, as by default Oracle stats can only assume *even* distribution of data. This can be resolved by generating histogram statistics on your indexed columns which do not have even distribution characteristics.
• Oracle makes the assumption that indexes in general are more inefficient to use than may necessarily be the case. By default, Oracle assumes that reading an index block generates a physical read when in reality the vast majority of an index could already be cached. The parameter OPTIMIZER_INDEX_CACHING is set to 0 by default (effectively meaning zip caching) when perhaps the buffer cache hit rate (guys, please be gentle and flame me on low heat) might be a more appropriate value. Also Oracle assumes that the cost of an index I/O (one block) is exactly the same as a FTS I/O (multiple blocks). The parameter OPTIMIZER_INDEX_COST_ADJ who's default is 100(%) can be "tweaked" to more accurately reflect the comparative cost of an index physical I/O. Depending of disk hardware and the position of data within a disk, the extra time required to simply rotate the disk could be a significant factor and proportion of the overall I/O.
• It simply gets it wrong. Oracle is not exactly bug free and there have been a number of issues depending on database version when the optimizer just doesn't want to play ball. "Hints" are a technique that allow you to "prod" Oracle in the right direction.

Remember that Oracle "likes" full table scans because:

-They make use of multiblock reads
-They ensure that each block only needs to be read the once
-It can be performed in parallel
-It reduces stress in the buffer cache

So take the above on board and maybe the solution to your problems is somewhere in there.

Good Luck

Richard Received on Thu Jan 30 2003 - 07:59:14 CST