System Statistics and the CBO

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  

This e-mail is confidential, intended solely for the use of the recipient(s) to whom it was addressed. If you have received it in error, please do not copy or distribute this e-mail. We ask that you notify us immediately by replying to the sender and then delete this e-mail. E-mail sent or received over the internet may not be secure. You should use caution when sending e-mail messages containing private and confidential information or consider other secure means to send the information. If you have any questions regarding the authenticity or security of e-mail you have received from Green Shield Canada please do not hesitate to contact us at 1-800-265-5615.

Please visit us at our website: http://www.greenshield.ca

Ce message de courriel est confidentiel et s’adresse uniquement à la personne ou à l’organisme indiqué(e). Si vous l’avez reçu par erreur, veuillez ne pas copier ni distribuer ce message de courriel. Nous vous demandons de nous aviser immédiatement en répondant à l’expéditeur, puis en supprimant ce message. Les messages envoyés ou reçus par courriel pourraient ne pas être sécurisés. Vous devriez être prudent lorsque vous envoyez des messages de courriel contenant des renseignements confidentiels et privés ou songer à prendre d’autres moyens sécurisés pour envoyer les renseignements. Si vous avez des questions concernant l’authenticité ou la sécurité d’un courriel que vous avez reçu de Green Shield Canada, n’hésitez pas à communiquer avec nous au 1-800-265-5615.

Visitez notre site Web: http://www.greenshield.ca

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jun 17 2005 - 08:35:44 CDT