RE: Index Full Scan -- Strange Issue

Waleed,

Right, the pattern in the way the value increases as we bump up optimizer_index_cost_adj is readily apparent, but, that still doesn't tell me *how* the final value (4924, 9849, and so on) with 1, 2, or whatever is calculated. In other words, how did 297 cost for the index full scan become 4924 total cost when set at 1? I assume because there is some factoring in of then going and getting each row in the table. Working the full trace and various combination of numbers I never came up with a correlation, but I didn't spend a whole lot of time on it since it would be a fact hardly worth knowing, just a case of curious minds wanting to know ;-)

Thanks for your responses.

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 Khedr,
> Waleed
> Sent: Saturday, March 16, 2002 7:08 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Index Full Scan -- Strange Issue
>
>
> It's clear that 9849 is almost twice 4924.29 when the parameter got
> doubled. I think if you leave it at the default value (100) the
> cost will be
>
> 100 * 4924.29 = 50 * 9849 = 492450
>
> Regards,
>
> Waleed
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L
> Sent: 3/16/02 7:48 AM
>
> Been a it busy but am following up. Waleed was on target with the
> question
> on the optimizer_index_cost_adj (I owe you a beer, or your favorite
> beverage). This particular DB has optimizer_index_cost_adj set to 1. I
> originally said I thought it was 30 or 40, not sure why so low but there
> is
> some history there about why they did that.
>
> Recap. Assume a table has 30 columns. 3 not null single column BMI's
> exist
> on columns A, B, and C, not on D. Here is a sample query:
>
> Select *
> From Table
> Where D='ABCDE'
>
> Note that there is no index on D, but, there are on columns A, B, and C.
> However, no criteria specified for any of those columns. The CBO chose
> to do
> a full index scan using the BMI on indexed column A, then getting each
> row
> from that BMI and filtering on column D criteria. I have yet to be able
> to
> duplicate in a test environment but understanding how a particular value
> is
> derived, touched on later, would help me get there.
>
> We know the optimizer_index_cost_adj is used to tilt the scales either
> way
> regarding full table scan or index usage, but I would have assumed
> (yeah, I
> know ;-)) that this *only* applied when making the decision between
> using an
> index with criteria for the index versus a full table scan. Wrong! For
> example, if the WHERE clause had been "B=1", an indexed column, I could
> see
> the value for optimizer_index_cost_adj impacting the calculated cost.
> But in
> the case above with no criteria on an index, index access paths were
> still
> considered (10053 trace shows them being considered). And, with the
> value
> for optimizer_index_cost_adj at 1, the index access path was chosen. Set
> to
> 2, full table scan was chosen.
>
> Here are some snippets from the 10053 trace files showing this.
>
> Value of 1 for optimizer_index_cost_adj:
>
> <<<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
> Not believed to be index-only.
> BEST_CST: 4924.29 PATH: 20 Degree: 1
>
> Table scan cost of 9696. The CST of the full index scan was calculated
> at
> 297. Then, the total cost bumped to 4924. Includes additional cost for
> hitting the table I assume but how is this number derived? Value of it
> bumps
> higher and higher as the value of the optimizer_index_cost_adj is
> increased
> as illustrated in the following.
>
> Value of 2 for optimizer_index_cost_adj:
>
> <<<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
> Not believed to be index-only.
> BEST_CST: 9696.00 PATH: 2 Degree: 1
>
> The index cost is still 297, but the calculated cost is now 9849, more
> than
> the table scan cost of 9696. As you continue to bump up the value for
> optimizer_index_cost_adj, you see the index cost stay the same, but that
> final cost for the index based access to continue to rise. Anyone know
> that
> calculation? I left out major parts of the 10053 trace but I have been
> trying to come up with some correlation between various values for how
> that
> 4924 and 9849 got calculated. No luck so far.
>
> Regards,
>
> Larry G. Elkins
> elkinsl_at_flash.net
> 214.954.1781

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