태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'Index Rebuild'에 해당되는 글 5건

  1. 2008.06.13 왜 인덱스 크기가 계속 커지는가 - 삭제된 공간이 재활용됨에도 불구하고 (4)
  2. 2008.02.25 Oracle 10g의 재미있는 버그 - Index Rebuild 후 Logical Reads 증가 현상 (3)
  3. 2007.12.28 [Oracle is Mad] Index Rebuild를 둘러싼 논쟁 - Part3 (3)
  4. 2007.12.24 [Oracle is Mad] Index Rebuild를 둘러싼 논쟁 - Part2 (4)
  5. 2007.12.20 [Oracle is Mad] Index Rebuild를 둘러싼 논쟁 - Part1

왜 인덱스 크기가 계속 커지는가 - 삭제된 공간이 재활용됨에도 불구하고

오라클 2008.06.13 21:09
아래 글에서 Index 내의 삭제된 공간은 재활용이 이루어짐을 정리한 바 있다.

2007/12/24 - [Advanced Oracle] - [Oracle is Mad] Index Rebuild를 둘러싼 논쟁 - Part2

하지만, 이 내용을 세미나 시간에 이야기하면 항상 이 질문이 따라 온다.

삭제하고 데이터 추가하는 방식으로 사용하는데 Index 크기가 계속 커지던데요?

다음과 같은 두 가지 상황에서 이런 일이 발생한다.

1. Commit 하지 않는 Delete

아래와 같은 작업에서는 Index의 크기가 변하지 않는다. 삭제 후 추가 작업이기 때문이다.

delete from t1 where ...; -- 5000 블록 삭제
commit;
insert into t1 select...; -- 5000 블록 추가

하지만, 아래와 같은 작업 시에는 Index의 크기가 추가된 데이터만큼 늘어난다.

delete from t1 where ...; --  5000 블록 삭제
insert into t1 select...; -- 5000 블록 추가

Oracle의 Transaction 관리 메커니즘은 위와 같은 경우 delete에 의해 확보된 Free Block이 Commit에 의해 확정되지 않은 상태에서는 Batch Insert에 의해 재사용되지 않도록 한다.


2. 작업 패턴 상의 문제

아래와 같은 세 가지 패턴의 작업이 있다. 세 패턴 모두 최종 결과는 10,000 건이다.
(모두 DML 사이에 Commit이 이루어진다고 가정한다)

-- Case1
insert(10,000)

delete(5,000)
delete(5,000)
insert(10,000)

-- Case2
insert(10,000)
delete(5,000)
insert(10,000)
delete(5,000)

-- Case3
insert(10,000)
insert(10,000)
delete(5,000)
delete(5,000)


비록 최종 데이터는 모두 10,000건이지만, 차지하는 공간은 10,000 : 15,000 : 20,000 이다. Index의 크기는 일단 커지고 나면 다시는 줄지 않기 때문이다. (Rebuild를 하지 않는 한)

위의 두 가지 사례가 반복적으로 작용하면 마치 Index의 공간이 다시는 재활용되지 않고 무한정 커지는 것으로 생각될 수 있다. Index 크기가 계속 증가하는 현상이 발생할 때는 위와 같은 상황에 해당하지 않는지 면밀하게 점검할 필요가 있을 것이다.

PS) 그 외에도 사례가 더 있을 수 있을 거 같다. 이 글을 읽고 많은 분들이 의견을 달았으면 좋겠다.

