From oracle-l-bounce@freelists.org Tue May 24 15:25:04 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4OKP4in010286 for ; Tue, 24 May 2005 15:25:04 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j4OKP4Ni010281 for ; Tue, 24 May 2005 15:25:04 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ACE751B233A; Tue, 24 May 2005 14:22:07 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08625-10; Tue, 24 May 2005 14:22:07 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0C3A01B2338; Tue, 24 May 2005 14:22:07 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=qMTqfHabqX41rIbHvqHgKbrAhyScf01+HxyR3IjfiBYrYzY0SEaxDwBpZ2ILbYeHDRz4BV/g2cQgssiwBz2q1gITNVfkd04em5HP1RkghRVlmkdNvo6cNi203nGDhKsBnZe9kcdBoggfXyqnctng0yR8u9dN9o0vwd3ukJwkAIo= Message-ID: <3b0f44a105052412202833d2df@mail.gmail.com> Date: Wed, 25 May 2005 00:50:18 +0530 From: K Gopalakrishnan To: breitliw@centrexcc.com Subject: Re: BITMAP index cost 10053 trace Cc: "Oracle-L (oracle-l@freelists.org)" , oradebug@freelists.org In-Reply-To: <4293334A.7000108@centrexcc.com> Mime-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Content-Disposition: inline References: <4127F81F6CAFC245A18BC49054EFB06301D6E249@ssslexchusr6.sssl.bskyb.com> <4293334A.7000108@centrexcc.com> X-archive-position: 20205 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: kaygopal@gmail.com Precedence: normal Reply-To: kaygopal@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=none autolearn=ham version=2.63 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 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/ -- http://www.freelists.org/webpage/oracle-l