by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

3.6 Fast Block Cleanout vs. Delayed Block Cleanout




Fast Block Cleanout 동작 방식 테스트

  • 블록 클린아웃 : 트랜잭션이 종료한 시점에 트랜잭션과 관련되었던 데이터 블록들의 변경 내용을 정리하는 작업.
  • fast block cleanout : 빠르게 블록을 클린아웃 하는 방식
  • delayed block cleanout : 지연된 블록 클린아웃 방식

테스트 초기화

SQL> create table undo_layer_t6 (c1 number, c2 varchar2(10), c3 char(20));
SQL> insert into undo_layer_t6 values(1, 'A', 'a');
SQL> insert into undo_layer_t6 values(2, 'B', 'b');
SQL> insert into undo_layer_t6 values(3, 'C', 'c');
SQL> commit;

Fast Block Cleanout - 1단계 : 커밋 전 데이터 블록 덤프

kjwon:ora10g:KJWON >
  l  select rowid, substr(rowid, 1, 15), c1 from undo_layer_t6;

ROWID              SUBSTR(ROWID,1,15)                     C1
------------------ ------------------------------ ----------
AAAM1MAAGAAAAAVAAA AAAM1MAAGAAAAAV                         1
AAAM1MAAGAAAAAVAAB AAAM1MAAGAAAAAV                         2
AAAM1MAAGAAAAAVAAC AAAM1MAAGAAAAAV                         3

kjwon:ora10g:KJWON >
  l  @rowid2fb
 INPUT ROWID  : AAAM1MAAGAAAAAVAAA

    OBJECT    FILE_NO   BLOCK_NO ROW_NUMBER
---------- ---------- ---------- ----------
     52556          6         21          0
     
kjwon:ora10g:KJWON >
  l  delete from undo_layer_t6 where c1=1;
  
kjwon:ora10g:KJWON >
  l  alter system dump datafile 6 block 21;  
Start dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
buffer tsn: 7 rdba: 0x01800015 (6/21)
scn: 0x0000.000adaa9 seq: 0x01 flg: 0x00 tail: 0xdaa90601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800015
Object id on Block? Y
seg/obj: 0xcd4c csc: 0x00.ada00 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.028.00000146 0x00800058.0144.35 C--- 0 scn 0x0000.000ad2a0
0x02 0x000a.019.00000154 0x00800f2f.00ea.2f ---- 1 fsc 0x001b.00000000
data_block_dump,data header at 0x80e8464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x080e8464
bdba: 0x01800015
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f24
avsp=0x1f29
tosp=0x1f46
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f24
0x14:pri[1] offs=0x1f5e
0x16:pri[2] offs=0x1f41
block_row_dump:
tab 0, row 0, @0x1f24
tl: 2 fb: -HDFL- lb: 0x2
tab 0, row 1, @0x1f5e
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 03
col 1: [ 1] 42
col 2: [20] 62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 2, @0x1f41
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 04
col 1: [ 1] 43
col 2: [20] 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21

Fast Block Cleanout - 2단계 : 커밋 후 데이터 블록 덤프

kjwon:ora10g:KJWON >
  l  commit;
  
kjwon:ora10g:KJWON >
  l  alter system dump datafile 6 block 21;  
Start dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
buffer tsn: 7 rdba: 0x01800015 (6/21)
scn: 0x0000.000adb50 seq: 0x01 flg: 0x02 tail: 0xdb500601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800015
Object id on Block? Y
seg/obj: 0xcd4c csc: 0x00.ada00 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.028.00000146 0x00800058.0144.35 C--- 0 scn 0x0000.000ad2a0
0x02 0x000a.019.00000154 0x00800f2f.00ea.2f -U 1 fsc 0x001b.000adb50
data_block_dump,data header at 0x80e8464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x080e8464
bdba: 0x01800015
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f24
avsp=0x1f29
tosp=0x1f46
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f24
0x14:pri[1] offs=0x1f5e
0x16:pri[2] offs=0x1f41
block_row_dump:
tab 0, row 0, @0x1f24
tl: 2 fb: -HDFL- lb: 0x2
tab 0, row 1, @0x1f5e
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 03
col 1: [ 1] 42
col 2: [20] 62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 2, @0x1f41
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 04
col 1: [ 1] 43
col 2: [20] 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21