신고
tags : Index Rebuild
Trackback 0 : Comments 4
  1. oracler 2008.06.20 21:49 신고 Modify/Delete Reply

    인덱스 크기는 오로지 rebuild 로만 줄이는것이 가능하다.............
    그러면 shrink 로도 인덱스 사이즈는 "원래" 줄어들수 없다는 건가요.?
    asa_recommendation 돌리면 index 도 shrink 해서 size 줄이라고 나오는데
    그럼 oracle 권고가 틀리다는건지요 ?
    그리고 size 측정은 정확히 무엇을 기준으로 하는건가요 dba_segment의 byte 인가요 dba_index 의 block 인가요. 가령, stats 으로 돌릴때와 analyze 돌릴때 동일컬럼에 대해 _index 나 _tables 의 통계정보가 미미한
    차이가 나기때문에 사이즈 측정을 무엇을 기준으로 하는지가 명확해야 할것 같습니다. (num_rows 같은거 다르게 나옴..)

  2. 욱짜 2008.06.20 22:06 신고 Modify/Delete Reply

    여기서 rebuild는 Online rebuild, offline rebuild, shrink space를 모두 포함한 일반적인 용어로 이해하시면 됩니다.
    Size 측정 기준은 상호 검증을 해보아야 할 거 같은데요? 기회가 되면 검증해보겠습니다. oracler 님께서도 검증 가능하시면 테스트 후 결과를 올려 주셔도 좋겠습니다.

  3. oracler 2008.07.01 00:42 신고 Modify/Delete Reply

    음....진심으로 말씀드리건데,
    행여나 딴지를 거는것처럼 느끼실까봐 많이 걱정되네요,
    제가 부족해서 (많이 몰라서) 여쭤본겁니다. 진심입니다.
    모르기때문에 질문이 이것저것 많음을 양해하여 주시면 감사하겠습니다.

    저번에 인덱스 힌트 이상한것 질문시 스크립트 올리라고 하셨는데요,
    어떤 상황이든지 그런 특정한 상황 재연 테스트 스크립트를 아무나 만드나요?
    그런 DB 전문가는 대한민국, 우리나라에 몇명 안될것입니다. 그중 한분이 조선생님이실것이구요.

    IT 에서 어떤 이상야릇한 상황을 시간이 지나서 재연할수 있다는것은 그 방면의 공인 전문가일것입니다.
    이 웹싸이트에 접속하는 여러 사람들중의 하나인 저에게
    그런 전문가적인 상호검증을 해봐라라든가, 스크립트를 올려달라는건
    저에겐 너무나 벅찬 말씀일수도 있다는점을 재고해주세요.

    리빌드가 쉬링크를 포함한다든 말은 처음 들어보는 "용어"입니다.
    온라인 오프라인만 같은 테두리로 생각했지 쉬링크까지의 같은 그룹인지는 정녕 몰랐네요.
    RBS 쉬링크는 알지만 RBS 도 리빌드가 되는건지 이거참 에구 어렵네요.

  4. 욱짜 2008.07.01 09:16 신고 Modify/Delete Reply

    Rebuild라는 용어는 저도 고민이 많았습니다. Offline Rebuild, Online Rebuild, Shrink Compact, Shrink Space, Coalesce 같은 명령들이 사실은 다 재구축(rebuild)의 범주에 속하는데(사실은 Move 명령도 Rebuild의 범주에 속하죠) 이미 Rebuild라는 명령문이 물리적으로 존재해버리니까 용어의 선정이 어렵더라구요. 그래서 저도 가끔 혼용해서 쓰고 있습니다.

    오해를 줄이려면 정확한 용어를 사용하는게 더 낫겠다는 판단이 듭니다. 즉 Rebuild와 Shrink는 전혀 다른 용어로 정확하게 분류하는게 좋겠네요.

    그리고 모든 케이스가 다 Test로 재현가능한 것은 아니죠. 하지만, 언제는 재현 가능하도록 하는 것이 최선이라고 봅니다. Test Case를 정확하게 만들고 재현할 수 있다면 원리와 해결책은 자연스럽게 따라 나오기 때문입니다.

    Test Case를 만들고 재현하는 것이 항상 어려운 것은 아닙니다. 여기도 아마 80:20의 법칙이 적용될 것으로 봅니다. 즉 80%는 재현이 쉬운 케이스일 것이고, 20%는 어렵거나 불가능한 경우일겁니다.

    제가 가끔 받는 질문들의 약 80%는 사실 문의하는 분들이 약간의 시간만 들이면 충분히 재현가능한 것들입니다. Test Case를 만들고 재현하는 기술이 늘면 Oracle과 관련된 지식도 자연스럽게 증가합니다. 처음엔 조금 어렵더라도 계속 시도하면 그 열매는 매우 클겁니다.

Write a comment


Oracle 10g의 재미있는 버그 - Index Rebuild 후 Logical Reads 증가 현상

오라클 2008.02.25 13:35
얼마전 10.2.0.4 Patch Set이 발표되었는데 거기에 상당히 재미있는 버그가 보고되어 있다.
내용은 이렇다.

버그번호: 6455161
제목: Index Rebuild나 Table Truncate후 Logical Reads와 cache buffers chains latch 경합이 증가하는 현상

구체적인 현상은 이렇다.

다음과 같은 Access 경로를 가정해본다.

Nested Loop
   Table Full Scan (TabA)
    Index Range Scan (IndB)


