태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'buffer pinning'에 해당되는 글 4건

  1. 2009.05.19 Buffer Pinning, Logical Reads, Elapsed Time (4)
  2. 2008.09.29 Advanced Nested Loop Join? Table Prefetching? Buffer Pinning? (15)
  3. 2008.02.25 Oracle 10g의 재미있는 버그 - Index Rebuild 후 Logical Reads 증가 현상 (3)
  4. 2008.01.21 Oracle 10g vs. Oracle 11g - Buffer Pinning and Logcial Reads (2)

Buffer Pinning, Logical Reads, Elapsed Time

오라클 2009.05.19 11:42
SQL*Trace는 Query가 어떻게 얼마나 효율적으로 수행되는지를 파악하는 가장 기본적인 도구이다.

하지만 그 결과를 해석할 때 조심해야 할 몇 가지 함정들이 있다. 그 중에서도 상당히 해석하기가 까다로운 사례를 하나 보자.

아래 결과를 면밀히 비교해 보면...

SELECT /*+ index(t1 t1(c1)) */ COUNT(C3)
FROM
 T1 WHERE C1 > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.00       0.00          0          0          0           0
Fetch      100     11.51      11.51          0     178800          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201     11.51      11.52          0     178800          0         100

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  SORT AGGREGATE (cr=178800 pr=0 pw=0 time=11513907 us)
10000000   TABLE ACCESS BY INDEX ROWID T1 (cr=178800 pr=0 pw=0 time=166705427 us)
10000000    INDEX RANGE SCAN T1_N1 (cr=20000 pr=0 pw=0 time=40426316 us)



SELECT /*+ index(t1 t1(c2)) */ COUNT(C3)
FROM
 T1 WHERE C2 > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.00       0.00          0          0          0           0
Fetch      100     25.78      25.77          0    9711400          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201     25.78      25.78          0    9711400          0         100

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  SORT AGGREGATE (cr=9711400 pr=0 pw=0 time=25775700 us)
9990800   TABLE ACCESS BY INDEX ROWID T1 (cr=9711400 pr=0 pw=0 time=184934979 us)
9990800    INDEX RANGE SCAN T1_N2 (cr=25700 pr=0 pw=0 time=40795883 us)


무언가 비상식적인 것을 발견할 수 있다.

Logical Reads의 차이는 178800:9711400 = 약 54배의 차이
하지만 실행 시간의 차이는 11.52:25.78 = 약 2.2배의 차이

왜 Logical Reads의 차이는 엄청나게 큼에도 불구하고 실행 시간의 차이는 고작 2배 정도에 불과할까? 불행하게도 SQL*Trace(+tkprof)의 결과만으로는 해석이 불가능하다.

우선 Index의 통계정보를 보면 재미있는 사실을 알 수 있다.

UKJA@ukja102> @ind_stat t1
UKJA@ukja102> set echo off
TABLE_NAME                    : T1                                             
INDEX_NAME                    : T1_N1                                          
BLEVEL                        : 1                                              
LEAF_BLOCKS                   : 199                                            
DISTINCT_KEYS                 : 100000                                         
CLUSTERING_FACTOR             : 1588             <-- Good CF                                
SAMPLE_SIZE                   : 100000                                         
GLOBAL_STATS                  : YES                                            
LAST_ANAL                     : 2009/05/19 11:25:16                            
-----------------                                                              
TABLE_NAME                    : T1                                             
INDEX_NAME                    : T1_N2                                          
BLEVEL                        : 1                                              
LEAF_BLOCKS                   : 256                                            
DISTINCT_KEYS                 : 1000                                           
CLUSTERING_FACTOR             : 96947          <-- Bad CF                                   
SAMPLE_SIZE                   : 100000                                         
GLOBAL_STATS                  : YES                                            
LAST_ANAL                     : 2009/05/19 11:25:17                            
-----------------                      
                                        

Index t1_n1과 t1_n2 사이의 큰 차이가 Clustering Factor에 있다는 것을 알 수 있다. 여기까지 오면 경험이 있는 분들은 아항! Clustering Factor의 차이에 의해 buffer pinning이 이루어진 회수의 차이 때문이구나! 라는 추측을 하게 될 것이다.

이것을 증명하기 위해서는 SQL*Trace 외에도 V$SESSTAT 같은 뷰를 통해 실행 시점의 일량을 파악해야 한다.

NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
buffer is pinned count                     19,841,100   10,295,800   -9,545,300
...
consistent gets                               178,815    9,711,414    9,532,599

Logical Reads의 차이만큼 buffer is pinned count가 이루어진 것을 알 수 있다. Buffer Pinning에 의해 Block을 다시 읽을 때는 Latch를 획득하고 Cache Chain을 탐색하는 일련의 과정은 없어지지만 Buffer를 읽는 행위 자체는 발생할 수 있다. 따라서 수행 시간이 수행 회수에 비례해서 감소하지는 않는 것이다.

그렇다면 Latch 획득은 얼마나 차이가 날까?

LATCH_NAME                         D_GETS   D_MISSES   D_SLEEPS  D_IM_GETS     
------------------------------ ---------- ---------- ---------- ----------     
cache buffers chains             19065334          0          0         17     
simulator hash latch               577910          0          0          0     
simulator lru latch                577909          1          0          1     
...


