| 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: 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
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
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
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Larry
> Elkins
> Sent: Thursday, March 14, 2002 6:28 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Index Full Scan -- Strange Issue
>
>
> Waleed,
>
> Yeah, Ian's idea got me thinking. If you ask the question "When is it
more
> efficient to access *all* rows in a table via an index versus a full
table
> scan?", a HWM way up there and a small number of rows seems plausible.
And
> when someone asks "why does a full table scan take so long when
> there are no
> rows?" the first idea we all think of is the HWM. So it is
> logical to extend
> that thinking to the first question? So, what would happen if I create
a
> table with a few million rows and have an index on a column in the
table.
> Then, I delete all the rows, drop and re-create the index, analyze,
and
> issue a select * from table with no criteria? The index would be in
good
> shape and we would have the HWM way up there. Would an index full scan
be
> used? Can't complete the test -- I have to go to work and the delete
is
> still running :-(
>
> But it is something to consider and something I can come back to. The
goal
> here is to duplicate the case. And I now have some more question about
how
> the tables in question have been handled. This is fun!
>
> 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: Wednesday, March 13, 2002 11:48 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Index Full Scan -- Strange Issue
> >
> >
> > Larry,
> >
> > I think Ian's idea might be correct. When you have a HWM the
optimizer
> > will make its mind based on segments' sizes. Having high percentage
of
> > deleted rows and fragmented segment in the table, leads the
optimizer to
> > read te table indirectly using the index. Why this index and not the
> > others?
> > It could be also related to status of the index: Size, percentage of
> > deleted rows, clustering factor(very important), etc.
> >
> > Regards,
> >
> > Waleed
-- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: Waleed.Khedr_at_FMR.COM 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 Sat Mar 16 2002 - 19:08:20 CST
 |
 |