Fast Block Cleanout - 3단계 : READ 후 데이터 블록 덤프

kjwon:ora10g:KJWON >
  l  select count(*) from undo_layer_t6;

  COUNT(*)
----------
         2

kjwon:ora10g:KJWON >
  l  alter system dump datafile 6 block 21;         
Start dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
buffer tsn: 7 rdba: 0x01800015 (6/21)
scn: 0x0000.000adb50 seq: 0x01 flg: 0x02 tail: 0xdb500601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800015
Object id on Block? Y
seg/obj: 0xcd4c csc: 0x00.ada00 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.028.00000146 0x00800058.0144.35 C--- 0 scn 0x0000.000ad2a0
0x02 0x000a.019.00000154 0x00800f2f.00ea.2f -U 1 fsc 0x001b.000adb50
data_block_dump,data header at 0x80e8464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x080e8464
bdba: 0x01800015
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f24
avsp=0x1f29
tosp=0x1f46
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f24
0x14:pri[1] offs=0x1f5e
0x16:pri[2] offs=0x1f41
block_row_dump:
tab 0, row 0, @0x1f24
tl: 2 fb: -HDFL- lb: 0x2
tab 0, row 1, @0x1f5e
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 03
col 1: [ 1] 42
col 2: [20] 62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 2, @0x1f41
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 04
col 1: [ 1] 43
col 2: [20] 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21

Fast Block Cleanout - 4단계 : DML 후 데이터 블록 덤프

kjwon:ora10g:KJWON >
  l  delete from undo_layer_t6 where c1=2;
  
kjwon:ora10g:KJWON >
  l  alter system dump datafile 6 block 21;  
Start dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
buffer tsn: 7 rdba: 0x01800015 (6/21)
scn: 0x0000.000adb50 seq: 0x01 flg: 0x02 tail: 0xdb500601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800015
Object id on Block? Y
seg/obj: 0xcd4c csc: 0x00.ada00 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.005.00000149 0x008000cb.0117.06 ---- 1 fsc 0x001b.00000000
0x02 0x000a.019.00000154 0x00800f2f.00ea.2f -U 1 fsc 0x001b.000adb50
data_block_dump,data header at 0x80e8464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x080e8464
bdba: 0x01800015
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f24
avsp=0x1f29
tosp=0x1f63
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f24
0x14:pri[1] offs=0x1f5e
0x16:pri[2] offs=0x1f41
block_row_dump:
tab 0, row 0, @0x1f24
tl: 2 fb: -HDFL- lb: 0x2
tab 0, row 1, @0x1f5e
tl: 2 fb: -HDFL- lb: 0x1
tab 0, row 2, @0x1f41
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 04
col 1: [ 1] 43
col 2: [20] 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21

Fast Block Cleanout - 5단계 : 커밋 후 데이터 블록 덤프

kjwon:ora10g:KJWON >
  l  commit;
  
kjwon:ora10g:KJWON >
  l  alter system dump datafile 6 block 21;  
Start dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
buffer tsn: 7 rdba: 0x01800015 (6/21)
scn: 0x0000.000adc0c seq: 0x02 flg: 0x02 tail: 0xdc0c0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800015
Object id on Block? Y
seg/obj: 0xcd4c csc: 0x00.ada00 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.005.00000149 0x008000cb.0117.06 -U 1 fsc 0x001b.000adc0c
0x02 0x000a.019.00000154 0x00800f2f.00ea.2f -U 1 fsc 0x001b.000adb50
data_block_dump,data header at 0x80e8464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x080e8464
bdba: 0x01800015
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f24
avsp=0x1f29
tosp=0x1f63
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f24
0x14:pri[1] offs=0x1f5e
0x16:pri[2] offs=0x1f41
block_row_dump:
tab 0, row 0, @0x1f24
tl: 2 fb: -HDFL- lb: 0x2
tab 0, row 1, @0x1f5e
tl: 2 fb: -HDFL- lb: 0x1
tab 0, row 2, @0x1f41
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 04
col 1: [ 1] 43
col 2: [20] 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21

