Re: System Statistics and the CBO

On 06/17/2005 08:29:56 AM, Patty.Charlebois_at_greenshield.ca wrote:
> I am just now getting around to implementing system statistics in my 9.2.0.6 database that runs on an IBM P650 under AIX 5L.
>
> I have found a handful of explain plan differences with and without system statistics and am trying to understand why the plan changes when I import the system statistics. Can someone explain to my why, once I import my system statistics, the CBO chooses a index that has a higher cardinality (X01CLAIM_FORM instead of X04CLAIM_FORM) for the following query:

The difference in the plans is choice of single index:

 INDEX (RANGE SCAN) OF 'X04CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=1)

vs.

 INDEX (RANGE SCAN) OF 'X01CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=153)

To have any idea why is that, I should know the default values for your system, which I don't. Because of larger cardinality, I somehow think that the latter has larger MBRC, which makes multiblock reads cheap enough rot the CBO to choose the latter plan. Have you ever tried gathering system statistics? Importing it doesn't seem like a very good idea to me, because system stats are supposed to calibrate your system. If you import system stats, you are using a calibration from another system.

-- 
Mladen Gogala
Oracle DBA


--
http://www.freelists.org/webpage/oracle-l