Posts
位图转换
在多个字段连接, 没有联合索引, 高基数(high-cardinality)的情况下, 可能会产生位图转换(bitmap conversion)
Jonathan Lewis "Cost-Based Oracle Fundamentals" P456:
B-tree to Bitmap Conversions
One of the optimizer’s strategies is to range scan B-tree indexes to acquire lists of rowids, convert the lists of rowids into the equivalent bitmaps, and perform bitwise operations to identify a small set of rows. Effectively, the optimizer can take sets of rowids from index range scans and convert them to bitmap indexes on the fly before doing an index_combine on the resulting bitmap indexes.
In 8i, only tables with existing bitmap indexes could be subject to this treatment, unless the parameter _b_tree_bitmap_plans had been set to relax the requirement for a preexisting bitmap index.
In 9i, the default value for this parameter changed from false to true—so you may see execution plans involving bitmap conversions after you’ve upgraded, even though you don’t have a single bitmap index in your database. Unfortunately, because of the implicit packing assumption that the optimizer uses for bitmap indexes, this will sometimes be a very bad idea.
As a related issue, this change can make it worth using the minimize_records_per_block option on all your important tables.
比如:
conn a/a
set autot off
drop table t1;
create table t1 as
select floor(dbms_random.value(1,90000)) a,
floor(dbms_random.value(1,50000)) b,
floor(dbms_random.value(1,10000)) c,
cast('1' as char(2000)) x,
'111111' aa
from dual
connect by level <= 100000;
create index ind_t1_a on t1(a);
create index ind_t1_b on t1(b);
create index ind_t1_c on t1(c);
analyze table t1 compute statistics for table for all columns for all indexes;
SQL> set autot trace exp stat
SQL> select aa from t1 where (a between 1000 and 3000 or a between 9010 and 9015) and ((b between 3000 and 7000) or (c between 3000 and 9000));
1428 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2150721541
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1429 | 24293 | | 1825 (1)| 00:00:22 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1429 | 24293 | | 1825 (1)| 00:00:22 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 3 | BITMAP AND | | | | | | |
| 4 | BITMAP OR | | | | | | |
| 5 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 6 | SORT ORDER BY | | | | | | |
|* 7 | INDEX RANGE SCAN | IND_T1_A | | | | 7 (0)| 00:00:01 |
| 8 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 9 | SORT ORDER BY | | | | | | |
|* 10 | INDEX RANGE SCAN | IND_T1_A | | | | 2 (0)| 00:00:01 |
| 11 | BITMAP OR | | | | | | |
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 13 | SORT ORDER BY | | | | 1896K| | |
|* 14 | INDEX RANGE SCAN | IND_T1_C | | | | 127 (0)| 00:00:02 |
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 16 | SORT ORDER BY | | | | 264K| | |
|* 17 | INDEX RANGE SCAN | IND_T1_B | | | | 19 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("A">=1000 AND "A"<=3000)
10 - access("A">=9010 AND "A"<=9015)
14 - access("C">=3000 AND "C"<=9000)
17 - access("B">=3000 AND "B"<=7000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1560 consistent gets
0 physical reads
0 redo size
25080 bytes sent via SQL*Net to client
1537 bytes received via SQL*Net from client
97 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1428 rows processed
SQL>
由多个字段索引取得的ROWID转换成位图, 然后进行与或操作, 最后转换回ROWID修改参数,禁止位图转换
alter session set "_b_tree_bitmap_plans"=false;
SQL> select aa from t1 where (a between 1000 and 3000 or a between 9010 and 9015) and ((b between 3000 and 7000) or (c between 3000 and 9000));
1428 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4246370027
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1301 | 22117 | 2071 (1)| 00:00:25 |
| 1 | CONCATENATION | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 4 | 68 | 8 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T1_A | 6 | | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1297 | 22049 | 2063 (1)| 00:00:25 |
|* 5 | INDEX RANGE SCAN | IND_T1_A | 2055 | | 7 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C">=3000 AND "C"<=9000 OR "B"<=7000 AND "B">=3000)
3 - access("A">=9010 AND "A"<=9015)
4 - filter("C">=3000 AND "C"<=9000 OR "B"<=7000 AND "B">=3000)
5 - access("A">=1000 AND "A"<=3000)
filter(LNNVL("A"<=9015) OR LNNVL("A">=9010))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2354 consistent gets
0 physical reads
0 redo size
25080 bytes sent via SQL*Net to client
1537 bytes received via SQL*Net from client
97 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1428 rows processed
SQL>
位图转换减少了一致性读(consistent gets)的次数, 但增加了一些内存排序(sorts (memory))奇怪的是, 如果WHERE条件中只查了一个字段, 也可能出现bitmap conversion
alter session set "_b_tree_bitmap_plans"=true;
set autot off
drop table t1;
create table t1 as
select level a,
cast('1' as char(2000)) x,
'111111' aa
from dual
connect by level <= 100000
order by dbms_random.value;
create index ind_t1_a on t1(a);
analyze table t1 compute statistics for table for all columns for all indexes;
SQL> set autot trace exp stat
SQL> select aa from t1 where a between 1 and 3 or a between 10 and 15;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 768713482
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 70 | 13 (16)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 7 | 70 | 13 (16)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 5 | SORT ORDER BY | | | | | |
|* 6 | INDEX RANGE SCAN | IND_T1_A | | | 2 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 8 | SORT ORDER BY | | | | | |
|* 9 | INDEX RANGE SCAN | IND_T1_A | | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A">=10 AND "A"<=15)
9 - access("A">=1 AND "A">=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
600 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9 rows processed
SQL>
用use_concat提示后变成SQL> select /*+use_concat*/ aa from t1 where a between 1 and 3 or a between 10 and 15;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4246370027
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 70 | 13 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 20 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T1_A | 2 | | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 5 | 50 | 8 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_T1_A | 5 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A">=1 AND "A"<=3)
5 - access("A">=10 AND "A"<=15)
filter(LNNVL("A"<=3) OR LNNVL("A">=1))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
600 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
有必要bitmap conversion吗, 不用它多好啊, 还能少2次内存排序外部链接:
Execution plan operation shows bitmap conversion from rowids
Optimization of large inlists/multiple OR`s
Using the USE_CONCAT hint with IN/OR Statements
Oracle Database 10g Performance Tuning Tips & Techniques By Richard J. Niemiec
NO_EXPAND Hint
Table 19-3 OPERATION and OPTIONS Values Produced by EXPLAIN PLAN
Sorry I did not phrase my question right
-fin-
No comments:
Post a Comment