태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

Bitmap Index 크기 문제 - Scatterness

오라클 2008.09.30 10:54
오전에 다음과 같은 문의를 받았다.

Bitmap Index의 Key가 Scattered되어 있으면 Index의 크기가 커진다고 하는데(즉, Leaf Block의 수가 많아진다고 하는데) 그 이유가 무엇인가?

실제로 그렇다. Key의 분산 정도가 Bitmap Index의 크기에 많은 영향을 줄 수 있다. 예를 들어 Key의 분포가 다음과 같은 경우를 비교해보자.

- Key#1: 1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4,5,5,5,5,5
- Key#2: 1,2,3,4,5,1,2,3,4,5,1,2,3,4,5,1,2,3,4,5,1,2,3,4,5

Key#1과 Key#2는 Distinct Count는 동일하지만 Key의 군집 정도가 틀리다.  Key#1은 군집이 매우 잘 되어있고, Key#2는 상당히 흩어져 있다.

이 경우 Key#1 Bitmap Index의 크기가 Key#2 Bitmap Index의 크기보다 훨씩 작다. 왜 그런가?

Bitmap Index의 Leaf Block에 Key가 저장되는 방식 때문이다. Bitmap Index의 {Start RowID:End RowID:Bitmap} Data를 Leaf Block에 저장한다. 이때 Bitmap 값에서 연속된 "0"의 값은 Compression이 된다. 가령 Key 값이 1인 경우를 비교해보면

- Key#1
Value=1 : {1:25:11111/00000/00000/00000/00000} = {1:25:11111/0*20}

- Key#2
Value=1 : {1:25:10000/10000/10000/10000/10000} = {1:25:1+0*4/1+0*4/1+0*4/1+0*4/1+0*4}

와 같이 저장된다. 값이 모여 있으면 Compression이 잘 되기 때문에 크기가 작아지는 것이다.
(Compression의 정확한 방식은 추가적인 테스트와 조사가 필요함)

간단한 테스트를 통해서 이를 확인해보자.

값이 보여 있는 Table t_clustered와 Bitmap Index를 만든다.

UKJA@ukja102> drop table t_clustered purge;

Table dropped.

Elapsed: 00:00:00.14
UKJA@ukja102>
UKJA@ukja102> create table t_clustered
  2  as
  3  select
  4      mod(level, 1000) as c1
  5  from
  6      dual
  7  connect by level <= 100000
  8  order by 1;

Table created.

Elapsed: 00:00:02.20
UKJA@ukja102>
UKJA@ukja102> create bitmap index t_clustered_bidx on t_clustered(c1);

Index created.

Elapsed: 00:00:00.75


값이 흩어져 있는 Table t_scattered와 Bitmap Index를 만든다.

UKJA@ukja102> drop table t_scattered purge;

Table dropped.

Elapsed: 00:00:00.04
UKJA@ukja102>
UKJA@ukja102> create table t_scattered
  2  as
  3  select
  4      mod(level, 1000) as c1
  5  from
  6      dual
  7  connect by level <= 100000
  8  order by dbms_random.random;

Table created.

Elapsed: 00:00:03.32
UKJA@ukja102>
UKJA@ukja102> create bitmap index t_scattered_bidx on t_scattered(c1);

Index created.

Elapsed: 00:00:00.75
UKJA@ukja102>

통계 정보를 수집한다.

UKJA@ukja102> -- gather statistics
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't_clustered', cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.14
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't_scattered', cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.98

두 Bitmap Index의 크기를 비교해본다.

UKJA@ukja102> select index_name, leaf_blocks, distinct_keys
  2  from dba_indexes
  3  where index_name in ('T_CLUSTERED_BIDX', 'T_SCATTERED_BIDX');

INDEX_NAME           LEAF_BLOCKS DISTINCT_KEYS
-------------------- ----------- -------------
T_CLUSTERED_BIDX               6          1000
T_SCATTERED_BIDX              36          1000

Elapsed: 00:00:00.29

Distinct Key의 수는 1000개로 동일하지만 Leaf Block의 수가 확연하게 다르다. 값이 흩어져 있는 경우가 훨씬 크다.

좀 더 정확한 정보를 위해 Index Tree Dump를 수행한다.