이 경우 TabA 테이블의 로우 하나에 대해 indB 인덱스를 Root->Branch->Leaf 블록을 탐색한다. 만일 TabA가 10만건이라면 Root->Branch->Leaf 블록을 총 10만번 일일이 방문해야 하는 셈이다. 오라클이 이것에 의한 비효율을 줄이기 위해 Root 블록에 대해 Buffer Pinning 기법을 사용한다. 즉 Root 블록이 액세스될 때 이것을 Pin(말 그대로 buffer cache에서 밀려나지 않도록 고정시킨다는 의미)시킨다. 이렇게 Pin이 된 Root 블록은 메모리에서 밀려나지 않는다는 것이 보장되기 때문에 매번 새로 읽지 않고 한번 읽은 블록을 재활용할 수 있다. 따라서 Root 블록에 대한 반복적인 Logical Reads를 줄여주는 효과가 있다.

이렇게 Buffer Pinning이 동작하는 경우에는 session logical reads 지표 대신 buffer is pinned count 지표가 증가한다.

여기까지가 Oracle 9i부터의 일반적인 스토리다. 하지만 위의 버그에 의하면 10g에서는 Index Rebuild나 Table Truncate에 의해 Index 정보가 바뀌면 이 Buffer Pinning이 동작하지 않는다는 것이다. 더 정확하게 말하면 Index Rebuild나 Table Truncate에 의해 dba_objects.object_id와 dba_objects.data_object_id 가 달라지는 현상시 발생하면 Index Root 블록에 대한 Buffer Pinning이 동작하지 않게 된다.

머리털이 쭈뼛해지는 버그다. 만일 Nested Loop Join으로 많은 량의 데이터를 인덱스를 경유해 추출하는 쿼리를 사용하는 환경에서, 인덱스  경유 부하를 줄이기 위해 Index Rebuild를 주기적으로 수행하고 있다면? 오히려 쿼리 성능이 크게 저하되는 결과를 가져오게 된다.

아래 테스트 결과가 이를 잘 증명하고 있다.


UKJA@ukja10> @bug                                                                                        
UKJA@ukja10> select * from v$version;
BANNER                                                                     -----------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- create table
UKJA@ukja10> drop table t_bug1 purge;

Table dropped.

Elapsed: 00:00:00.01
UKJA@ukja10> drop table t_bug2 purge;

Table dropped.

Elapsed: 00:00:00.03
UKJA@ukja10>
UKJA@ukja10> create table t_bug1(id int);

Table created.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> create table t_bug2(id int, name char(10));

Table created.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10> -- creat index
UKJA@ukja10> create index t_bug2_idx on t_bug2(id);

Index created.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- generate data
UKJA@ukja10> insert into t_bug1
  2  select level
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> insert into t_bug2
  2  select level, 'name'
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.07
UKJA@ukja10>
UKJA@ukja10> commit;

Commit complete.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- delete half of t_bug2
UKJA@ukja10> delete from t_bug2 where mod(id, 2) = 0;

5000 rows deleted.

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

Commit complete.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10> -- nested loop join
UKJA@ukja10> select /*+ gather_plan_statistics ordered use_nl(t_bug1 t_bug2) */ count(*)
  2  from t_bug1, t_bug2
  3  where t_bug1.id = t_bug2.id;

                                          COUNT(*)                         --------------------------------------------------                                                                       5000                                                       
Elapsed: 00:00:00.18
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                          --------------------------------------------------------------------------------------------
SQL_ID  8x1jvara1cwga, child number 0                                      -------------------------------------                                                      
select /*+ gather_plan_statistics ordered use_nl(t_bug1 t_bug2) */ count(*) from           
t_bug1, t_bug2 where t_bug1.id = t_bug2.id                                                 
Plan hash value: 934322507                                                 --------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |            |      1 |      1 |      1 |00:00:00.17 |   10043 |
|   2 |   NESTED LOOPS      |            |      1 |   5000 |   5000 |00:00:00.22 |   10043 |
|   3 |    TABLE ACCESS FULL| T_BUG1     |      1 |  10000 |  10000 |00:00:00.04 |      23 |
|*  4 |    INDEX RANGE SCAN | T_BUG2_IDX |  10000 |      1 |   5000 |00:00:00.12 |   10020 |
--------------------------------------------------------------------------------------------

