Re: BITMAP index cost 10053 trace
- From: K Gopalakrishnan <kaygopal@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Wed, 25 May 2005 00:50:18 +0530
Wolfgang:
I don't want to speculate on Jonathan's book. But bitmap index costing
algorithms have changed from 8i onwards and there is an event to
disable that (or change to old costing algorithms). We have a
discussion about that in the Oracle Wait Interface book, Appendix -A.
Here is the snipped, for those who don't have the book handy :)
Since change behavior events are used to enable or disable certain
features of Oracle RDBMS, they cause potential data loss or data
corruption if used incorrectly. They can also be used to change Oracle
kernel operations. For example, the event 10170 can be used to change
the bitmap index costing algorithms.
Bitmap index access cost is calculated as the sum of the index access
cost and table access cost for those blocks. Here index access cost
will be a function of blevel and the number of leaf blocks containing
the key. Once the rowids are fetched from the leaf blocks, the bitmap
is constructed and table access cost is estimated. The table access
cost is calculated based on the number of blocks to fetch to get all
the keys. This mainly depends on the selectivity.
In the old costing model, it is assumed that all the keys could be
found in same block. That is, the number of block visits is calculated
by dividing the number of rows by the rows per block. So the number of
blocks multiplied by the selectivity of a block is assumed to be equal
to the total number of rows satisfying the condition.
For example, if you want to retrieve 100 rows from a table that has
10,000 blocks with an average of 10 rows per block, the old costing
model will compute 10 blocks as the I/O cost (100 rows/10 rows per
block). So the table access I/O cost is approximately 10. With the
enhanced costing model, it assumes 80 percent of the rows are in the
same block and the remaining 20 percent of the rows are split across
all blocks (which is quite possible). In this case, the cost will be
(0.8*100/10) + (0.2*10000) =3D 8+2000 =3D 2008. The difference is quite
significant.
Let us take another case where an index has 100,000 rows from a table
with an average of 50 rows per block (for a total of 2,000 blocks) and
the result set expects 1,000 rows. In the old model, the I/O cost will
be 20. In the new costing model, the cost will be (0.8*20) +
(0.2*2000), which is 56 blocks.
In most cases, the enhanced (new) costing model, which is based on the
Watkins formula, works reasonably well in estimating the bitmap access
costs. However, the old model outperforms the new model when
partitioned tables are used and when star transformation is used. In
these cases, most of the rows (or all of them) will be from the same
partition, and the old costing model will be more suitable than the
new enhanced costing approach. Sometimes the new costing model
artificially inflates the bitmap index cost where bitmap indexes could
be used for transformation. The old costing mode can be enabled by
setting the event 10170.
On 5/24/05, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
> I have high hopes that Jonathan Lewis' upcoming book will shed loght on
> this.
>=20
> Barr, Stephen wrote:
> > Is the formula available anywhere?
> >
> >>If the formula is blevel + FF*leaf_blocks + FF*clustering_factor then t=
he
> >>cost comes out as 11 - how does it then jump to 9131? I'm assuming the =
cost
> >>calculationfor bitmap indexes is very different?
> >
> > I'm assuming you are right. :-)
> >
> --
> Regards
>=20
> Wolfgang Breitling
Best Regards,
K Gopalakrishnan=20
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
