Re: BITMAP index cost 10053 trace (some doubts)

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Sun, 5 Jun 2005 22:24:01 +0200
Message-ID: <08e901c56a0c$85cf3bf0$3c02a8c0@JARAWIN>

Hi Gopal,

I'm investigating a strange behavior with NL join to a partitioned table with bitmap index (probably similar to the bug 2423599 WRONG COST ESTIMATION WITH BITMAP INDEX, RANGE PARTITION AND NESTED LOOP). BTW the - not very intuitive thing is - on a full partition the bitmap index is used to access the inner table in NL, if the partition is additionally constrained, the bitmap index is *not* used.

I took a closer look on the bitmap index costing and I encountered some strange results.

> 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 new costing model, the cost will be (0.8*20) + (0.2*2000), which is
> 56 blocks.

SQL> select (0.8*20) + (0.2*2000) from dual;

(0.8*20)+(0.2*2000)

I don't thing it is only a typo. It is probably not very realistic to expect that accessing 200 rows (20% of 1000) will result in 400 table block access (20% of 2000 blocks).

My observation shows that this part of the cost formula (.2 * blocks) is valid only for larger count of selected rows; for smaller count there is some kind correction that lower the cost.

Secondly, I cannot verify the first part of the formula (selected rows * .8 / records_per_block), although a see a strong linear dependency on the selected row count. The test case below - very similar to yours (100.000 rows, 2000 blocks) - gives something like

selected rows * 2 / records_per_block (in my case selected rows * 24,81)

This means instead of 80% of "blocked" rows 200% are considered - this is not very intuitive to me.

In the test case I completely ignored the bitmap index cost, but as they will range between 1 and 9 (= level + LB) this is not an extraordinary error.

Any explanations?

Regards

Jaromir D.B. Nemec

http://www.db-nemec.com

Test case

SQL> @bitmap_cost

SQL> set pagesize 10000

SQL> set pause off

SQL> alter session set nls_language=english;

Session altered.

SQL> -- SQL> select value from v$parameter where name like '%block%size%';

VALUE

16384

SQL> select * from v$version;

BANNER

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

PL/SQL Release 9.2.0.5.0 - Production

CORE 9.2.0.6.0 Production

TNS for Linux: Version 9.2.0.5.0 - Production

NLSRTL Version 9.2.0.5.0 - Production

SQL> -- define table with 100.000 rows in 2000 blocks

SQL> --- SQL> drop table x;

Table dropped.

adjust the rpad for other block sizes

SQL> create table x

2 pctfree 0

3 as

4 select mod(rownum , 100) a, rpad('x',307) b

5 from dual connect by 1=1 and level < 100001;

Table created.

SQL> -- SQL> alter table x minimize records_per_block;

Table altered.

I guess with the minimize I adjust the 50 row per block, but without it the results are not really different

SQL> -- SQL> create bitmap index x_ix1 on x(a);

Index created.

SQL> --- SQL> begin

2 dbms_stats.gather_table_stats

3 ( ownname => user,

4 tabname => 'x',

5 cascade => TRUE );

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> --- SQL> select num_rows, blocks from dba_tables where table_name = 'X' and owner = 'NEMECJ';

NUM_ROWS BLOCKS

----------

100000 1995

SQL> -- SQL> select blevel, leaf_blocks from dba_indexes where table_name = 'X' and owner = 'NEMECJ';

BLEVEL LEAF_BLOCKS

-----------

1 8

SQL> ---- explain plan

SQL> delete from nemecj.plan_table a

2 where statement_id like 'N%';

400 rows deleted.

select 1000 rows via bitmap index

SQL> EXPLAIN PLAN set statement_id = 'N1' into nemecj.plan_table FOR select /*+ INDEX(x)*/ * from x where a <= 1;

Explained.

select 2000 rows via bitmap index

SQL> EXPLAIN PLAN set statement_id = 'N2' into nemecj.plan_table FOR select /*+ INDEX(x)*/ * from x where a <= 2;

Explained.

rows 3 - 95 deleted --

SQL> EXPLAIN PLAN set statement_id = 'N96' into nemecj.plan_table FOR select /*+ INDEX(x)*/ * from x where a <= 96;

Explained.

SQL> EXPLAIN PLAN set statement_id = 'N97' into nemecj.plan_table FOR select /*+ INDEX(x)*/ * from x where a <= 97;

Explained.

SQL> EXPLAIN PLAN set statement_id = 'N98' into nemecj.plan_table FOR select /*+ INDEX(x)*/ * from x where a <= 98;

Explained.

SQL> EXPLAIN PLAN set statement_id = 'N99' into nemecj.plan_table FOR select /*+ INDEX(x)*/ * from x where a <= 99;

Explained.

SQL> EXPLAIN PLAN set statement_id = 'N100' into nemecj.plan_table FOR select /*+ INDEX(x)*/ * from x where a <= 100;

Explained.

SQL> --- get the count of selected row, bitmap index (and table) cost and calculate the difference

SQL> select to_number(substr(statement_id,2))*1000 rowset, cost,

2 -- costs difference

3 round(to_number(substr(statement_id,2))*1000 / 24.81 + 0.2 * 1995) - cost cost_diff

4 from nemecj.plan_table a

5 where statement_id like 'N%' and operation = 'SELECT STATEMENT'

6 order by to_number(substr(statement_id,2));

ROWSET COST COST_DIFF

---------- ----------

1000 338 101

2000 436 44

3000 511 9

4000 572 -12

5000 624 -23

6000 672 -31

7000 716 -35

8000 758 -37

9000 800 -38

10000 841 -39

11000 881 -39

12000 922 -39

rows deleted here --

83000 3767 -23

84000 3807 -22

85000 3847 -22

86000 3888 -23

87000 3928 -22

88000 3968 -22

89000 4008 -22

90000 4048 -21

91000 4088 -21

92000 4128 -21

93000 4168 -21

94000 4208 -20

95000 4248 -20

96000 4288 -20

97000 4328 -19

98000 4368 -19

99000 4368 21

100000 4368 62

100 rows selected.

SQL> -- and quit

SQL> quit;

Original Message ----- From: "K Gopalakrishnan" <kaygopal_at_gmail.com> To: <breitliw_at_centrexcc.com> Cc: <oracle-l_at_freelists.org>; <oradebug_at_freelists.org> Sent: Tuesday, May 24, 2005 9:20 PM Subject: Re: BITMAP index cost 10053 trace

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jun 05 2005 - 16:34:15 CDT