==> t_bug2_idx 인덱스에 대한 Logical Reads가 10,000인것에 유의하자. t_bug1 테이블의 10,000건에 대해 Index 깊이(2)만큼 읽으면 20,000 블록이 정상이다. 하지만 buffer pinning에 의해 Root 블록이 Pin됨으로써 실제로는 절반에 불과한 10,000 블록만을 읽는다.
                                                                           Predicate Information (identified by operation id):                        ---------------------------------------------------                          4 - access("T_BUG1"."ID"="T_BUG2"."ID")
                                                                                           
25 rows selected.

Elapsed: 00:00:00.06
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10> -- Rebuild index
UKJA@ukja10> alter index t_bug2_idx rebuild;

Index altered.

Elapsed: 00:00:00.01
                              
UKJA@ukja10> -- nested loop join
UKJA@ukja10> select /*+ gather_plan_statistics ordered use_nl(t_bug1 t_bug2) */ count(*)
  2  from t_bug1, t_bug2
  3  where t_bug1.id = t_bug2.id;

                                          COUNT(*)                         -------------------------------------------------- 
5000                                         

Elapsed: 00:00:00.20
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                          --------------------------------------------------------------------------------------------
SQL_ID  8x1jvara1cwga, child number 0                                      -------------------------------------                                                      
select /*+ gather_plan_statistics ordered use_nl(t_bug1 t_bug2) */ count(*) from t_bug1,   
t_bug2 where t_bug1.id = t_bug2.id                                                         
Plan hash value: 934322507                                                 --------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |            |      1 |      1 |      1 |00:00:00.20 |   20042 |
|   2 |   NESTED LOOPS      |            |      1 |   5000 |   5000 |00:00:00.23 |   20042 |
|   3 |    TABLE ACCESS FULL| T_BUG1     |      1 |  10000 |  10000 |00:00:00.04 |      23 |
|*  4 |    INDEX RANGE SCAN | T_BUG2_IDX |  10000 |      1 |   5000 |00:00:00.15 |   20019 |
--------------------------------------------------------------------------------------------

==> t_bug2_idx 인덱스에 대한 Logical Reads가 20,000으로 증가한 것을 알 수 있다. Root 블록에 대한 Pinning이 발생하지 않기 때문이다.
                                                                                          
Predicate Information (identified by operation id):                        ---------------------------------------------------                           4 - access("T_BUG1"."ID"="T_BUG2"."ID")
                                                                                           
25 rows selected.

Elapsed: 00:00:00.06
UKJA@ukja10>
UKJA@ukja10> spool off


위의 테스트 결과는 Index Rebuild를 함부로 해서는 안되는 또 하나의 이유가 될 만하다.

만일 시스템에서 위와 같은 문제가 발생한다면? 불행하게도 Workaround는 10.2.0.4로 Patch를 하거나, Index Rebuild 대신 Index Drop/Create를 수행하고, Table Truncate 후에도 Index를 Drop/Create하는 것이다. 이런 중요한 버그에 대한 Patch가 10g의 수명이 다되어가는 이제야 나왔다는 것이 의아할 따름이다.

신고
Trackback 0 : Comments 3
  1. Mr. Park 2008.03.03 15:52 신고 Modify/Delete Reply

    interim patch 가 존재 하네요 6455161

  2. 욱짜 2008.03.03 17:59 신고 Modify/Delete Reply

    좋은 정보 감사합니다.

  3. genious 2008.04.07 17:52 신고 Modify/Delete Reply

    10.2.0.2 에서 AIX용 patch는 없어서..이거이거~

Write a comment


[Oracle is Mad] Index Rebuild를 둘러싼 논쟁 - Part3

오라클 2007.12.28 23:51
Index Rebuild 논쟁을 이해하기 위해 이해해야할 또 하나의 개념이 Index Split이다. Index를 Rebuild한 후에 발생하는 대표적인 부작용이 Split이기 때문이다.

Index Split이란, Leaf Node나 Branch Node가 꽉 찬 상태에서 새로운 키 값이 삽입될 때 공간 확보를 위해서 키 값을 Split하는 것을 말한다. Index Split이 발생하면 새로운 블록을 할당받고, 기존의 키 값들을 기존 블록과 새로운 블록으로 나누어(Split) 저장한다.

Index Split은 보통 50-50 Split과 90-10 Splt으로 구분된다.

1. 50-50 Split은 아래 그림과 같이 중간에 위치한 Leaf Node가 Split되는 경우에 발생한다. 기존 블록과 새로운 블록이 값을 50:50으로 나누어 갖는다고 해서 50-50 Split이라는 이름이 붙여졌다.