Fast Block Cleanout - 6단계 : DML 후 데이터 블록 덤프

kjwon:ora10g:KJWON >
  l  delete from undo_layer_t6 where c1=3;
  
kjwon:ora10g:KJWON >
  l  alter system dump datafile 6 block 21;  
Start dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
buffer tsn: 7 rdba: 0x01800015 (6/21)
scn: 0x0000.000adc3b seq: 0x01 flg: 0x00 tail: 0xdc3b0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800015
Object id on Block? Y
seg/obj: 0xcd4c csc: 0x00.adc3b itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.005.00000149 0x008000cb.0117.06 C--- 0 scn 0x0000.000adc0c
0x02 0x000a.022.00000154 0x00800f30.00ea.07 ---- 1 fsc 0x001b.00000000
data_block_dump,data header at 0x80e8464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x080e8464
bdba: 0x01800015
76543210
flag=--------
ntab=1
nrow=3
frre=0
fsbo=0x18
fseo=0x1f24
avsp=0x1f63
tosp=0x1f80
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] sfll=1
0x14:pri[1] sfll=-1
0x16:pri[2] offs=0x1f41
block_row_dump:
tab 0, row 2, @0x1f41
tl: 2 fb: -HDFL- lb: 0x2
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21

결과 분석

  • 커밋 시에 fast block cleanout 방식이 적용되는 블록은 커밋과 관련된 최소한의 내용만을 변경
  • fast block cleanout 방식이 적용된 블록에 대해서는 읽기 작업이나 해당 블록에 DML이 발생하더라도 full cleanout이 발생하지 않는다.
  • 해당 블록은 itl을 재사용하는 시점에 full cleanout이 발생.
  • 오라클 9i까지는 fast block cleanout 방식이 적용된 블록에 DML이 발생할 경우, itl의 재사용 여부와 상관없이 full cleanout 발생.

Delayed Block Cleanout 동작 방식 테스트

테스트 초기화

SQL> create table undo_layer_t7(c1 number, c2 char(2000));
SQL> create sequence t7_sq;
SQL> begin
                for i in 1..1000 loop
                    insert into undo_layer_t7 values(t7_sq.nextval, 'c2');
                end loop;
                commit;
          end;
          /

Delayed Block Cleanout - 1단계 : 커밋 전 데이터 블록 덤프

kjwon:ora10g:KJWON >
  l  select rowid, substr(rowid, 1, 15), c1 from undo_layer_t7 where c1=1000;

ROWID              SUBSTR(ROWID,1,15)                     C1
------------------ ------------------------------ ----------
AAAM1NAAGAAAAIGAAA AAAM1NAAGAAAAIG                      1000

kjwon:ora10g:KJWON >
  l  @rowid2fb
 INPUT ROWID  : AAAM1NAAGAAAAIGAAA

    OBJECT    FILE_NO   BLOCK_NO ROW_NUMBER
---------- ---------- ---------- ----------
     52557          6        518          0
     
kjwon:ora10g:KJWON >
  l  delete from undo_layer_t7;
  
kjwon:ora10g:KJWON >
  l  alter system dump datafile 6 block 518;
Start dump data blocks tsn: 7 file#: 6 minblk 518 maxblk 518
buffer tsn: 7 rdba: 0x01800206 (6/518)
scn: 0x0000.000ae9d2 seq: 0x01 flg: 0x00 tail: 0xe9d20601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800206
Object id on Block? Y
seg/obj: 0xcd4d csc: 0x00.ae9d2 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x180018a ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.000.00000144 0x0080006a.0111.02 C--- 0 scn 0x0000.000ae6f7
0x02 0x0006.011.0000014f 0x0080045c.012c.02 ---- 1 fsc 0x07d7.00000000
data_block_dump,data header at 0x80e8464
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x080e8464
bdba: 0x01800206
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x17bf
avsp=0x17ab
tosp=0x1f84
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x17bf
block_row_dump:
tab 0, row 0, @0x17bf
tl: 2 fb: -HDFL- lb: 0x2
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 518 maxblk 518

