[Fwd: Re: System Statistics and the CBO]

For the "benefit" of those following this thread.
Patty had sent me 10053 traces privately and the attached is my response after looking at them.
Since I'm not 100% satisfied with my analysis, I am not sure how much benefit there is.

--
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--- Begin Message ---
  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: Patty.Charlebois@xxxxxxxxxxxxxx
  • Date: Fri, 17 Jun 2005 12:08:46 -0600
I have to speculate somewhat here but I believe it comes down to having optimizer_index_cost_adj=10 (i.e. 10% of index costs), rounding and what Jonathan so entertainingly demonstrated (with good_index and bad_index) in his Hotsos 2004 presentation and in his column in dbazine "Oh! I see a Problem" (http://www.dbazine.com/oracle/or-articles/jlewis18):

Without the system statistics, the index costing is as follows:

  Access path: index (equal)
      Index: X01CLAIM_FORM
  TABLE: CLAIM_FORM
      RSC_CPU: 0   RSC_IO: 13
  IX_SEL:  0.0000e+00  TB_SEL:  7.1429e-02
...
  Access path: index (scan)
      Index: X04CLAIM_FORM
  TABLE: CLAIM_FORM
      RSC_CPU: 0   RSC_IO: 3
  IX_SEL:  3.8341e-05  TB_SEL:  3.8341e-05
...

With the 10% cost reduction factor applied, the respective costs are
X01CLAIM_FORM: 1.3 rounded to 2.0
X04CLAIM_FORM: 0.3 rounded to 1.0

And thus X04CLAIM_FORM gets the nod:
  BEST_CST: 1.00  PATH: 4  Degree:  1

Unfortunately it doesn't identify which index.

With system statistics, the IO costs stay the same (because of mreadtim < sreadtim), but the cpu cost obviously factors in (which answers one question I had earlier in response to Mladen):

  Access path: index (equal)
      Index: X01CLAIM_FORM
  TABLE: CLAIM_FORM
      RSC_CPU: 279741   RSC_IO: 13
  IX_SEL:  0.0000e+00  TB_SEL:  7.1429e-02
...
  Access path: index (scan)
      Index: X04CLAIM_FORM
  TABLE: CLAIM_FORM
      RSC_CPU: 21475   RSC_IO: 3
  IX_SEL:  3.8341e-05  TB_SEL:  3.8341e-05
...
Nothing different from above. However, the bottom line, BEST_CST shows:

  BEST_CST: 2.00  PATH: 4  Degree:  1

So, apparently the cpu cost portion brought both index access costs above 1.0, both got rounded up to 2.0 and then the alphabetical ordering broke the tie in favour of X01CLAIM_FORM

You could try setting event 10183 to have fractional costs displayed. However, I doubt that is will add much more clarity.

To be frank, I am not 100% satisfied with my explanation, but it is the best I can think of at the moment. The IO cost of the X01 index is higher than that of the X04 index and the CPU cost is also more than 10 times higher than that of the X04 index. So how that would lift X04 over the 1.0 threshold without lifting X01 of the 2.0 threshold is something I don't understand.

You could try to explain the sql with and without system statistics and with the two optimizer_index parameters set at their default.

Patty.Charlebois@xxxxxxxxxxxxxx wrote:

Yes, I do:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
optimizer_index_caching              integer     99
optimizer_index_cost_adj             integer     10





Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
2005/06/17 01:10 PM

To
Patty.Charlebois@xxxxxxxxxxxxxx
cc

Subject
Re: System Statistics and the CBO






Patty,

the reason I had asked was that I saw the following in the trace without
stats which is not in the trace with stats:

******** Bitmap access path rejected ********
Cost: 3 Cost_io: 3 Cost_cpu: 0 Selectivity: 0


The traces you sent are different from the ones I'm used to. The entire
preamble of "PARAMETERS USED BY THE OPTIMIZER" and "BASE STATISTICAL
INFORMATION" is missing.

Another thing I noticed is that you have optimizer_index_caching set to
99. Do you also have set optimizer_index_cost_adj? It would show in the
parameters used section but that is missing.

Patty.Charlebois@xxxxxxxxxxxxxx wrote:


No, there are not.



Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> 2005/06/17 12:37 PM

To
Patty.Charlebois@xxxxxxxxxxxxxx
cc

Subject
Re: System Statistics and the CBO






Patty,

Are there any bitmap indexes on any of the tables involved?

Patty.Charlebois@xxxxxxxxxxxxxx wrote:



I have attached the winzip file containing the 2 traces, one with stats
and one without.
-- 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




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



--
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com


--- End Message ---

Other related posts: