태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'10.2.0.4'에 해당되는 글 1건

  1. 2008.02.25 Oracle 10g의 재미있는 버그 - Index Rebuild 후 Logical Reads 증가 현상 (3)

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

티스토리 툴바