태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Bitmap Index 크기 문제

오라클 2008.03.02 22:22
성별이나 지역과 같은 Low Distinct Cardinality 컬럼에 대한 탐색 속도가 빠르다는 Bitmap Index의 강력한 특징때문에 Bitmap Index을 OLTP 환경(정확하게 말하면 Hybrid 환경)에서 사용하고자 하는 위험한 욕망을 가지게 된다. 하지만 이런 욕망은 대부분 파국으로 끝나고 만다. Bitmap Index의 다음과 같은 부작용 때문이다.
  • Bitmap Index은 전혀 다른 로우들간에도 로우 레벨의 락 경합이 발생한다. BTree Index와는 달리 하나의 인덱스 키가 넓은 범위의 로우를 관리하기 때문이다.
  • Bitmap Index의 크기가 DML의 유형에 따라 매우 불안정하다. 몇 번의 짧은 DML만으로 Bitmap Index의 크기가 지나치게 커지는 경우가 발생한다.
첫번째 문제는 Bitmap Index를 OLTP 환경에서 사용하지 못하는 결정적인 요인이다. 하지만 두번째 문제또한 매우 심각한 결과를 낳곤 한다. 50M에 불과하던 인덱스 크기가 이유없이 갑자기 수백M로 증가한다면 선뜻 사용하기는 쉽지 않을 것이다. 아래 테스트 결과를 보자.

UKJA@ukja92> select * from v$version;

BANNER                                                                                 -------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production  
CORE    9.2.0.1.0    Production                                                    
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Elapsed: 00:00:00.21
UKJA@ukja92>
UKJA@ukja92> create table t_short_commit(id int, c1 int);

Table created.

Elapsed: 00:00:00.01
UKJA@ukja92>
UKJA@ukja92> create table t_long_commit(id int, c1 int);

Table created.

Elapsed: 00:00:00.01
UKJA@ukja92>
UKJA@ukja92> create table t_batch_dml(id int, c1 int);

Table created.

Elapsed: 00:00:00.01

UKJA@ukja92>
UKJA@ukja92> -- create bitmap index
UKJA@ukja92> create bitmap index t_short_commit_bidx on t_short_commit(c1);

Index created.

Elapsed: 00:00:00.01
UKJA@ukja92>
UKJA@ukja92> create bitmap index t_long_commit_bidx on t_long_commit(c1);

Index created.

Elapsed: 00:00:00.01
UKJA@ukja92>
UKJA@ukja92> create bitmap index t_batch_dml_bidx on t_batch_dml(c1);

Index created.

Elapsed: 00:00:00.01
UKJA@ukja92>
UKJA@ukja92> -- Insert 문과 매우 짧은 단위의 Commit
UKJA@ukja92> begin
  2           for idx in 1 .. 10000 loop
  3               insert into t_short_commit values(idx, mod(idx,2));
  4               commit;
  5           end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.18
UKJA@ukja92> -- Insert 문과 1000 건 단위의 긴 Commit
UKJA@ukja92> begin
  2           for idx in 1 .. 10000 loop
  3               insert into t_long_commit values(idx, mod(idx,2));
  4               if mod(idx, 1000) = 0 then
  5                   commit;
  6               end if;
  7           end loop;
  8           commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.14
UKJA@ukja92> -- Insert ... Select 를 이용한 Batch DML
UKJA@ukja92> insert into t_batch_dml
  2  select level, mod(level,2)
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.09
UKJA@ukja92>
UKJA@ukja92> commit;

Commit complete.