사용자 삽입 이미지




















아래에 50-50 Split이 발생한 실제 예가 있다.

(Split 전)
----- begin tree dump
branch: 0x141b81f 21084191 (0: nrow: 3, level: 1)
   leaf: 0x141b837 21084215 (-1: nrow: 69 rrow: 69)
   leaf: 0x141b83d 21084221 (0: nrow: 69 rrow: 69)
   leaf: 0x141b83a 21084218 (1: nrow: 62 rrow: 62)
----- end tree dump

(Split 후)
----- begin tree dump
branch: 0x141b81f 21084191 (0: nrow: 4, level: 1)
   leaf: 0x141b837 21084215 (-1: nrow: 69 rrow: 69)
   leaf: 0x141b83d 21084221 (0: nrow: 40 rrow: 40) 
   leaf: 0x141b834 21084212 (1: nrow: 30 rrow: 30) 
   leaf: 0x141b83a 21084218 (2: nrow: 62 rrow: 62)
----- end tree dump


2. 90-10 Split은 아래 그림과 같이 가장 오른쪽 마지막에 위치한 Leaf Node에 최대값이 Insert되는 경우에 발생
한다. 기본 블록과 새로운 블록이 값을 90:10으로 나누어 갖는다고 해서 90-90 Split이라는 이름이 붙여졌다.
엄밀하게 말하면 90-10이 아니라 99-1이 맞을 것이다. 새로운 블록에는 기존 블록 중 최대 값만이 들어가고 나머지는 모두 기존 블록에 남기 때문이다. 그리고 이 새로운 블록에 새로운 최대값이 추가된다.

최대값이 Insert되는 경우에 한해 90-10 Split을 수행하는 이유는 Split의 회수를 줄이기 위해서이다. 제일 오른쪽 블록에 Insert가 집중되는 경우에 대비해 제일 오른쪽 블록에 여유 공간을 많이 만들기 위한 일종의 트릭이다.

사용자 삽입 이미지




















아래에 90-10 Split이 발생한 실제 예가 있다.

(Split 전)
----- begin tree dump
branch: 0x141b81f 21084191 (0: nrow: 4, level: 1)
   leaf: 0x141b837 21084215 (-1: nrow: 69 rrow: 69)
   leaf: 0x141b83d 21084221 (0: nrow: 40 rrow: 40) 
   leaf: 0x141b834 21084212 (1: nrow: 30 rrow: 30) 
   leaf: 0x141b83a 21084218 (2: nrow: 69 rrow: 69)
----- end tree dump

(Split 후)
----- begin tree dump
branch: 0x141b81f 21084191 (0: nrow: 5, level: 1)
   leaf: 0x141b837 21084215 (-1: nrow: 69 rrow: 69)
   leaf: 0x141b83d 21084221 (0: nrow: 40 rrow: 40)
   leaf: 0x141b834 21084212 (1: nrow: 30 rrow: 30)
   leaf: 0x141b83a 21084218 (2: nrow: 68 rrow: 68) 
   leaf: 0x141b840 21084224 (3: nrow: 2 rrow: 2)   
----- end tree dump


3. Branch Node와 Root Node는 항상 50-50 Split을 수행한다. Root Node가 Split되면 Index의 높이가 높아진다.



Index Split은 Index에 키 값이 추가됨에 따라 발생하는 자연스러운 현상이며 인덱스의 균형(Balance)를 맞추는 과정이기도 하다. 교묘한 Index Split 과정에 의해 Oracle은 Index의 균형을 유지하고, 모든 Leaf Node들이 적절하게 Key를 나누어 가지도록 유도한다.

하지만 Index Split은 성능면에서는 매우 불리한 것이다.

- Index Split은 새로운 블록을 할당받고 Key을 옮기는 등의 복잡한 작업을 수행한다. 이 과정들이 모두 Redo에 기록되어야 하기 때문에 많은 량의 Redo를 유발한다.
- Index Split이 이루어지는 동안 해당 블록에 대해 키값이 변경되면 안되기 때문에 DML이 블로킹된다. 이 과정에서 enq: TX - index contention, RAC환경에서는 gc current split 대기 이벤트가 발생한다.

Index Split은 Index Rebuild의 부작용을 언급할 때 반드시 등장하는 개념이다. 이 글을 통해 최소한의 필요한 개념을 익히기를 바래본다. 이 시리즈의 추후 글들에서 다시 한번 이에 대해 언급하게 될 것이다.

