| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: too low optimizer_index_cost_adj causing bizarre index choice
Jonathan,
And here is some more info in addition to what Jack said. This was discussed a while back but don't remember if you were involved. I know Waleed was. Anyway, here are some parts from the 10053 trace files:
OPTIMIZER_INDEX_COST_ADJ = 1
<<<Snip>>>
Access path: tsc Resc: 9696 Resp: 9696
<<<Snip>>>
Access path: index (no sta/stp keys)
INDEX#: 82652 TABLE: RENAMED_IT!!!
CST: 297 IXSEL: 1.0000e+00 TBSEL: 1.0000e+00
******** Bitmap access path accepted ********
Cost: 4924 Selectivity: 1
Note the CST for the index access alone is 297. Then, when accounting for the I/O to actually hit the table, the final cost for the bitmap was 4924, lower than the serial and parallel tablescan (tsc) of 9696.
OPTIMIZER_INDEX_COST_ADJ = 2
<<<Snip>>>
Access path: tsc Resc: 9696 Resp: 9696
<<<Snip>>>
Access path: index (no sta/stp keys)
INDEX#: 82652 TABLE: RENAMED_IT!!!
CST: 297 IXSEL: 1.0000e+00 TBSEL: 1.0000e+00
******** Bitmap access path rejected ********
Cost: 9849 Selectivity: 1
Now, notice the index and tsc costs are still the same, but the total cost including the overhead of getting the rows via an index has gone up to 9849 (doubling), more expensive that a table scan. And as you bump the parameter value up, you see the total cost for the index increment accordingly. So my question back then, using the first example above, was how does it derive the cost of 4924 from 297 for the index. Yes, I know the value for the parameter has the effect of doubling, tripling, quadrupling, etc the final values from the base value of the total cost for the index inclusive of hitting the table. It was rather clear, and as Waleed pointed out, the bitmap access cost moved in synch with the parameter value -- but was hard pressed to come up with how it got the base calculation before applying the value of optimizer_index_cost_adj to it. I'm sure if I went back through the docs and Metalink notes, I could finally come up with it. Just didn't have the time.
Regards,
Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jonathan
> Lewis
> Sent: Thursday, May 09, 2002 2:41 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: too low optimizer_index_cost_adj causing bizarre index
> choice
>
>
>
> There seems to be a perfectly good theoretical reason
> for this. But it would be interesting to know:
> Number of blocks below HWM
> Setting for db_file_multiblock_read_count
> Maximum usable value for db_file_mbrc
> The cost given by Explain Plan for the tablescan
>
> before I confuse the issue further by expounding a
> hypothesis that may be totally misleading.
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: 09 May 2002 14:30
>
>
> |Good morning listers,
> |
> |Some weeks ago I noticed a query plan that was using a
> |bizarre index choice and opened a TAR. Here is that
> |query:
> |
> |select DSS_EMPR.V_CLAIM_EMPR_HX.AMT_COPAY from
> |DSS_EMPR.V_CLAIM_EMPR_HX where
> |DSS_EMPR.V_CLAIM_EMPR_HX.BATCH_NUM between
> |to_date('2000-01-01','yyyy-mm-dd') and
> |to_date('2000-01-31','yyyy-mm-dd');
> |
> |Here is the query plan:
> |
> | SELECT STATEMENT (all_rows) Cost
> |(4924,636953,12739060)
> |
> | 1 0 1 2
> | TABLE ACCESS (analyzed) DSS_EMPR T_CLAIM_EMPR_HX
> |(by index rowid) Cost (4924,636953,12739060)
> |
> | 2 1 1
> | BITMAP CONVERSION (to rowids)
> |
> | 3 2 1
> | BITMAP INDEX DSS_EMPR X_CLAIM_EMPR_HX_N01
> |(full scan)
> |
> |
> |The index x_claim_empr_hx_n01 is on the phmcy_gid
> |column of the t_claim_empr_hx table.
> |
> |Now, here is the crux of the matter: phmcy_gid is
> |referred to nowhere in the query, not in select nor in
> |where nor even in order by.
> |
> |Worked through the tar with Oracle and they advised
> |that Oracle can and will cost ALL indexes during a
> |plan parse and eval, so it became a matter of
> |discovering why the index was being incorrectly
> |costed.
> |
> |Remembered that our optimizer_index_cost_adj was set
> |to 1 (don't ask). When I upped this value to 2 or more
> |and reran the query, it returned the appropriate FTS
> |plan.
> |
> |hth,
> |
> |Jack Silvey
> |
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: elkinsl_at_flash.net Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu May 09 2002 - 18:53:25 CDT
 |
 |