Elapsed: 00:00:00.01
UKJA@ukja92>
UKJA@ukja92> -- 인덱스 크기 정보를 얻기 위해 통계 정보 수집
UKJA@ukja92> exec dbms_stats.gather_table_stats(user, 't_short_commit', cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
UKJA@ukja92> exec dbms_stats.gather_table_stats(user, 't_long_commit', cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
UKJA@ukja92> exec dbms_stats.gather_table_stats(user, 't_batch_dml', cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
UKJA@ukja92>
UKJA@ukja92> -- 인덱스 크기 비교
UKJA@ukja92> select index_name, leaf_blocks, distinct_keys
  2  from dba_indexes
  3  where index_name in ('T_SHORT_COMMIT_BIDX',
  4                   'T_LONG_COMMIT_BIDX',
  5                   'T_BATCH_DML_BIDX');

INDEX_NAME                     LEAF_BLOCKS DISTINCT_KEYS                              
------------------------------ ----------- -------------
T_SHORT_COMMIT_BIDX                     12             2
T_LONG_COMMIT_BIDX                     31             2
T_BATCH_DML_BIDX                         1             2

Elapsed: 00:00:00.40


위의 결과를 보면 매우 재미있는 사실을 알 수 있다. 짧은 단위의 Commit이 긴 단위의 Commit보다는 인덱스의 크기가 훨씬 작으며, Batch DML에서 인덱스의 크기가 최적을 유지한다. 긴 단위의 Commit에서 인덱스의 크기가 가장 큰 이유는 Commit이 이루어지기 전까지는 선행 Insert에서 사용하고 남은 공간을 재활용하지 못하게끔 구현이 되어 있기 때문이다. Insert ... Select 류의 Batch DML에서는 Bitmap Index의 크기가 최적화되는 것을 확인할 수 있다.

Update문이 미치는 영향은 훨씬 크다. 아래 테스트 결과를 보자.

UKJA@ukja92>
UKJA@ukja92> -- Update와 매우 짧은 단위의 Commit
UKJA@ukja92> begin
  2           for idx in 1 .. 10000 loop
  3               if(mod(idx,2)) = 0 then
  4                   update t_short_commit set c1 = 1 where id = idx;
  5               else
  6                   update t_short_commit set c1 = 0 where id = idx;
  7               end if;
  8               commit;
  9           end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.29
UKJA@ukja92> -- Update와 1000건 단위의 긴 Commit
UKJA@ukja92> begin
  2           for idx in 1 .. 10000 loop
  3               if(mod(idx,2)) = 0 then
  4                   update t_long_commit set c1 = 1 where id = idx;
  5               else
  6                   update t_long_commit set c1 = 0 where id = idx;
  7               end if;
  8               if(mod(idx,1000)) = 0 then
  9                   commit;
 10               end if;
 11           end loop;
 12           commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.59
UKJA@ukja92> -- Batch Update
UKJA@ukja92> update t_batch_dml set c1 = decode(c1,0,1,0);

10000 rows updated.

Elapsed: 00:00:00.34
UKJA@ukja92> commit;

Commit complete.

Elapsed: 00:00:00.04
UKJA@ukja92>
UKJA@ukja92> -- 인덱스 크기 계산
UKJA@ukja92> exec dbms_stats.gather_table_stats(user, 't_short_commit', cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.15
UKJA@ukja92> exec dbms_stats.gather_table_stats(user, 't_long_commit', cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
UKJA@ukja92> exec dbms_stats.gather_table_stats(user, 't_batch_dml', cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
UKJA@ukja92>
UKJA@ukja92> -- 인덱스 크기 비교
UKJA@ukja92> select index_name, leaf_blocks, distinct_keys
  2  from dba_indexes
  3  where index_name in ('T_SHORT_COMMIT_BIDX',
  4                   'T_LONG_COMMIT_BIDX',
  5                   'T_BATCH_DML_BIDX');

INDEX_NAME                     LEAF_BLOCKS DISTINCT_KEYS                              
------------------------------ ----------- -------------
T_SHORT_COMMIT_BIDX                     12             2
T_LONG_COMMIT_BIDX                      93             2
T_BATCH_DML_BIDX                         1             2

Elapsed: 00:00:00.86
UKJA@ukja92>


위의 테스트 결과를 보면 Update문이 긴 단위의 Commit과 조합되면 최악의 결과를 낳는 것을 알 수 있다. 이런 원리 때문에 DML이 빈번한 OLTP 환경에서 Bitmap 인덱스의 크기가 비상식적으로 커지는 현상이 종종 발생하게 된다.

다행히 10g부터는 이런 문제가 완전히 해소되었다. 동일한 테스트를 10g/11g에서 수행한 결과는 다음과 같다.

Insert 수행후 인덱스 크기
INDEX_NAME                     LEAF_BLOCKS DISTINCT_KEYS
------------------------------ ----------- -------------
T_SHORT_COMMIT_BIDX                      1             2
T_LONG_COMMIT_BIDX                       1             2
T_BATCH_DML_BIDX                         1             2  

Update 수행 후 인덱스 크기
INDEX_NAME                     LEAF_BLOCKS DISTINCT_KEYS                 ------------------------------ ----------- -------------
T_SHORT_COMMIT_BIDX                      1             2
T_LONG_COMMIT_BIDX                       1             2
T_BATCH_DML_BIDX                         1             2      

10g부터는 DML의 유형(짧은 Commit, 긴 Commit, Batch DML)과 무관하게 Bitmap 인덱스의 크기가 항상 적절한 수준을 유지하는 것을 확인할 수 있다. 큰 개선이라고 할 수 있다.

위의 테스트 결과를 Bitmap 인덱스를 OLTP 환경에서 사용하는데 있어서 제약이 없어진 것으로 해석하는 것은 무리가 있을 것이다. Bitmap 인덱스는 그 태생 자체가 DW를 염두에 두고 설계된 것이다. OLTP 환경에서 Bitmap 인덱스를 잘못 사용하는 것은 말그대로 Performance Killer를 만드는 꼴이다.

하지만, OLTP와 DW의 중간적 성격을 요구하는 현재의 환경은 OLTP 환경에서 Bitmap 인덱스를 사용하고자 하는 욕구를 항상 낳게 된다. 위의 테스트 결과는 Hybrid 환경에서 Bitmap 인덱스가 가지는 문제점 중 하나가 해소되었음을 알려주는 좋은 소식임은 틀림없다.              

PS) 비록 10g이후부터는 대부분 상황에서 Bitmap 인덱스의 크기가 안정된 패턴을 보이지만, 여전히 BTree 인덱스에 비해서는 안정성이 떨어지는 현상이 종종 발생한다. Bitmap 인덱스의 크기가 단지 DML의 패턴 뿐만 아니라, Distinct 키의 수와 키값의 군집성(Clustering)과도 연관이 있기 때문이다. 또한 버그로 인해 인덱스 크기가 비정상적으로 커지는 경우도 보고되고 있다.

신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바