다음 글에서는 본격적으로 Index Rebuild와 Index Scan의 성능에 대해 논의하게 될 것이다.

참조) Index Split에 의한 enq: TX - index contention 이벤트와 gc current split 이벤트 현상의 원인과 해결책은 아래 책에 상세히 나와있다.(내가 적은 책을 쓸려니 어색하기 하지만...)

http://book.interpark.com/product/BookDisplay.do?_method=Detail&sc.shopNo=0000400000&sc.dispNo=&sc.prdNo=11505916
http://book.interpark.com/product/BookDisplay.do?_method=Detail&sc.shopNo=0000400000&sc.dispNo=&sc.prdNo=200701633




신고
Trackback 0 : Comments 3
  1. hoho 2008.05.20 08:04 신고 Modify/Delete Reply

    좋은글 잘 읽었습니다~
    "다음 글에서는 본격적으로 Index Rebuild와 Index Scan의 성능에 대해 논의하게 될 것이다.
    다음글은 언제 나오나요?
    넘 궁금~~

  2. 욱짜 2008.05.20 14:30 신고 Modify/Delete Reply

    어떻게 하다 보니까 완결을 못지었네요. 다만 다음 URL에서 제가 세미나에서 사용하는 교재의 온라인 버전이 있습니다. 필요하신 분은 참조하세요.
    http://wiki.ex-em.com/index.php/Oracle_is_mad#Index_Rebuild_.EB.85.BC.EC.9F.81

  3. 욱짜 2009.01.11 00:35 신고 Modify/Delete Reply

    혼란을 일으킬 의도는 당연히 없습니다. ^^

    사실 가이드라인이라는게 무의미합니다. 어떤 곳에서는 Index Rebuild를 한번도 하지 않았는데도 문제없이 운영되는 곳도 있고, 어떤 곳에서는 매우 자주 Index Rebuild를 하는 곳도 있습니다. 어떤 곳에서는 문제가 전혀 없는데도 Index의 크기가 기준치 이상 커지면 자동으로 Rebuild하는 곳도 있습니다.

    제가 의도하는 것은 왜 이런 일이 발생하는가? 이것을 좀 더 체계적으로 논의하고자 하는 것입니다. 이런 표현을 하셨죠.

    ------------------------------------------------------
    이것은 실무가 테스트 처럼 단순하지 않기 때문에
    delete된 공간을 계속 재사용 하지 못해서 늘어나겠죠..
    이런 상황이 계속 이어지다 보면 결국 index의 속도는 느려지게 되어 있습니다.
    ------------------------------------------------------

    왜 Delete된 공간이 재사용되지 못할까요? 이것은 실무와 테스트의 문제가 아니라 Oracle의 동작 방식에 관한 문젭니다. 어떤 경우에는 Oracle은 재사용을 매우 잘하는데, 어떤 경우에는 전혀 재사용을 못하는 것처럼 보입니다.

    또 index의 속도가 느려진다고 하셨는데, index를 scan하는 방식은 다양합니다. 그 중에 정확하게 어떤 Operation이 문제가 되고 있는 것일까요?

    제가 의도하는 역할은 그 메커니즘을 이해하자는 것이고
    현재 실제 운영하고 있는 시스템에서 Index 크기 문제와 그로 인한 성능 저하 현상이 어떻게 발생하며 어떻게 해결하는지는 철저하게 담당자의 몫이라고 생각합니다.

    결국 온라인에서 제가 할 수 있는 일은 의사로 말하면 과학적인 의학 상식을 공유하는 것이고, 실제 병을 어떻게 예방하고 치료하는 가는 개개인의 몫이거나 만일 전담의사(지원 엔지니어나 컨설턴트)가 있다면 그 분들의 몫이 될겁니다.

Write a comment


[Oracle is Mad] Index Rebuild를 둘러싼 논쟁 - Part2

오라클 2007.12.24 22:38
Index Rebuild는 Oracle과 관련된 논쟁들중에서 가장 뜨거운 쟁점에 속한다.
이 논쟁을 이해하기 위해서 필요한 몇가지 지식을 정리해보자.

