Re: System Statistics and the CBO
- From: Patty.Charlebois@xxxxxxxxxxxxxx
- To: breitliw@xxxxxxxxxxxxx
- Date: Fri, 17 Jun 2005 9:56:56 -0400
Yes, that is the only thing that has changed. This is the test scenario:
- delete system statistics
- generate explain plan for query (uses x04claim_form)
- import system statistics that were gathered in production
- generate explain plan for query (uses x01claim_form)
I have tried gathering system statistics several times during the past week and
my stats always show mreadtim < sreadtim for our production system. Do you
think this means that the CBO will always ignore the system statistics and
therefore I shouldn't bother with them?
Thanks for you help,
Patty
Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> 2005/06/17 09:32 AM
To
Patty.Charlebois@xxxxxxxxxxxxxx
cc
oracle-l@xxxxxxxxxxxxx
Subject
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@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
----------------------------------------------------------
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