UKJA@ukja102> exec tree_dump('t_clustered_bidx');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
UKJA@ukja102>
UKJA@ukja102> exec tree_dump('t_scattered_bidx');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
UKJA@ukja102>
UKJA@ukja102> @trace_view

값이 모여 있는 경우에는 다음과 같이 하나의 Leaf Block에 많은 수의 Key가 들어간다. Compression이 잘 되기 때문이다.

----- begin tree dump
branch: 0x2805a54 41966164 (0: nrow: 6, level: 1)
   leaf: 0x2805a55 41966165 (-1: nrow: 187 rrow: 187)
   leaf: 0x2805a56 41966166 (0: nrow: 185 rrow: 185)
   leaf: 0x2805a57 41966167 (1: nrow: 184 rrow: 184)
   leaf: 0x2805a58 41966168 (2: nrow: 185 rrow: 185)
   leaf: 0x2c0f9b1 46201265 (3: nrow: 185 rrow: 185)
   leaf: 0x2c0f9b2 46201266 (4: nrow: 74 rrow: 74)
----- end tree dump

값이 흩어져 있는 경우에는 하나의 Leaf Block에 적은 수의 Key가 들어간다. Compression의 효율성이 떨어지기 때문이다.

----- begin tree dump
branch: 0x2805a74 41966196 (0: nrow: 36, level: 1)
   leaf: 0x2805a75 41966197 (-1: nrow: 28 rrow: 28)
   leaf: 0x2805a76 41966198 (0: nrow: 28 rrow: 28)
   leaf: 0x2805a77 41966199 (1: nrow: 28 rrow: 28)
   leaf: 0x2805a78 41966200 (2: nrow: 28 rrow: 28)
   leaf: 0x2c0f9e1 46201313 (3: nrow: 28 rrow: 28)
   leaf: 0x2c0f9e2 46201314 (4: nrow: 28 rrow: 28)
   leaf: 0x2c0f9e3 46201315 (5: nrow: 28 rrow: 28)
   leaf: 0x2c0f9e4 46201316 (6: nrow: 28 rrow: 28)
   leaf: 0x2c0f9e5 46201317 (7: nrow: 28 rrow: 28)
   leaf: 0x2c0f9e6 46201318 (8: nrow: 28 rrow: 28)
   leaf: 0x2c0f9e7 46201319 (9: nrow: 28 rrow: 28)
   leaf: 0x2c0f9e8 46201320 (10: nrow: 27 rrow: 27)
   leaf: 0x18023a2 25174946 (11: nrow: 28 rrow: 28)
   leaf: 0x18023a3 25174947 (12: nrow: 27 rrow: 27)
   leaf: 0x18023a4 25174948 (13: nrow: 28 rrow: 28)
   leaf: 0x18023a5 25174949 (14: nrow: 28 rrow: 28)
   leaf: 0x18023a6 25174950 (15: nrow: 28 rrow: 28)
   leaf: 0x18023a7 25174951 (16: nrow: 28 rrow: 28)
   leaf: 0x18023a8 25174952 (17: nrow: 28 rrow: 28)
   leaf: 0x2805a79 41966201 (18: nrow: 28 rrow: 28)
   leaf: 0x2805a7a 41966202 (19: nrow: 28 rrow: 28)
   leaf: 0x2805a7b 41966203 (20: nrow: 28 rrow: 28)
   leaf: 0x2805a7c 41966204 (21: nrow: 28 rrow: 28)
   leaf: 0x2805a7d 41966205 (22: nrow: 28 rrow: 28)
   leaf: 0x2805a7e 41966206 (23: nrow: 27 rrow: 27)
   leaf: 0x2805a7f 41966207 (24: nrow: 28 rrow: 28)
   leaf: 0x2805a80 41966208 (25: nrow: 28 rrow: 28)
   leaf: 0x2c0f9ea 46201322 (26: nrow: 28 rrow: 28)
   leaf: 0x2c0f9eb 46201323 (27: nrow: 28 rrow: 28)
   leaf: 0x2c0f9ec 46201324 (28: nrow: 28 rrow: 28)
   leaf: 0x2c0f9ed 46201325 (29: nrow: 28 rrow: 28)
   leaf: 0x2c0f9ee 46201326 (30: nrow: 28 rrow: 28)
   leaf: 0x2c0f9ef 46201327 (31: nrow: 28 rrow: 28)
   leaf: 0x2c0f9f0 46201328 (32: nrow: 28 rrow: 28)
   leaf: 0x1802aa9 25176745 (33: nrow: 28 rrow: 28)
   leaf: 0x1802aaa 25176746 (34: nrow: 23 rrow: 23)