우리가 흔히 사용하는 B*Tree Index는 다음과 같은 속성을 지닌다.
  • B*Tree에서 "B"는 Balanced의 약자이다.
  • B*Tree Index는 Root->Branch->Leaf의 Tree 구조를 가지며, Tree 높이는 키의 수에 따라 결정된다.
  • Balanced란 Index 키 값에 무관하게 Root Node에서 Leaf Node로 가는 경로의 길이가 일정하다는 의미이다.
  • B*Tree Index는 상당히 효율적이어서 대부분의 대용량 데이터에서도 Root Node에서 Leaf Node까지의 탐색 길이가 3~4 블록 정도에 불과하다. 이 말은 특정 키 값을 검색하는데 불과 3~4 블록 정도의 Logical Reads만이 필요하다는 의미이다.
  • Leaf Node는 정렬된 상태로 연결되어 있다. 따라서 Leaf Node에서 하나의 키 값을 찾으면 정렬된 형태로 다음 키 값을 매우 손쉽게(Leaf Node만 따라가면 되므로) 얻을 수 있다.
B*Tree Index를 그림으로 표현하면 아래 그림과 같다.
사용자 삽입 이미지

여기까지는 너무나 상식적인 내용이므로 별도의 설명은 사족에 불과하다.

여기서의 핵심은 B*Tree Index는 매우 효과적인 탐색 구조를 가지고 있으며, 키 값 탐색에 매우 적은 량의 Logical Reads만이 필요하다는 것이다.

그럼에도 불구하고 왜 많은 사람들이 Index를 Rebuild하라고 권고하고, 또 많은 시스템에서 실제로 Index Rebuild를 수행하는가? 그 이유는 매우 많다.

하지만, 일차적으로 다음과 같은 잘못된 지식이 Index Rebuild를 부추기는 원인을 제공했다.

"Index에서 삭제된 Entry(키 값)나 Block은 재사용되지 않는다"

위와 같은 잘못된 지식들이 어떤 알 수 없는 이유로(아마 외국에서 발행된 책이나 자료 등에서...) 몇 몇 엔지니어사이에서 회자되었고 그 결과로 상당히 넓은 범위로 이런 지식들이 보급된 것으로 생각된다.

위의 잘못된 사실을 연쇄적으로 다음과 같은 오해를 불러일으킨다.
  • 이전 값이 계속해서 삭제되고, 새로운 값이 계속해서 추가되는 환경을 생각해보자.
  • 삭제된 Entry나 Block은 재사용되지 않으므로 Index의 크기는 계속적으로 증가할 것이다.
  • Index의 크기가 늘어남으로써 Logical Reads가 증가하고 공간 요구량은 터무니없이 증가할 것이다.
생각만 해도 끔찍하지 않은가? 이렇게 생각한다면 누구라도 alter index ... rebuild 명령문을 당장 실행하고 싶어질 것이다.

하지만 다행히 위의 지식은 완전히 잘못된 것이다. 실제로는 다음과 같이 말해야 정확한 것이다.

- Deleted Entries는 다음번 Insertion시에 정리된다.
가령 특정 Leaf Node Block에 (1,2,3,4,5) Entry가 있다. 한 세션이 2를 삭제하고 Commit하면 (1,-,3,4,5)가 된다. 다른 세션이 여기에 6을 Insertion하면? (1,-,3,4,5,6)이 아니라 (1,3,4,5,6)과 같이 삭제된 Entry가 정리된다.

- Deleted Block은 New Block이 필요할 때 재활용된다.
Deleted Block은 현재 모든 Entry가 삭제된 Block을 말한다. 이런 Block들은 Leaf Node에 텅 빈 채로 남아 있다가 새로운 Leaf Node나 Branch Node가 필요할 때(New Block) 재활용된다.

아래 URL에 위의 사실을 증명하는 간단한 Case Study가 있다.

http://wiki.ex-em.com/index.php/Oracle_is_mad#Script_.28Index_Space_Reuse.29

Index에서 삭제된 Entry나 Block은 재사용되지 않는다는 것은 완전히 잘못된 것이다. 따라서 이러한 잘못된 믿음이 Index Rebuild를 수행하는 근거가 되어서는 안된다.

오히려 다음과 같은 사실을 걱정해야 한다.
  • 삭제된 Entry는 해당 Leaf Node에 새로운 Insertion이 발생하지 않으면 삭제된 상태로 남아 있다.
  • 텅빈 Block은 새로운 Block 요청에 의해 재사용되기 전까지는 그대로 남아 있다.
