Re: System Statistics and the CBO

I gathered system statistics over during a typical day and stored them in a table as I do not want them 'turned on' until I have done some testing and can ensure that they will not negatively impact our production OLTP system.

I then imported the system statistics that I gathered from production into our test system and compared plans before and after the system stats were imported. The production and test server are the exact same hardware and database.

Where would I find the default values for our system?

Mladen Gogala <gogala_at_sbcglobal.net> Sent by: oracle-l-bounce_at_freelists.org 2005/06/17 08:52 AM
Please respond to
gogala_at_sbcglobal.net

To
Patty.Charlebois_at_greenshield.ca
cc
oracle-l_at_freelists.org
Subject
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







----------------------------------------------------------

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