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 ---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):
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Patty.Charlebois@xxxxxxxxxxxxxx
- Date: Fri, 17 Jun 2005 12:08:46 -0600
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 ---
