Re: System Statistics and the CBO

From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
To: Patty.Charlebois@xxxxxxxxxxxxxx
Date: Fri, 17 Jun 2005 07:32:17 -0600

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@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:

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
--
//www.freelists.org/webpage/oracle-l

Follow-Ups:
- Re: System Statistics and the CBO
  - From: Mladen Gogala

References:
- System Statistics and the CBO
  - From: Patty . Charlebois

Re: System Statistics and the CBO

Other related posts: