BITMAP索引異常增大

在一個資料庫中發現了一個異常增大的物件，經檢查改物件為BITMAP索引。

 

 

在資料庫中執行下面的指令碼，發現了一個異常增大的物件：

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1024/1024/1024 G
  2  FROM USER_SEGMENTS
  3  GROUP BY SEGMENT_NAME, SEGMENT_TYPE
  4  HAVING SUM(BYTES)/1024/1024/1024 > 15;

SEGMENT_NAME                   SEGMENT_TYPE                G
------------------------------ ------------------ ----------
TU_ORD_ORDER_ZJ_K_CODE         INDEX              16.7539215

檢查該物件的詳細資訊：

SQL> SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES     
  2  WHERE INDEX_NAME = 'TU_ORD_ORDER_ZJ_K_CODE';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
TU_ORD_ORDER_ZJ_K_CODE         FUNCTION-BASED BITMAP

這個物件是一個基於函式的BITMAP索引。一般來說，BITMAP索引的大小要遠遠小於普通索引，而這裡索引的大小達到了16G，遠遠超過表本身的大小，這裡肯定存在問題。透過對DBMS_SPACE包中SPACE_USAGE過程的封裝，檢查這個物件的空間使用情況：

SQL> create or replace procedure p_space_usage (p_segment_name in varchar2,
  2     p_segment_type in varchar2 default 'TABLE',
  3     p_segment_owner in varchar2 default user,
  4     p_partition_name in varchar2 default '') as
  5     v_unformatted_blocks number;
  6     v_unformatted_bytes number;
  7     v_fs1_blocks number;
  8     v_fs1_bytes number;
  9     v_fs2_blocks number;
 10     v_fs2_bytes number;
 11     v_fs3_blocks number;
 12     v_fs3_bytes number;
 13     v_fs4_blocks number;
 14     v_fs4_bytes number;
 15     v_full_blocks number;
 16     v_full_bytes number;
 17  begin
 18     dbms_space.space_usage(upper(p_segment_owner), upper(p_segment_name), upper(p_segment_type), v_unformatted_blocks,
 19             v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes,
 20             v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, upper(p_partition_name));
 21    
 22     dbms_output.put_line('unformatted_blocks is ' || v_unformatted_blocks);
 23     dbms_output.put_line('unformatted_bytes is ' || v_unformatted_bytes);
 24     dbms_output.put_line('fs1_blocks is ' || v_fs1_blocks);
 25     dbms_output.put_line('fs1_bytes is ' || v_fs1_bytes);
 26     dbms_output.put_line('fs2_blocks is ' || v_fs2_blocks);
 27     dbms_output.put_line('fs2_bytes is ' || v_fs2_bytes);
 28     dbms_output.put_line('fs3_blocks is ' || v_fs3_blocks);
 29     dbms_output.put_line('fs3_bytes is ' || v_fs3_bytes);
 30     dbms_output.put_line('fs4_blocks is ' || v_fs4_blocks);
 31     dbms_output.put_line('fs4_bytes is ' || v_fs4_bytes);
 32     dbms_output.put_line('full_blocks is ' || v_full_blocks);
 33     dbms_output.put_line('full_bytes is ' || v_full_bytes);
 34  end;
 35  /

Procedure created.

SQL> SET SERVEROUT ON SIZE 10000
SQL> EXEC P_SPACE_USAGE('TU_ORD_ORDER_ZJ_K_CODE', 'INDEX', 'NDMAIN')
unformatted_blocks is 2038268
unformatted_bytes is 16697491456
fs1_blocks is 0
fs1_bytes is 0
fs2_blocks is 139652
fs2_bytes is 1144029184
fs3_blocks is 0
fs3_bytes is 0
fs4_blocks is 0
fs4_bytes is 0
full_blocks is 13365
full_bytes is 109486080

PL/SQL procedure successfully completed.

問題已經很顯然了，這個基於函式的BITMAP索引包含了大量的unformatted blocks，造成大量空間的浪費，而實際上索引所需要的空間並沒有這麼大。

檢查METALINK，發現Oracle處理ASSM表空間的BITMAP索引存在空間浪費的bug，詳細bug描述參考：Bug No. 2915226。

Oracle給出了一個例子來重現這個bug：

SQL> SELECT TABLESPACE_NAME, SEGMENT_SPACE_MANAGEMENT         
  2  FROM DBA_TABLESPACES
  3  WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE_NAME                SEGMEN
------------------------------ ------
USERS                          AUTO

SQL> CREATE TABLE T (ID NUMBER NOT NULL, BITMAP_COL NUMBER NOT NULL);

表已建立。

SQL> INSERT INTO T SELECT ROWNUM, MOD(ROWNUM, 2) FROM DBA_OBJECTS;

已建立31016行。

SQL> CREATE BITMAP INDEX IND_B_T_BIT ON T(BITMAP_COL) TABLESPACE USERS;

索引已建立。

SQL> BEGIN
  2  FOR I IN REVERSE 1..1000 LOOP
  3  UPDATE T SET BITMAP_COL = 1
  4  WHERE BITMAP_COL = 0  AND ID = I;
  5  END LOOP;
  6  END;
  7  /

PL/SQL 過程已成功完成。

SQL> SET SERVEROUT ON SIZE 1000000
SQL> EXEC P_SPACE_USAGE('IND_B_T_BIT', 'INDEX')
unformatted_blocks is 93536
unformatted_bytes is 766246912
fs1_blocks is 0
fs1_bytes is 0
fs2_blocks is 8211
fs2_bytes is 67264512
fs3_blocks is 0
fs3_bytes is 0
fs4_blocks is 0
fs4_bytes is 0
full_blocks is 18
full_bytes is 147456

PL/SQL 過程已成功完成。

這個結果和這裡碰到的問題幾乎完全一致，只不過這個例子是人為構造bug的產生，而資料庫中則是由於正常修改在某些情況下觸發了這個bug，檢查當前的表空間資訊：

SQL> SELECT INDEX_NAME, TABLESPACE_NAME FROM USER_INDEXES 
  2  WHERE INDEX_NAME = 'TU_ORD_ORDER_ZJ_K_CODE';

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TU_ORD_ORDER_ZJ_K_CODE         INDX

SQL> SELECT SEGMENT_SPACE_MANAGEMENT FROM USER_TABLESPACES  
  2  WHERE TABLESPACE_NAME = 'INDX';

SEGMEN
------
AUTO

這個Bug在10.1.0.2被修正，對於9.2.0.2以上的版本，可以直接下載編號為2915226的patch來解決這個問題。