Re: System Statistics and the CBO

Patty,

are you sure that is the only thing that changed? From all I know, your system statistics are ignored by the CBO because mreadtim < sreadtim. That is at odds with the CBO's ground rules that scattered reads have to be more "expensive" than sequential reads.

Of course, cardinality is only one aspect of an index' statistics

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:
>
> System Stats:
>
> PNAME PVAL1
> ------------------------------ ----------
> CPUSPEED 508
> MAXTHR 14344192
> MBRC 4
> MREADTIM .672
> SLAVETHR -1
> SREADTIM 3.244
>
> Without system stats:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=97)
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_DETAIL' (Cost=1 Card=1 Bytes=28)
> 2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=97)
> 3 2 NESTED LOOPS (Cost=2 Card=1 Bytes=69)
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_FORM' (Cost=1 Card=1 Bytes=39)
>
> 5 4 INDEX (RANGE SCAN) OF 'X04CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=1)
>
> 6 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM' (Cost=1 Card=1 Bytes=30)
> 7 6 INDEX (RANGE SCAN) OF 'XPKCLAIM' (UNIQUE) (Cost=1 Card=1)
> 8 2 INDEX (RANGE SCAN) OF 'XPKCLM_DETAIL' (UNIQUE) (Cost=1 Card=1)
>
> With system stats:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=97)
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_DETAIL' (Cost=2 Card=1 Bytes=28)
> 2 1 NESTED LOOPS (Cost=4 Card=1 Bytes=97)
> 3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=69)
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_FORM' (Cost=2 Card=1 Bytes=39)
>
> 5 4 INDEX (RANGE SCAN) OF 'X01CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=153)
>
> 6 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM' (Cost=2 Card=1 Bytes=30)
> 7 6 INDEX (RANGE SCAN) OF 'XPKCLAIM' (UNIQUE) (Cost=2 Card=1)
> 8 2 INDEX (RANGE SCAN) OF 'XPKCLM_DETAIL' (UNIQUE) (Cost=2 Card=1)
>
> Thanks,
>
> Patty

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l