태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

[오라클이 미쳤어요] Index Range Scan이 비효율적예요...

오라클 2007.11.10 17:05
요 즘 [오라클이 미쳤어요]라는 시리즈를 구상중이다. 오라클이 비상식적인 이상 행동을 보이는 경우, 그 이유를 오라클의 내부 동작 메커니즘에 대한 지식을 기반으로 분석해보려는 시도이다. 틈틈이 아이디어가 떠오르는데로 블로그를 통해서 검증을 받고자 한다.

오늘의 [오라클이 미쳤어요]는 키 값이 별로 없는데도 불구하고 Index Range Scan이 비효율적인 경우에 대한 사례이다.

다음과 같은 시나리오를 가정해보자.

CREATE TABLE index_test(id INT);
...
CREATE INDEX index_test_idx ON index_test(id);

SELECT count(*) FROM index_test;
==> 1569408

SELECT max(id) FROM index_test;
==> 1000000

SELECT count(*) FROM index_test WHERE id <= 2;
==> 12

즉, 전체 데이터는 150만건이고, ID 컬럼 값이 2보다 작거나 같은 경우는 오직 12건에 불과하다. 그리고 최대 값은 1,000,000이다.
이제 다음과 같이 12건만을 제외하고 모든 데이터를 삭제한다.

DELETE FROM index_test WHERE id > 2;

이제 12건만의 데이터가 남았고, 인덱스의 경우 제일 왼쪽 Leaf Block에만 12건에 대한 키값이 존재하게 된다.
이 상황에서 다음과 같이 Index Range Scan을 하면 어떻게 될까?

SELECT /*+ INDEX(index_test index_test_idx) */ * FROM index_test WHERE id < 1000000;

다음과 같은 두 가지 대답이 가능할 것이다.

1) 1,000,000 이하의 값은 단 12개만 존재하고 이 값들은 다 제일 왼쪽 Leaf Block에만 존재하므로 Logical Reads는 불과 3 블록 정도에 불과할 것이다.
2) 비록 12개의 값만 존재하지만 Oracle은 각 Leaf Block들에 어떤 값들이 있는지 알 수 없으므로 결국 한 때 1,000,000보다 작았던 모든 Leaf Block들을 다 방문해야할 것이다. 따라서 Logical Reads는 값을 삭제하기 전과 거의 동일할 것이다.

상식적으로 생각하면 당연히 1)번으로 구현되었을 것 같다. 하지만 불행하게도 2)번이 정답이다. Branch 노드는 여전히 1,000,000보다 작은 값을 가지는 Leaf Block에 대한 정보를 가지고 있으며 이 Leaf Block을 방문해보아야 만 값이 있는지 없는지 알 수 있기 때문이다. 현재 존재하는 키의 수만 생각하면 마치 오라클이 이상 동작으로 생각하기 십상이다.

이것은 여러 가지 방법으로 증명할 수 있다.

우선 tkprof의 결과는 다음과 같다.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3490 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 3490 0 12

결과를 보면 12개의 값(rows)을 얻기 위해 무려 3,490블록의 Logical Reads가 수행되었음을 알 수 있다.

Index Tree Dump를 이용하면 Branch Block과 Leaf Block의 분포를 알 수 있다.

SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 67513' (Index Id)

----- begin tree dump
branch: 0x1000124 16777508 (0: nrow: 6, level: 2)
   branch: 0x100b1ca 16822730 (-1: nrow: 557, level: 1)
      leaf: 0x1000125 16777509 (-1: nrow: 512 rrow: 12)
      leaf: 0x1000126 16777510 (0: nrow: 484 rrow: 0)
      leaf: 0x1000127 16777511 (1: nrow: 479 rrow: 0)
      leaf: 0x1000128 16777512 (2: nrow: 479 rrow: 0)
      leaf: 0x1000139 16777529 (3: nrow: 479 rrow: 0)
      leaf: 0x100013a 16777530 (4: nrow: 478 rrow: 0)
      ...
  branch: 0x100b401 16823297 (0: nrow: 558, level: 1)
     leaf: 0x100b1c9 16822729 (-1: nrow: 449 rrow: 0)
     leaf: 0x100b1cb 16822731 (0: nrow: 449 rrow: 0)
     leaf: 0x100b1cc 16822732 (1: nrow: 449 rrow: 0)
     ...

==> leaf:3488, branch: 7

Logical Reads값이 Leaf Block과 Branch Block의 수를 합친 것과 거의 유사한 것을 알 수 있다. 즉, 거의 모든 Block들을 다 방문한 후에야 Query 수행이 끝났음을 의미한다.

10200 Event를 이용하면 매우 극적인 증명이 가능하다. 10200 Event는 CR Reads에 대한 Trace 기능을 제공한다.
SQL> ALTER SESSION SET EVENTS '10200 trace name context forever, level 1';
이후 Query를 수행하면 해당 Query의 Logical Reads(어떤 블록을 읽었는지)를 거의 완벽하게 Trace할 수 있다.

이 이벤트를 이용해서 Logical Reads를 Trace해보면 Index의 Leaf Block을 일일이 방문했음을 확인할 수 있다.

위의 내용들과 더불어 오라클에 대한 몇 가지 상식을 이용하면 다음과 같은 몇 가지 사실들을 도출할 수 있다.
1) Leaf Block이 다 Delete(Empty)되어도 특정 키가 있는지 없는지는 방문해보아야 알 수 있다.
2) 따라서 많은 키 값을 가지고 있다가 키 값들이 거의 삭제된 Index에 대한 Range Scan은 여전히 많은 Logical Reads를 수반할 수 있다. 다행히 이런 상황은 매우 드물다.
3) Index에 대한 Coalesce 작업이나 Rebuild 작업은 이런 특수한 경우에만 필요하다.

그런데, 텅 빈 Leaf Block들은 어디로 가는 것일까? Empty Leaf Block을 어디론가 옮기거나 하는 작업은 발생하지 않는다. FLM에서라면 Free List에 등록되어 있을 것이고 ASSM에서라면 Free 상태의 블록으로 BMB에 기록되어 있을 것이다. 그리고 이후 완전히 새로운 Leaf Block이 필요한 시점(Index Split 등)에 재활용된다.



신고
Trackback 0 : Comment 1
  1. Ejql 2010.06.25 09:57 신고 Modify/Delete Reply

    예전에 한번 읽었는데 그때는 전혀 모르겠던데.. 지금은 아주 좋은 내용인것을 알 수 있네요.
    감사합니다.

Write a comment

티스토리 툴바