즉, 삭제된 Entry나 Block들은 Index Tree 구조에 그대로 남아 있다. 이것은 너무나 당연한 결과이지만, 이로 인해 Index Scan 성능이 떨어질 수 있다. 여기서 오는 공포심(?)이 Index Rebuild를 조장하는 가장 중요한 원인일 것이다. 다음 Part에서 이 문제를 다루기로 한다.


신고
Trackback 0 : Comments 4
  1. 이민규 2008.06.13 17:05 신고 Modify/Delete Reply

    질문 : Deleted Entries는 다음번 Insertion시에 정리된다. 라고 하셨는데요

    index 생성시 pctused의 개념이 없습니다. 그렇다면 어떤 index가 1~100의 값으로 차있는 상태에서
    50을 지우고 다시 50을 insert 하는 경우 원래 block에 들어 갈 수 있다는 말씀인가요?

    그렇다면 index rebuild를 왜 하는건가요? 즉 데이타의 증가가 없다면 split이 발생하지 않고 말씀 하신대로라면
    인덱스 rebuild가 필요 없다는 말씀인데요....설명 부탁드립니다.

  2. 이민규 2008.06.13 17:08 신고 Modify/Delete Reply

    Deleted Entries는 다음번 Insertion시에 정리된다.의 의미가 pctfree를 넘지 않은 블럭에만 해당되는것 아닌가요?

  3. 이민규 2008.06.13 19:28 신고 Modify/Delete Reply

    테스트 수행 결과 욱짜님 말씀이 맞습니다. 그동안 잘못 알고 있었습니다. ...감사합니다.

  4. 욱짜 2008.06.13 21:09 신고 Modify/Delete Reply

    http://ukja.tistory.com/138
    참조하세요.

Write a comment


[Oracle is Mad] Index Rebuild를 둘러싼 논쟁 - Part1

오라클 2007.12.20 22:00
얼마전 Oracle 관련된 교육을 하면서 다음과 같은 질문을 한 적이 있다.

Index Rebuild를 하는가? 한다면 왜, 어떤 주기로 하는가?

대답은 제 각각 이었다. 가령..
  • 2달에 한 번씩 인덱스 높이와 크기를 체크해서 기준치 이상이면 Rebuild를 한다.
  • 3달에 한번씩 인덱스 크기가 어느 정도 커지면 Rebuild를 한다.
  • 인덱스 테이블스페이스 공간 부족이 생기면 Rebuild를 한다.
Rebuild를 하는 방법은 어떤가?라는 질문에는 다음과 같은 두 가지 종류의 답변이 나왔다.
  • 새벽 시간에 Offline Rebuild(alter index i rebuild)을 수행한다.
  • 한가한 시간에 Online Rebuild(alter index i rebuild online)을 수행한다.
놀라운 것은(실제로는 전혀 놀랍지 않은 것이지만) 대부분의 시스템에서 Index Rebuild를 수행하고 있다는 것이이다. 그리고 더 놀라운 것은 본인들이 왜 Index를 주기적으로 Rebuild하고 있는지 이유를 잘 모른다는 것이다. 혹은 안다고 착각하든가...

우선 우리가 너무나 당연하게 여기는 Index Rebuild가 실제로는 대단한 논쟁 거리라는 것을 일단 말해 둔다.

Oracle Forum과 같은 온라인 커뮤니티에 "Index Rebuild를 해야 할까요?"라는 질문을 올릴려면 세계적으로 저명한 Oracle 전문가들에게 두드려 맞을 각오를 해야 한다. Index Rebuild를 수행하는 명백한 이유를 설명하지 못하면 그야말로 본전도 못찾고 서둘러 로그 아웃을 해야할 것이다.

왜 이런 일이 벌어지는 것일까? 대부분의 시스템에서 Index Rebuild를 수행하고 있음에도 불구하고, 왜 대부분의 세계적인 전문가들은 Index Rebuild에 회의적인 입장일까?

여기에는 Index에 대한 오해와 편견, 그리고 Index를 Rebuild하는 방법에 대한 무지등이 복합적으로 작용하고 있다. 하지만, 진실을 알게 되면 누구나 합리적인 판단을 할 수 있을 것으로 믿는다.

앞으로 몇 차례의 글을 통해 B*Tree 인덱스의 메커니즘과 Index Rebuild의 정체에 대해 심도깊은 논의를 해보기로 한다. 이 글이 마칠 때쯤이면 적어도 글을 접한 사람들은 Index Rebuild에 대해 좀 더 합리적인 태도를 취할 수 있을 것으로 기대한다.





신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바