태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

Oracle 10g vs. Oracle 11g - Buffer Pinning and Logcial Reads

오라클 2008.01.21 20:52

(실수로 글을 삭제해서 다시 올립니다)

아래의 간단한 테스트 결과를 보자.

create table t_join1(id int, name char(10));
create table t_join2(id int, name char(10));

create index t_join1_idx on t_join1(id);
create index t_join2_idx on t_join2(id);

insert into t_join1
select rownum, 'name' from all_objects where rownum <= 10000;

insert into t_join2
select rownum, 'name' from all_objects where rownum <= 5000;

commit;

exec dbms_stats.gather_table_stats(user, 'T_JOIN1', cascade=>true);
exec dbms_stats.gather_table_stats(user, 'T_JOIN2', cascade=>true);

select /*+ gather_plan_statistics ordered use_nl(t_join1 t_join2) */
   t_join1.id
from t_join1, t_join2
where t_join1.id = t_join2.id;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

즉, 10000건의 t_join1 테이블과 5000건의 t_join2 테이블을 Index만을 경유해서 Nested Loop 조인해서 가져오는 쿼리를 수행하는 간단한 테스트이다.

결과를 보기 전에 예측을 해볼 수 있다.

1. Hint에 의해 t_join_idx1 인덱스를 Full Scan하면서 t_join2_idx 인덱스를 매번 방문하는 Nested Loop Join 실행 경로를 따른다.
2. 두 인덱스 모두 높이가 2이다. (analyze index <index_name> validate structure 로 확인 가능)
3. t_join1 테이블이 로우 수가 10,000 건이므로 t_join2_idx 인덱스를 10,000번 경유해야 한다. t_join2_idx 인덱스의 높이가 2이므로 한번 액세스마다 2블록을 읽어야 한다.
따라서 t_join2_idx 인덱스에 대한 Logical Reads 20,000 블록 정도가 되어야 하며, 이것이 가장 성능에 많은 영향을 미친다.

아래 결과를 보자. (gather_plan_statistics 힌트를 이용한다. 자세한 내용은 여기를 참조한다)

일단 10g에서의 결과는 다음과 같다.
-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS         |             |      1 |   5000 |   5002 |00:00:00.04 |   11056 |
|   2 |   INDEX FAST FULL SCAN| T_JOIN1_IDX |      1 |  10000 |  10000 |00:00:00.01 |     378 |
|*  3 |   INDEX RANGE SCAN    | T_JOIN2_IDX |  10000 |      1 |   5002 |00:00:00.05 |   10678 |
-----------------------------------------------------------------------------------------------


일단 예상대로 t_join2_idx 에 대한 일량이 대부분을 차지한다. 하지만 우리가 예상한 20,000 블록의 절반 정도에 불과한 것을 알 수 있다. 왜 이런 현상이 발생할까?

이것은 Buffer Pinning이라고 부르는 효과에 의한 것이다. 오라클은 한번의 콜(Fetch)에서 연속적으로 액세스되는 블록을 "Pin"해서 추가적으로 다시 SGA를 탐색하는 과정없이 재활용할 수 있도록 해준다. 위의 결과를 보면 우리가 예상한 20,000 블록이 아닌 그 절반 정도에 해당하는 10,000 블록만을 읽었음을 보여준다. 오라클은 루트 노드에 해당하는 블록에 대해서 Buffer Pinning을 수행해서 그만큼 블록 읽기 회수를 줄여주었다.

Buffer Pinning에 의한 블록을 재활용한 회수는 buffer is pinned count 라는 통계값으로 확인 가능한데, 실제로 위의 테스트를 수행하면 buffer is pinned count는 약 10,000 블록의 값을 보인다. 즉, 루트 노드에 대한 반복적인 읽기 작업을 절약했다는 의미이다.

이제 11g에서의 테스트 결과를 보자.

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS         |             |      1 |   5000 |   5000 |00:00:00.12 |    1716 |
|   2 |   INDEX FAST FULL SCAN| T_JOIN1_IDX |      1 |  10000 |  10000 |00:00:00.02 |     673 |
|*  3 |   INDEX RANGE SCAN    | T_JOIN2_IDX |  10000 |      1 |   5000 |00:00:00.07 |    1043 |
-----------------------------------------------------------------------------------------------


놀랍게도 t_join2_idx 인덱스에 대한 Logical Reads 10g에서의 1/10 수준에 불과하다. 이것은 오라클이 Buffer Pinning에 의한 Logical Reads 줄이기를 매우 적극적으로 수행한다는 것을 암시하며, 인덱스의 리프 노드에 대해서도 Buffer Pinning이 적용된다는 것을 의미한다. 즉 한번의 콜(Fetch)에서 연속적으로 액세스되는 리프 노드에 대해서도 오라클은 추가적인 Logical Reads를 일으키지 않는다.

한가지 이상한 것은 이러한 개선된 Buffer Pinning을 관찰할 지표를 알 수 없다는 것이다. buffer is pinned count 통계값에는 이러한 현상이 반영되지 않는다. 아쉽게도 어떤 값을 통해서 리프 노드에 대한 Buffer Pinning이 발생했는지 확인할 수 있는지는 아직 밝혀내지 못한 상태이다.

한 가지 주의할 것은 위와 같은 Buffer Pinning의 효과는 한번의 콜(Fetch)에서 동일 블록을 액세스할 때만 의미가 있다는 것이다. 위의 테스트에서는 t_join1_idx 인덱스와 t_join2_idx 인덱스가 거의 동일하게 정렬이 되어 있으므로 성능 개선 효과가 아주 극단적으로 개선된 것처럼 보인다.
만일 정렬 순서가 심하게 흩어져 있다면 효과는 크게 반감될 것이다.

위의 테스트 결과는 오라클이 Nested Loop Join에 의한 Random Access I/O를 줄이기 위해 얼마나 많은 노력을 기울지는를 잘 보여준다. Nested Loop Join에 의한 Random I/O는 비용이 매우 크기 때문에 일반적으로 대량의 데이터에대해서는 Nested Loop Join보다는 Hash Join을 선호하게 된다. 하지만 위의 테스트 결과가 보는 것처럼, 11g에서는대량의 데이터에 대한 Nested Loop Join이 더 이상 과거처럼 비효율적인지 않을 수도 있음을 알 수 있다.

오라클의 노력이 가상할 뿐이다.

(참고) 위와 같은 확장된 Buffer Pinning 효과는 굳이 NL Join 뿐만 아니라 인덱스 리프 노드를 반복적으로 액세스하는 모든 오퍼레이션에서 동일하게 적용된다. 가령 In (1,2,3,4, ..., 100) 과 같은 InList Operation도 동일한 혜택을 받게 된다.

신고
Trackback 1 : Comments 2
  1. 멀더엄마 2008.02.01 11:05 신고 Modify/Delete Reply

    EXPLAIN PLAN FOR
    select /*+ gather_plan_statiscs ordered use_nl (tmp_join1 tmp_join2) */
    tmp_join1.id
    from tmp_join1, tmp_join2
    where tmp_join1.id = tmp_join2.id;

    SELECT * FROM TABLE(dbms_xplan.display);

    이렇게하면 플랜이 조회가 되는데...


    select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
    이 쿼리를 수행할때 ORA-00904 열명이 부적합합니다. 이러케 나오네용. 아....... 9i 라서 그런감?

  2. 욱짜 2008.02.01 14:05 신고 Modify/Delete Reply

    gather_plan_statistics 힌트와 dbms_xplan.dispaly_cursor는 10g부터만 지원되는 기능이라서~

Write a comment

티스토리 툴바