----- end tree dump

Block Dump를 수행해보면 훨씬 정확한 정보를 얻을 수 있다.

값에 모여 있는 경우에는 다음과 같이 Bitmap 값(col3)의 크기가 작다.

row#0[8002] flag: ------, lock: 0, len=34
col 0; len 1; (1):  80
col 1; len 6; (6):  02 80 5a 24 00 00
col 2; len 6; (6):  02 80 5a 24 00 67
col 3; len 15; (15):  cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f

하지만 값이 흩어져 있는 경우에는 Bitmap 값(col3)의 크기가 매우 크다.

col 0; len 1; (1):  80
col 1; len 6; (6):  01 80 09 8a 00 d0
col 2; len 6; (6):  02 c0 f9 e0 00 27
col 3; len 232; (232):
 04 c4 01 c7 ed 01 c5 f9 02 c1 05 c5 25 c0 16 c2 32 c4 92 02 c1 ed db 25 c3
 49 c0 0f c6 e2 01 c0 e1 01 c3 09 c6 7d c6 bb 01 c2 66 c5 6f a3 c2 4c c2 33
 c1 14 c6 9a 01 c3 9b 8b cf e8 05 c3 6b c4 52 c6 9d 01 c4 7c 1b c7 15 17 c7
 77 c3 b0 01 c1 7d c7 04 c1 81 02 c3 b1 01 c7 1c c5 6f 13 c7 e5 29 71 8c c7
 07 c0 1e c0 10 c4 68 f9 0b 08 01 c4 d5 01 c2 d1 02 c7 3b c4 4a c7 8e 04 3a
 26 9e c0 56 c0 7a c6 45 c0 e7 ac a5 b9 01 c2 59 c5 35 c2 15 c6 86 02 c4 81
 03 c3 10 c4 75 c2 96 01 c6 4f 31 c2 9a 01 c5 20 c5 a6 02 c3 e4 01 c3 a2 01
 c2 c0 02 95 c2 35 c5 18 c0 e8 f9 c1 02 c1 0a c3 e5 01 c6 9f 01 c7 aa 04 c1
 cf 01 c4 9c 06 c0 30 97 c2 c8 01 c4 8b 01 ac c0 20 c7 06 c3 a7 01 c3 92 01
 c4 10 c3 b1 01 c7 30

Bitmap Index의 크기는 매우 다양한 요소에 의해 결정된다. Distinct Count, 군집도, DML의 유형 등이 모두 Bitmap Index의 크기에 큰 영향을 준다. 이런 다양한 요인들을 점검한 후에 사용해야 나중에 발등 찍히는 일이 없을 것이다.


신고
Trackback 0 : Comment 1
  1. 욱짜 2008.10.11 13:01 신고 Modify/Delete Reply

    아마 맡은 업무의 차이에서 비롯될겁니다. 개발자의 입장이라면 위의 내용은 전혀 공감이 안되는 이상한 이야기로 들릴겁니다. 하지만 업무의 특성상 Troubleshooting을 해야하는 입장이라면 대단히 유용한 지식이 될 수 있습니다.

    가령 제가 일하는 회사에서 고객사로부터 받는 질문들은 위에 제가 적은 것보다 훨씬 더 인터널하고 이상한 현상들이 많습니다. 하지만 어떻게든 그 원인을 찾아서 해결책까지 제시해야되는 입장입니다. 만일 저희같은 회사를 이용할 수 없는 DBA들 중에 욕심이 있다면 어떻게든 스스로 조사를 해야겠죠. 그런 측면으로 이해하시면 될 거 같습니다.

    그리고, Bitmap는 절대 쓰지 말아야할 Object가 아닙니다. DW 환경에서는 없어서는 안될 중요한 기능입니다. OLTP에서는 말씀하신대로 사용해서는 안되겠죠. DW+OLTP의 Hybrid 환경이라면 상황을 봐서 사용해야할 겁니다.

Write a comment

티스토리 툴바