Re: BITMAP index cost 10053 trace (some doubts)
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <kaygopal@xxxxxxxxx>
- Date: Sun, 5 Jun 2005 22:24:01 +0200
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)
-------------------
416
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@xxxxxxxxx>
To: <breitliw@xxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>; <oradebug@xxxxxxxxxxxxx>
Sent: Tuesday, May 24, 2005 9:20 PM
Subject: Re: BITMAP index cost 10053 trace
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re: BITMAP index cost 10053 trace (some doubts)