cache buffers chains latch의 획득 회수의 차이는 19,065,334인데 이 값은 Logical Reads의 차이인 9,532,59의 2배에 해당한다는 것을 알 수 있다.

Oracle은 한번의 Logical Reads에 대해 2번의 Latch 획득과 한번의 Latch Upgrade를 한다는 것의 간접적인 증명이기도 하다.

SQL*Trace(+tkprof)의 결과를 항상 100% 숫자 그대로 믿을 수 없는 상황이 의외로 많이 존재한다는 사실을 명심하기를! 분석할 Data는 많을수록 좋고 서로가 서로를 보완 설명하는 경우가 많다는 것도 기억하자.

PS) Buffer Pinning의 개념은 여기에서 얻을 수 있다.
신고

'오라클' 카테고리의 다른 글

SIMILAR cursor sharing의 문제  (0) 2009.06.02
Index 공간 재활용에 대한 오해는 뿌리 깊다.  (0) 2009.05.27
Buffer Pinning, Logical Reads, Elapsed Time  (4) 2009.05.19
Bind 변수 값 알아내기  (1) 2009.05.07
V$SQL_HINT View  (0) 2009.05.04
Trackback 0 : Comments 4
  1. 김진호 2009.09.03 13:24 신고 Modify/Delete Reply

    동욱님 죄송한데 질문하나 드려도 될런지..
    tkprof 분석결과가
    select *
    from
    big_emp


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.03 0.01 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1932 0.01 0.04 0 2109 0 28955
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 1934 0.04 0.05 0 2109 0 28955

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 54
    이렇게 나왔습니다.
    제가 질문드리고 하는게 cpu time이 elapsed time보다 왜 더 많이 나오는것일까요? 제상식으로는 이해가 안되네요. 조언좀 부탁드립니다.

    • 욱짜 2009.09.03 17:02 신고 Modify/Delete

      번거로우시겠지만, 위의 질문을 다른 분들도 공유할 수 있도록 Ask Exem에 올려주시겠습니까?

      http://ask.ex-em.com

      Email 주소만 정확하게 적어주시면 게시판을 통해 질문/답변/의견 교환이 가능합니다. Email 주소는 외부에 노출되지 않으므로 걱정하지 않으셔도 됩니다.

  2. 그와함께 2009.09.04 09:51 신고 Modify/Delete Reply

    Tom Kyte의 Effective Oracle by Design 책에 이 문제에 관한 답이 나와 있습니다. 확인해보세요.

  3. 햇살아이 2011.03.23 10:57 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


Advanced Nested Loop Join? Table Prefetching? Buffer Pinning?

오라클 2008.09.29 14:12
(이 글에서 사용된 Nested Loop, Table Prefetching, Buffer Pinning 등의 용어에 대한 토론을 Comment를 통해서 반드시 확인하세요. 그리고 Table (Lookup) Prefetching에 대한 자세한 설명은 Metalink 406966.1를 참조하세요)

얼마전 다음과 같은 내용의 문의를 받았다.

"Oracle 10g(10.2.0.4)에서 Nested Loop Join의 순서를 Oracle 8i와 동일하게 할 수 없는가?"

무슨 말인고 하면...

Oracle 8i에서는 Nested Loop Join이 다음과 같은 순서로 실행 계획상에 나타난다.

select /*+ use_nl(t1 t2) full(t1) index(t2) */
  count(t2.c2)
from t1, t2
where t1.c1  = t2.c1
;

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
  10000   NESTED LOOPS
  10001    TABLE ACCESS FULL T1
  10000    TABLE ACCESS BY INDEX ROWID T2
  20000     INDEX RANGE SCAN (object id 14645)

하지만 9i 부터는 순서가 다음과 같이 바뀐다. TABLE ACCESS BY INDEX ROWID T2 단계가 NESTED LOOP의 바깥에 존재한다!!!

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
  10000   TABLE ACCESS BY INDEX ROWID T2
  20001    NESTED LOOPS 
  10000     TABLE ACCESS FULL T1
  10000     INDEX RANGE SCAN T2_N1 (object id 31030)

무슨 일이 생긴 것인가?

이 현상을 [새로쓴 대용량 데이터베이스 솔루션 I]에서는 "Advanced Nested Loop Join"이라고 부르고 있다. 하지만 이는 불행히도 공식적인 용어가 아니다. 적어도 내가 확인한 바로는 그렇다.

반면에 Jonathan Lewis의 [CBO Fundamentals]라는 책을 보면 이를 "Table Prefetching"이라고 부르고 있다. 비록 Oracle이 이를 공식적으로 설명하고 있지는 않지만 Jonathan Lewis의 용어가 정확하다. 그 이유는 이런 변화가 다음 두 개의 Parameter로 제어하기 때문이다.
(Parameter의 정확한 의미는 추가적인 조사가 필요함)


UKJA@ukja102> @para table_lookup_prefetch
old   9: and i.ksppinm like '%&1%'
new   9: and i.ksppinm like '%table_lookup_prefetch%'

NAME                           VALUE                SES_MODIFI SYS_MODIFI
------------------------------ -------------------- ---------- ----------
DESCRIPTION
--------------------------------------------------------------------------
--------------------------------------------------------------------------

_table_lookup_prefetch_size    40                   false      false
table lookup prefetch vector size

_table_lookup_prefetch_thresh  2                    false      false
table lookup prefetch threshold

Parameter 이름에서 알 수 있듯이 Oracle은 9i부터 Nested Loop Join에 의해 후행 Table을 Lookup할 때 Prefetch, 즉 미리 읽기를 수행하게끔 코드를 수정하였고 그 결과 Nested Loop Join의 순서가 바뀐 것이다.

이것은 순전히 성능을 위해서이다. 실제로 9i와 10g에서 동일한 Nested Loop Join을 수행하면 8i에 비해서 일량이 크게 개선된다.

아래에 간단한 테테스트 결과가 있다.

@capture_on

@version

drop table t1 purge;
drop table t1;

drop table t2 purge;
drop table t2;

create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);

insert into t1
select rownum, rownum
from all_objects, all_objects
where rownum <= 10000
;

insert into t2
select rownum, rownum
from all_objects, all_objects
where rownum <= 10000
;

@trace_on 10046 1

@mysid
@mon_on &v_sid

select /*+ use_nl(t1 t2) full(t1) index(t2) */
  count(t2.c2)
from t1, t2
where t1.c1  = t2.c1
;

@mon_off
@mon_show

@trace_off

@capture_off

8.1.0.7에서의 결과는 다음과 같다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.31       0.32         55      40020          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.31       0.33         55      40020         4           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
  10000   NESTED LOOPS
  10001    TABLE ACCESS FULL T1
  10000    TABLE ACCESS BY INDEX ROWID T2
  20000     INDEX RANGE SCAN (object id 14645)


NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
session logical reads                         138,414      178,618       40,204
consistent gets                               136,126      176,270       40,144
buffer is not pinned count                     81,131      111,200       30,069
no work - consistent read gets                 50,817       80,868       30,051
no buffer to keep pinned count                124,595      144,596       20,001
table fetch by rowid                           29,540       39,571       10,031
table scan rows gotten                            186       10,186       10,000
redo size                                   1,785,720    1,791,048        5,328
bytes received via SQL*Net from client          8,654       10,284        1,630
bytes sent via SQL*Net to client                6,285        7,165          880
recursive calls                                 4,899        5,560          661
sorts (rows)                                       17          307          290
...
buffer is pinned count                             14           38           24

9.2.0.1에서의 결과는 다음과 같다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.05          0      10062          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.06          0      10062         0           1

일량이 40,000 블록에서 10,000 블록으로 현격하게 감소했다! Table Prefetch에 의한 성능 개선 결과이다.

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
  10000   TABLE ACCESS BY INDEX ROWID T2
  20001    NESTED LOOPS 
  10000     TABLE ACCESS FULL T1
  10000     INDEX RANGE SCAN T2_N1 (object id 31030)

********************************************************************************

그 차이는 어디서 온 것인가?
buffer is pinned count 값의 변화에 주목하자. Table Prefetching에 의해 Buffer Pinning이 이루어지고 그 만큼 Logical Reads는 감소한다.

NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
redo size                                   5,056,820    5,117,476       60,656
buffer is pinned count                        315,121      345,100       29,979
session logical reads                         421,465      432,127       10,662
consistent gets                               416,214      426,355       10,141
no work - consistent read gets                173,058      183,136       10,078
shared hash latch upgrades - no wait          254,328      264,344       10,016
index scans kdiixs1                           254,282      264,297       10,015
table fetch by rowid                          132,582      142,597       10,015
table scan rows gotten                         20,497       30,497       10,000

10.2.0.1 또한 9i와 (거의) 비슷한 결과를 보여준다.

select /*+ use_nl(t1 t2) full(t1) index(t2) */
  count(t2.c2)
from t1, t2
where t1.c1  = t2.c1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          4          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.73       0.72          0      10062         0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.73       0.73          0      10066          0           1


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=10062 pr=0 pw=0 time=726161 us)
  10000   TABLE ACCESS BY INDEX ROWID T2 (cr=10062 pr=0 pw=0 time=640128 us)
  20001    NESTED LOOPS  (cr=10044 pr=0 pw=0 time=460735 us)
  10000     TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=40053 us)
  10000     INDEX RANGE SCAN T2_N1 (cr=10021 pr=0 pw=0 time=200364 us)(object id 68407)

NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
physical read total bytes                  20,545,536   20,717,568      172,032
physical read bytes                        20,545,536   20,717,568      172,032
buffer is pinned count                        113,181      153,148       39,967
table scan rows gotten                        218,640      248,640       30,000
sorts (rows)                                  110,074      132,467       22,393
session logical reads                         248,435      259,065       10,630
consistent gets from cache                    246,319      256,891       10,572
consistent gets                               246,319      256,891       10,572
no work - consistent read gets                100,752      110,982       10,230
shared hash latch upgrades - no wait           83,155       93,258       10,103
table fetch by rowid                           46,258       56,358       10,100
index scans kdiixs1                            82,872       92,968       10,096


다음과 같은 문의 내용을 만족하려면?

"Oracle 10g(10.2.0.4)에서 Nested Loop Join의 순서를 Oracle 8i와 동일하게 할 수 없는가?"

두 가지 정도의 옵션이 있다.

1) optimizer_features_enable = '8.1.7'로 변경한다.
2) Hidden Parameter인 _table_lookup_prefetch_size, _table_lookup_prefetch_thresh의 값을 변경(가령 0)한다.

이 경우 10g에서 다음과 같이 8i와 비슷한 형태로 변경된다.

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=20044 pr=0 pw=0 time=825897 us)
  10000   NESTED LOOPS  (cr=20044 pr=0 pw=0 time=800122 us)
  10000    TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=40057 us)
  10000    TABLE ACCESS BY INDEX ROWID T2 (cr=20021 pr=0 pw=0 time=637141 us)
  10000     INDEX RANGE SCAN T2_N1 (cr=10021 pr=0 pw=0 time=202725 us)(object id 68389)

NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
physical read total bytes                   5,914,624    6,086,656      172,032
physical read bytes                         5,914,624    6,086,656      172,032
session pga memory                          2,595,792    2,661,328       65,536
table scan rows gotten                        218,528      248,528       30,000
sorts (rows)                                  101,901      124,294       22,393
session logical reads                         214,015      234,628       20,613
consistent gets from cache                    211,916      232,471       20,555
consistent gets                               211,916      232,471       20,555
buffer is not pinned count                    151,561      171,791       20,230
no work - consistent read gets                 94,773      114,985       20,212
buffer is pinned count                        109,172      129,158       19,986
shared hash latch upgrades - no wait           80,301       90,403       10,102
table fetch by rowid                           35,529       45,629       10,100
index scans kdiixs1                            80,325       90,421       10,096

buffer is pinned count 값이 감소한 만큼 Logical Reads가 증가하게 된다. 물론 성능에는 매우 해롭다고 할 수 있다.

Oracle 11g에서의 변화는 더욱 놀랍다. 아래 결과(11.1.0.6)를 보면 일량이 무려 263으로 줄어들었다.

select /*+ use_nl(t1 t2) full(t1) index(t2) */
  count(t2.c2)
from t1, t2
where t1.c1  = t2.c1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.05          0         50          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.26       0.26          0        213          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.28       0.32          0        263          0           1

더불어 실행 계획은 더 마법같이 변했다. Nested Loop Join이 두 번 발생하는 것으로 관찰된다.

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=213 pr=0 pw=0 time=0 us)
  10000   NESTED LOOPS (cr=213 pr=0 pw=0 time=2804 us)
  10000    NESTED LOOPS  (cr=195 pr=0 pw=0 time=1520 us cost=20026 size=390000 card=10000)
  10000     TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=154 us cost=7 size=130000 card=10000)
  10000     INDEX RANGE SCAN T2_N1 (cr=172 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79713)
  10000    TABLE ACCESS BY INDEX ROWID T2 (cr=18 pr=0 pw=0 time=0 us cost=2 size=26 card=1)


이런 큰 변화는 어디서 발생한 것인가?
정답은 Row Source Operation에 있다. 10g와 11g의 Row Source Operation을 비교해보자.

10g의 경우 Table Prefetching에 의해 Table T2에 대한 Logical Reads가 10062-10021 = 41 블록만 발생한다. (Buffer Pinning) 하지만 Index T2_N1에 대해서는 10021의 높은 Logical Reads를 보인다.

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  SORT AGGREGATE (cr=10062 pr=0 pw=0 time=726161 us)
  10000   TABLE ACCESS BY INDEX ROWID T2 (cr=10062 pr=0 pw=0 time=640128 us)
  20001    NESTED LOOPS  (cr=10044 pr=0 pw=0 time=460735 us)
  10000     TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=40053 us)
  10000     INDEX RANGE SCAN T2_N1 (cr=10021 pr=0 pw=0 time=200364 us)(object id 68407)

11g의 경우 Table Prefetch뿐만 아니라 Index T2_N1에 대한 Prefetch까지 이루어진다. Index T2_N1에 대한 Logical Reads가 10021에서 172로 줄어든 것이 그 증거이다.

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  SORT AGGREGATE (cr=213 pr=0 pw=0 time=0 us)
  10000   NESTED LOOPS  (cr=213 pr=0 pw=0 time=2804 us)
  10000    NESTED LOOPS  (cr=195 pr=0 pw=0 time=1520 us cost=20026 size=390000 card=10000)
  10000     TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=154 us cost=7 size=130000 card=10000)
  10000     INDEX RANGE SCAN T2_N1 (cr=172 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79713)
  10000    TABLE ACCESS BY INDEX ROWID T2 (cr=18 pr=0 pw=0 time=0 us cost=2 size=26 card=1)


이쯤되면 "다른 건 몰라도 Nested Loop Join은 내가 좀 알지"라고 생각했던 것이 매우 좁은 시각이었던 것을 인정해야 할 것이다.

PS) 대용량데이터베이스 솔루션에서 밝힌 Advanced Nested Loop Join이라는 용어가 어디서 근거했는지 모르겠다. 있다면 누군가가 알려주었으면 좋겠다. 잘못된 용어의 사용은 우리가 가장 조심해야할 일이다.





신고
Trackback 0 : Comments 15
  1. 백면서생 2008.09.30 11:38 신고 Modify/Delete Reply

    2001년도인가 DSS관련 9i New Feature오라클 기술백서 문서 보면서 data block prefetching에 관한 내용( data blcok parallel random i/o )을 본적이 있는데 그때 위의 내용을 같이 봤던 기억이 나네요.
    11g는 한단계 더 업그레이드 된 모양이네요.^^
    매번 좋은 자료 감사합니다.
    새로쓴 대용량 데이터베이스 솔류션은 서브쿼리 세미조인 부분도 그렇고 개념적으로 정립이 안된 부분이 조금 있었던 것 같네요(subquery unnesting과 semi join의 불명확한 개념정도로 기억.^^;;).

  2. 욱짜 2008.09.30 16:07 신고 Modify/Delete Reply

    그런 자료가 있었군요. 시간날 때 검색해봐야겠습니다.

  3. oraking 2008.10.02 09:31 신고 Modify/Delete Reply

    안녕하세요... 비투엔 컨설팅 조시형입니다. 이렇게 온라인 상에서 만나 뵙기는 처음인 것 같군요. 회사 일도 바쁘실 텐데 이처럼 온라인 상에서도 활발히 활동하시는 것을 보면 참 부럽습니다.
    "Advanced"라는 용어는 저에게 원죄가 있어 이렇게 몇 자 적어 봅니다. 조동욱 선생께서 여기서 설명한 내용을 제가 2004년도 엔코아 근무시절에 내부 세미나에서 발표한 적이 있는데, 그 때 제가 "Advanced"라는 용어를 사용했었습니다. Inner Table Access가 NL 위쪽으로 올라가는 새로운 NL 조인 메커니즘 뿐 아니라 Clustering Factor가 좋을 때 Logical read가 적게 발생하는 원리도 함께 설명하는 자리였죠. 그 때는 PGA Buffer Cache라는 개념을 이용해 설명했었구요. 즉 한번 읽은 블럭을 PGA에 캐싱함으로써 반복적인 Logical Read를 발생시키지 않는 것이라고 말이죠. 이 개념이 buffer pinning 개념으로 설명돼야 한다는 것을 안 것은 엔코아를 나온 이후 시점이었는데, 엔코아에서 그 자료를 가지고 그대로 책을 기술했더군요. 아차 싶었죠. 하지만 PGA에 Buffer Block을 통째로 Cloning해서 캐싱한다기 보다는 그 블럭 주소(Address)를 캐싱하는 것이라고 이해한다면 크게 잘못된 설명도 아닐 것입니다. Buffer Pinning이라고 하는 것이 바로 그런 것이니까요.
    어쨌든 "Advanced"라는 표현은 저로부터 비롯된 것이라고 볼 수 있는데, NL Join이 이전보다 "진보된" 방식으로 처리된다는 것을 표현하고자 했던 것이므로 그다지 잘못 붙인 이름은 아니라고 생각하는데요...
    오히려 Buffer Pinning에 의한 새로운 처리방식을 "Prefetch"라는 용어를 사용해 설명하는 것은 부적절하지 않나 싶습니다. 물론 _table_lookup_prefetch_size와 _table_lookup_prefetch_thresh 이 두 파라미터에 의해 실행계획이 바뀐다는 사실은 알고 있었지만(참고로, _multi_join_key_table_lookup 파라미터에 의해서도 바뀜), 이들 파라미터의 값들이 의미하는 바를 정확히 밝혀내지 못하는 이상 Buffer Pinning에 의한 CR감소 효과를 table prefetch라고 명명하는 것은 좀 무리가 아닐까요? (이들 파라미터 의미에 대해 저도 나름대로 심증가는 부분이 있지만 증명하기가 어렵더군요.)
    오라클은 Buffer Pinning을 적용하는 지점을 점차 확대시켜 나가고 있고, NL 조인 메커니즘에서의 개선도 그 중 하나일 뿐이라고 생각합니다.
    - range scan에서의 Index Leaf Block(7 이전)
    - 인덱스로부터 액세스되는 테이블 블럭(8i)
    - NL 조인시 Inner Index Root Block(9i)
    - NL 조인시 Inner Table Block(9i)
    - Index Skip Scan에서의 Branch Block(9i)
    - NL 조인시 Inner Index Block(11g)
    - DML문이 수행되는 동안의 Undo Block(어느 버전부터인지 모르겠지만) 등

    제가 아는 한 Prefetch라는 용어는 Disk I/O와 관련 있습니다. Index Prefetch도 오래 전부터 사용돼 오고 있지만 여기서는 9i부터 사용되기 시작한 Table Prefetch에 대해서만 논하고자 합니다.
    인덱스를 경유해 rowid를 얻고 테이블을 액세스하려고 하는데 Buffer Cache에서 찾지 못하면 물리적인 I/O request가 필요해지죠. 이 때, 하나의 테이블 블럭을 읽기 위해 하나의 I/O request를 발생시키고 Waiting하는 것은 소모적이므로 테이블 액세스를 잠시 미루고 인덱스로부터 몇 개의 row를 더 읽어 앞으로 Access하게 될 테이블 블럭 주소 목록을 미리 얻습니다. 그런 후에 병렬방식으로(또는 batch 방식이라고도 함) multiple single block read requests를 합니다. 결국 multiblock read를 하는 것인데, 우리가 일반적으로 알고 있는 db file multiblock read는 "contiguous" multiblock read인 반면 prefetch에서의 multiblock read는 "noncontiguous" multiblock read 인 것입니다. 한번의 Waiting으로 여러 블럭을 미리 퍼 올리므로 전반적으로 I/O관련 Wait이 감소하겠죠. 그리고 prefetch에 의한 multiblock read는 비동기(asynch I/O) 방식으로 이루어집니다.
    prefetch는 어차피 곧이어 액세스하게 될 블럭들을 미리 Buffer Cache에 Load 해 두는 기능이라고 말할 수 있습니다. 앞서 잠시 언급한 index prefetch도 마찬가지죠(브랜치 블럭에서 리프 블럭 주소 목록을 얻어 한번에 I/O Call). 또한 CKPT 프로세스가 prefetch된 block들을 모니터링하다가 실제 Buffer Block Access로 연결되지 못한 채 메모리에서 Flushing(또는 Age-out)되는 비율이 높다면 더이상 prefetch 기능이 작동되지 못하도록 Disable 시켜 버립니다.

    앞서 백면서생이라는 분이 언급한 그 문서를 저도 오래 전에 봤는데 거기서도 Disk I/O 관점에서 설명하고 있는 것을 알 수 있습니다.(Oracle delays data blocks reads until multiple rows specified by the underlying index are ready to be accessed and then retrieves multiple data blocks at once, rather than reading a single data block at a time. Block prefetching allows better utilization of the I/O capacity, and provides a further reduction in response time by issuing I/O operations in parallel whenever possible.)

    Jonathan Lewis가 [CBO Fundamentals]에서 "Table Prefetching"이라는 용어를 사용했다고 하셨는데, 제가 볼 때는 이 책에서도 새로운 NL Join 메커니즘을 잘못 설명하고 있다고 여겨집니다(번역서에서 마침 그 부분을 제가 번역했는데, 제가 알고 있는 내용과 달라 많은 고민을 했었죠.) 거기서는 Inner Table 액세스를 위해 사용되는 인덱스 로우만으로 조인을 완성하고 인덱스 rowid 순으로 정렬한 후 Inner 테이블을 액세스하기 때문에 Logical Read를 줄일 수 있다, 그래서 결과적으로 Physical I/O까지 감소하는 효과가 있다고 설명하고 있죠(AskTom에서도 같은 방식으로 설명한 것을 본 적이 있습니다). 만약 그게 사실이라면 NL 조인 시 부분범위 처리가 가능할까요? 부분범위 처리를 떠나서 NL 조인 결과가 Inner Table의 Rowid 순으로 정렬돼서 나오는 경우를 본 적이 없습니다. 다행히 Jonathan Lewis도 확실히 그렇다기 보다는 그렇게 이해하고 있다고 표현하고 있습니다. 이 책에서 뿐 아니라 Google에서 찾아지는 Jonathan Lewis의 다른 설명에서도 조금씩 다르게 설명하고 있는데, 거기서도 확실치는 않다고 말하고 있습니다.(that’s my assumption at present, thought not proved.)
    어차피 "table prefetch"도 정확한 용어라고 보기 어렵다면 "Advanced"라고 표현하는 것도 나쁘지 않다고 봅니다.

    마지막으로, 오라클이 공식적으로 설명한 적이 없기 때문에 Jonathan Lewis처럼 저 역시도 위 내용이 제가 연구하고 이해한 바를 설명한 것에 불과하다는 점을 밝힙니다.

  4. 욱짜 2008.10.02 12:43 신고 Modify/Delete Reply

    반갑습니다.
    Advanced Nested Loop Join의 명칭에 그런 History가 있었군요. 미리 알았더라면 좋았었겠습니다.

    용어가 상당히 혼란스런운 것은 사실인거 같습니다. Oracle이 공식적으로 용어를 지정해주면 좋았었겠다는 생각을 하곤 합니다. 아마 Jonathan Lewis의 설명에다가 Prefetch 과정이 Query Level이 아닌 Fetch Level이다라는 첨언을 붙이면 어느 정도 정확하지 않을까 합니다. Nested Loop Join을 통해 여러 번에 나누어 Fetch(Fetch Array Size에 따라)하는 경우 각 *Fetch Level*로 Prefetch 및 Buffer Pinning이 이루어지게 될겁니다. 따라서 NL Join의 부분 범위 처리도 가능하고, 모든 Row가 Rowid 순으로 정렬될 필요도 없을겁니다.

    상세한 답변 감사드리구요. 나중에 공동으로 세미나같은 걸 할 수 있으면 좋겠습니다. ^^

  5. 욱짜 2008.10.02 12:51 신고 Modify/Delete Reply

    그리고, Jonathan Lewis의 [CBO Fundamentals]에서 10gR2로 인해 변화된 내용과 일부 보완해야할 규칙같은 것들을 몇 개 발견했습니다. 나중에 기회가 되면 블로그나 세미나를 통해 공유하도록 하겠습니다.

  6. oraking 2008.10.02 15:15 신고 Modify/Delete Reply

    Jonathan Lewis의 설명도 맞고 Buffer Pinning에 의한 효과도 맞다고 설명하시는 것 같은데, 제 의견은 후자가 맞다입니다. 조동욱 선생께서 이 Article 본문에서 설명하신 그대로입니다. 서로 배치되는 두 가지 원리를 같이 놓고 설명하는 것은 좀 이해하기 힘들군요.
    1)
    Jonathan Lewis는 NL 조인할 때 Inner 테이블 액세스 단계에서 Block I/O가 적게 발생하는 원인을 설명하는 데 있어 테이블 액세스 이전에 일정량을 모아 Rowid순(결과적으로 Block Address 순)으로 정렬하기 때문이라고 설명하고 있습니다. 저도 "Jonathan Lewis의 이론이 맞다면"(또한 Tom 아저씨도 그렇게 설명하고 있으며, 두 사람 다 무시할 수 없는 guru임) 말씀하신 대로 Fetch Level로 정렬해서 액세스할 것이라고 추측하고 있었습니다. 그 방법 말고는 부분범위 처리를 설명할 방법이 없으니까요. 하지만, 테스트 해 보면 아시겠지만 전체 Row가 Rowid순으로 정렬되지 않는 것은 물론이거니와 하나의 Fetch Call 내에서도 결코 정렬되지 않습니다. 인덱스 엔트리가 가리키는 순서 그대로 출력되죠.
    2)
    반면, 여기 본문에서 조동욱 선생께서 밝히고 계신 원리는 Buffer Pinning에 의한 것이고, 이것은 저의 오랜 테스트 결과와 정확히 일치합니다. 저는 Stat 정보를 조회해 본 것에 그치지 않고 실제 Dump를 떠서 액세스하는 블럭주소를 따라 가 보는 실험을 여러 번 했었습니다. Jonathan Lewis의 설명대로라면 Inner Table 인덱스 블럭들을 일정량 스캔하기 전까지 테이블 블럭 액세스가 나타나지 않아야 하는데, Dump를 떠보면 인덱스블럭 액세스에 이어 곧바로 테이블 블럭 액세스가 나타납니다. 그런 후 인덱스 엔트리가 다른 테이블 블럭을 가리킬 때까지 인덱스 블럭 액세스만 계속 나타납니다. 앞서 읽은 테이블 블럭 주소를 계속 기억하고 있기 때문에 CR Request 없이 곧바로 Pointer에 의한 액세스를 하고 있다는 뜻이죠. Rowid순 정렬 없이도 CR이 적게 발생하는 이유가 충분히 설명됩니다. 그리고 Buffer Pinning을 유지하는 단위가 Fetch Call 단위인 것은 분명 맞습니다. 어차피 Block Address를 기억하는 저장소는 CGA(Call Global Area)일텐데, 이 공간은 하나의 Call 내에서만 유효한 데이터만을 저장하는 곳이니까요. 실제 테스트 결과에서도 그렇게 나타납니다.
    결론)
    어쨌든 두 방법 모두 "prefetch"라는 용어와는 그다지 어울리지 않습니다. 제 설명대로 I/O Call 시점에 블럭들을 미리 퍼 올린다는 개념이 더 근접하다고 믿고 있습니다. 제 의견을 한마디로 요약하면, "논리적인 Memory Buffer 액세스 단계에서는 'Buffer Pinning'이라는 기술을 사용하고, 물리적인 I/O Call 시점에는 'Prefetch'라는 기술을 사용한다"입니다.
    PS)
    저도 이런 기술적인 토론을 상당히 즐기는 편인데, 프로젝트에만 몰두하다 보니 최근 몇 년 동안 이런 활동에 소홀했던 것 같습니다. 앞으로 자주 방문하고 토론에도 참여하도록 하겠습니다. 가끔 반대 의견을 개진하더라도 화내지 않으시리라 믿습니다. ^^

    반가웠습니다.

  7. 욱짜 2008.10.02 16:45 신고 Modify/Delete Reply

    역시 용어의 문제네요. 평소에도 항상 정확한 용어를 사용해야 한다고 생각하고 있는데 이번에도 제가 엄밀함을 잃었습니다.

    일단 9i에서의 실행 계획상의 변화는 Table (Lookup) Prefetch 메커니즘에 의한게 맞습니다. Metalink 406966.1에 명확하게 설명이 되어 있습니다.

    문제는 제가 테스트한 결과에서 볼 수 있는 성능 개선이 Prefetch에 의한게 아니라는 것입니다. Buffer Pinning에 의한 것이죠? 이 부분을 지적하신걸로 이해됩니다.

    즉, 실행 계획의 변화는 Table Lookup Prefetch 메커니즘을 반영한 것이고(하지만 실제로 Prefetch가 이루어지는 가와는 무관), 9i에서의 개선된 Logcial Reads는 Buffer Pinning에 의한 것이다. 이렇게 정리하면 정확할거 같습니다.

    참고로, Prefetch라는 용어 자체가 Oracle 내에서 상당히 혼용되어 있어서 오해의 소지가 많은 거 같습니다. Fetch Array Size도 Prefetch Size라고 불리고, 기타 문서에서 보면 Prefetch라는 용어가 자주 등장합니다. 위의 글에서의 Prefetch는 Table Lookup Prefetch라고 정확하게 부르는 것이 좋을 거 같습니다.

  8. oraking 2008.10.03 20:50 신고 Modify/Delete Reply

    많이 부분이 명확해 진 것 같습니다.
    제가 첫 번째 답변에서 "오라클은 Buffer Pinning을 적용하는 지점을 점차 확대시켜 나가고 있고, NL 조인 메커니즘에서의 개선도 그 중 하나일 뿐"이라고 했던 부분과도 잘 일치하고, prefetch는 오히려 Disk I/O와 관련 있다고 지적한 부분도 언급하신 메타링크 문서를 통해 확인이 된 것 같습니다. 그리고 메타링크 문서를 읽어 보니 NL 실행계획의 변화를 오라클이 "Table (Lookup) Prefetch"라고 명명하고 있는 것도 사실이군요.
    따라서 마지막으로 정리하신 아래 명제가 틀림없어 보입니다.
    "실행 계획의 변화는 Table Lookup Prefetch 메커니즘을 반영한 것이고, 9i에서의 개선된 Logcial Reads는 Buffer Pinning에 의한 것이다."
    함께 토론하는 과정에서 저 역시도 아주 명쾌해졌습니다. 감사합니다.
    PS) 애초에 설명하려고 했던 Buffer Pinning에 의한 NL 조인 개선 효과에 대해서는 아직 Naming이 이루어지지 않았네요. "Advanced"? ㅋㅋ 농담입니다. 여기 방문하신 모든 분들께 공모해 보는 건 어떨까요? ㅎㅎ

  9. extremedb 2008.10.07 17:09 신고 Modify/Delete Reply

    음 제가 낄틈이 없이 두분이서 결론을 내셨네요.
    테스트 하실때 buffer_cache 를 flush 한후에 테스트 해보시기 바랍니다.
    왜냐하면 physical read를 할때만 prefetch 의 효과를 누릴수 있기 때문입니다.
    아래는 테스트 결과 입니다.

    NAME VALUE
    ---------------------------------------- ----------
    physical reads cache prefetch 17 --> prefetch
    physical read total multi block requests 3 --> multi block IO 발생

  10. 욱짜 2008.10.07 17:32 신고 Modify/Delete Reply

    좋은 정보 감사합니다.

  11. 시연아카데미 2008.10.08 01:38 신고 Modify/Delete Reply

    국내에서도 이런 심도깊은 토론의 장이 열릴수 있다니... 아주 들뜬 마음으로 재미있게 글/답글을 읽었습니다. 동욱차장이 온라인으로 고수들을 하나둘 끌어모으시는군요. 좋은 정보들 감사합니다~!

  12. KT 2008.12.26 19:10 신고 Modify/Delete Reply

    "Direct path Reference Notes"
    ...
    Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache). If asynchronous IO is supported (and in use) then Oracle can submit IO requests and continue processing. It can then pick up the results of the IO request later and will wait on "direct path read" until the required IO completes.

    ....

    This style of read request is typically used for:

    - Sort IO (when a sort does not fit in memory)
    - Parallel Query slaves
    - Readahead (where a process may issue an IO request for a block it expects to need in the near future)

    여기서,
    "Readahead"가 "Table Lookup Prefetch" 과정을 얘기하는 걸까요?

  13. 욱짜 2008.12.26 20:37 신고 Modify/Delete Reply

    Metalink 문서 406966.1에 의하면 Table Lookup Prefetch가 Disk I/O를 유발하는 경우에는 db file parallel read 이벤트를 대기하게 됩니다.

  14. ktlee67@show.co.kr 2010.01.15 11:37 신고 Modify/Delete Reply

    좋은 글 잘 읽었습니다. 결론적으로 "실행계획의 변화는 Table (lookup) prefetching에 의한 것이고, logical read의 개선은 buffer pinning에 의한 것이다."로 귀결된 것 같습니다.
    헌데, 그렇다면 prefetching의 히든파라미터 "_table_lookup_prefetch_size"와 "_table_lookup_prefetch_thres"의 수정으로 이전 8i와 같은 plan으로 동작하게 한다면, buffer pinning 효과는 살아있어야 하는 것 아닐까요? table prefetch를 diable한다면, buffer pinning 효과도 사라지는 건가요? 위의 본문내용중에서 10g에서 파라미터 변경으로 8i plan으로 바뀐 경우 블록을 많이 읽는 것으로 나오는데(물론 실제 8.1.7 버전에서 테스트한 것 보다 적게 나오는 것으로 보이지만) 이 부분이 명확치 않습니다. 수고하세요 ^^

  15. makeityourrings 2011.11.17 17:33 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

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 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

티스토리 툴바