玉面飞龙的BLOG

So does BITMAP_MERGE_AREA_SIZE

June 20th, 2007 | Categories: Boring | Tags:

和sort_area_size，hash_area_size一样，mts下这些work area都分配在large pool中（除了10g的automatic pga management），需要shared pool latch的保护。

排序要sort_area_size，hash join要hash_area_size，bitmap index operation也要BITMAP_MERGE_AREA_SIZE。

从下面的测试可以看到, BITMAP CONVERSION 需要在UGA中分配额外的空间；MTS的话，用来将索引ROWID转换为BITMAP的内存空间将从large pool中分配。同理，有shared pool latch contention的可能性。

SQL> conn binzhang/binzhang@MTS9205
Connected.

create table test as select * from dba_objects;

update test set owner='BINZHANG' where mod(object_id,2)=0;

update test set object_name='BINZHANG' where mod(object_id,2)=1;

create index ind_test_owner on test(owner);

create index ind_test_object_name on test(object_name);

先创建测试表

SQL> select name,value from v$mystat m,v$statname s where
   (name like '%uga%' or name like '%pga%') and m.STATISTIC#=s.STATISTIC# ;

NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 111912
session uga memory max 111912
session pga memory 1326176
session pga memory max 4692040


SQL> select/*+ index_combine(test ind_test_owner ind_test_object_name)*/ count(*) from
test where owner = 'SYS' and object_name = 'BINZHANG';

COUNT(*)
----------
   70208

SQL> select name,value from v$mystat m,v$statname s where
   (name like '%uga%' or name like '%pga%') and m.STATISTIC#=s.STATISTIC# ;
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 111448
session uga memory max 240312
session pga memory 1326176
session pga memory max 4692040

可以看到session uga memory max有大约100k的增加。

这是因为使用了BITMAP相关的操作，当然也有SORT(AGGREGATE)

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=RULE (Cost=8652 Card=1 Bytes=19)
   1 0 SORT (AGGREGATE)
   2 1 BITMAP CONVERSION (COUNT)
   3 2 BITMAP AND
   4 3 BITMAP CONVERSION (FROM ROWIDS)
   5 4 INDEX (RANGE SCAN) OF 'IND_TEST_OBJECT_NAME' (NON-UNIQUE) (Cost=110)
   6 3 BITMAP CONVERSION (FROM ROWIDS)
   7 6 INDEX (RANGE SCAN) OF 'IND_TEST_OWNER' (NON-UNIQUE) (Cost=6182)

SQL> conn binzhang/binzhang@MTS9205
Connected.
SQL> select name,value from v$mystat m,v$statname s where
   (name like '%uga%' or name like '%pga%') and m.STATISTIC#=s.STATISTIC# ;
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 105248
session uga memory max 105248
session pga memory 1234128
session pga memory max 4532728

SQL>select/*+ full(test) */ count(*) from
test where owner = 'BINZHANG' and object_name = 'BINZHANG';
COUNT(*)
----------
   107040

SQL>select name,value from v$mystat m,v$statname s where
   (name like '%uga%' or name like '%pga%') and m.STATISTIC#=s.STATISTIC# ;
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 105248
session uga memory max 121312
session pga memory 1234128
session pga memory max 4532728

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 21274 |
| 1 | SORT AGGREGATE | | 1 | 19 | |
|* 2 | TABLE ACCESS FULL | TEST | 6596 | 122K| 21274 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TEST"."OWNER"='BINZHANG' AND
   "TEST"."OBJECT_NAME"='BINZHANG')

FTS模式，session uga memory max有大约16064byte的增加，正好约等于_large_pool_min_alloc（16000byte）的数值，姑且算是SORT AGGREGATE 所使用的内存区域。

相比较FTS和BITMAP INDEX Operation，BITMAP需要在Large pool中分配额外的内存空间（当然是MTS模式下）。

如果OLTP的MTS中，大量使用了BITMAP CONVERSION BITMAP AND 的操作，则可能导致在large pool的内存分配上，有严重的shared pool latch 竞争。引用OTN上的“歧义”原文，

Note:

Oracle does not recommend using the BITMAP_MERGE_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. BITMAP_MERGE_AREA_SIZE is retained for backward compatibility.

猜测文档的意思应该是shared server option不推荐BITMAP_MERGE_AREA_SIZE parameter。