Delayed Block Cleanout - 2단계 : Buffer Flush, Commit 후 데이터 블록 덤프

kjwon:ora10g:KJWON >
  l  alter system flush buffer_cache;
  
kjwon:ora10g:KJWON >
  l  commit;
  
kjwon:ora10g:KJWON >
  l  alter system dump datafile 6 block 518;  
Start dump data blocks tsn: 7 file#: 6 minblk 518 maxblk 518
buffer tsn: 7 rdba: 0x01800206 (6/518)
scn: 0x0000.000ae9d2 seq: 0x01 flg: 0x04 tail: 0xe9d20601
frmt: 0x02 chkval: 0x0343 type: 0x06=trans data
Block header dump: 0x01800206
Object id on Block? Y
seg/obj: 0xcd4d csc: 0x00.ae9d2 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x180018a ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.000.00000144 0x0080006a.0111.02 C--- 0 scn 0x0000.000ae6f7
0x02 0x0006.011.0000014f 0x0080045c.012c.02 ---- 1 fsc 0x07d7.00000000
data_block_dump,data header at 0x80e8464
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x080e8464
bdba: 0x01800206
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x17bf
avsp=0x17ab
tosp=0x1f84
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x17bf
block_row_dump:
tab 0, row 0, @0x17bf
tl: 2 fb: -HDFL- lb: 0x2
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 518 maxblk 518

Delayed Block Cleanout - 3단계 : READ 후 데이터 블록 덤프

kjwon:ora10g:KJWON >
  l  select * from undo_layer_t7;
  
kjwon:ora10g:KJWON >
  l  alter system dump datafile 6 block 518;
Start dump data blocks tsn: 7 file#: 6 minblk 518 maxblk 518
buffer tsn: 7 rdba: 0x01800206 (6/518)
scn: 0x0000.000aeab5 seq: 0x01 flg: 0x00 tail: 0xeab50601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800206
Object id on Block? Y
seg/obj: 0xcd4d csc: 0x00.aeab5 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x180018a ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.000.00000144 0x0080006a.0111.02 C--- 0 scn 0x0000.000ae6f7
0x02 0x0006.011.0000014f 0x0080045c.012c.02 C--- 0 scn 0x0000.000aea95
data_block_dump,data header at 0x80e8464
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x080e8464
bdba: 0x01800206
76543210
flag=--------
ntab=1
nrow=1
frre=0
fsbo=0x14
fseo=0x17bf
avsp=0x1f84
tosp=0x1f84
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] sfll=-1
block_row_dump:
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 518 maxblk 518

결과 분석

  • 커밋 시에 delayed block cleanout 방식이 적용되는 블록은 아무런 변경사항이 발생하지 않는다.
  • 해당 블록은 버퍼 캐시로 적재되는 시점에 full cleanout이 발생하게 된다.
항목 Fast block cleanout Delayed block cleanout
대상 블록 버퍼캐시에 존재하는 블록 버퍼개시에 존재하지 않는 블록
Partial Cleanout 여부 가능 불가능
Partial Cleanout 시에
변경되는 내용
블록 Scn#
Itl의 Flag 변경(-U)
Itl의 Scn/Fsc의 Scn Base#
N/A
Full Cleanout 이 발생하는 시점 해당 블록에 트랜잭션이 수행되는 시점 해당 블록을 디스크에서 버퍼캐시로 적재하는 시점
Full Cleanout시에
변경되는 내용
Itl의 Flag 변경(C---)
Itl의 Lck 초기화(0)
Itl의 Scn/Fsc의 Scn Wrap#
Row 레코드 정리
블록 Scn#
Itl의 Flag 변경(C---)
Itl의 Lck 초기화(0)
Itl의 Scn/Fsc의 Scm Base#/Wrap#
Row 레코드 정리

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.