Re: [Fwd: Re: System Statistics and the CBO]
- From: Patty.Charlebois@xxxxxxxxxxxxxx
- To: breitliw@xxxxxxxxxxxxx
- Date: Sun, 19 Jun 2005 5:57:54 -0400
Wolfgang,
You were correct in your speculation about optimizer_index_cost_adj=10
causing the change in plan. I set optmizer_index_cost_adj=100 (the
default) and explained my query with system statistics loaded and it is
now using the correct index (x04claim_form) instead of the high card index
(x01claim_form).
Thank-you for all of your help!
Patty
Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
2005/06/17 03:07 PM
Please respond to
breitliw@xxxxxxxxxxxxx
To
oracle-l@xxxxxxxxxxxxx, Mladen Gogala <mgogala@xxxxxxxxxxxxxxxxxxxx>
cc
Subject
[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
----- Message from Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> on Fri, 17
Jun 2005 12:08:46 -0600 -----
To:
Patty.Charlebois@xxxxxxxxxxxxxx
Subject:
Re: System Statistics and the CBO
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
----------------------------------------------------------
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:
