Re: System Statistics and the CBO

On 06/17/2005 08:29:56 AM, Patty.Charlebois@xxxxxxxxxxxxxx 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

Other related posts: