oracle deep internal 3 (ver.2)

Ver.2 (2016.07.19)

© Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved.

Scenario

Buffer Cache

XCURRENTcol 1: [ 1] 41 (A)

BH (0x657d8238)

BA

C

D

EF

G

H

I

Update; Commit

Update

; Com

mit

BCDEFGHI

When update a value ‘A’ with values ‘B’ to ‘I’ consecutively,

How are CU blocks and CR blocks allocated inside the Buffer Cache?


Expected outcome

Buffer Cache

CURRENT BLOCK(XCUR)

Update

S1

Consistent ReadBlock(CR 1)&

UNDO

Update

S2


UNDO

Update

S3


UNDO

Update

S4


UNDO

Update

S5


UNDO

Update

S6

© Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved.2013, Oracle 10g Performance: chapter 06 buffer cache 25,26 page, Kyle Hailey

Cited from http://www.slideshare.net/khailey/oracle-10g-performance-chapter-06-buffer-cache

It's so difficult to understand only through the book.I'd like to check internal actual situation of oracle

with my own eyes

“Max length in Cache Buffer”mechanism as explained in Kyle Hailey's presentation

1. UPDATE T1 SET C2= 'B' ; COMMIT ;

2. UPDATE T1 SET C2= 'C' ; COMMIT ;

3. UPDATE T1 SET C2= 'D' ; COMMIT ;

. . . . . .

9. UPDATE T1 SET C2= 'G' ; COMMIT ;

. . . . . . ?


Inside of Oracle via ODI Analyzer When _db_block_max_cr_dba = 6

Buffer Cache

CRcol 1: [ 1] 41

(A)

BH (0x657d8238)

CRcol 1: [ 1] 42

(B)

BH (0x657d8100)

CRcol 1: [ 1] 43

(C)

BH (0x653fab88)

CRcol 1: [ 1] 44

(D)

BH (0x653fa7e0)

CRcol 1: [ 1] 45 (E)

BH (0x653fa438)

CRcol 1: [ 1] 46 (F)

BH (0x653fa090)

New CU block

New CU block

New CU Block

XCURRENT

XCURRENTXCURRENT

XCURRENT

XCURRENT

New CU Block

XCURRENTcol 1: [ 1] 49

(I)

XCURRENT

block1

block2

block3

block4

block5

block6

block1

block2

block3

New CU block


(G)

CRcol 1: [ 1] 47

(G)


(H)

CRcol 1: [ 1] 48

(H)

CR Block Reuse

CR Block Reuse

CR Block Reuse

Oracle init parameterWhen _db_block_max_cr_dba = 6

Buffer Cache


Undo Segment

Data Block

1. UPDATE T1 SET C2= ' B ' ; COMMIT ;

Rowsblock_row_dump:tab 0, row 0, @0x1f70tl: 16 fb: --H-FL-- lb: 0x0 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41 “A”

BH (0x657d8100) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x65438000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0027.000.000000c4 0x00000000.0000.00 C--- 0 scn 0x0000.0021a1100x02 0x0025.000.00000138 0x018000a2.009b.01 ---- 1 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 42

BH (0x657d8238) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x6543a000st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0027.000.000000c4 0x00000000.0000.00 C--- 0 scn 0x0000.0021a1100x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x0 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41

1

BH (0x657d7e90) file#: 6 rdba: 0x018000a2 (6/162) class: 90 ba: 0x65434000xid: 0x0025.000.00000138 seq: 0x9b* Rec #0x1 slt: 0x00 objn: 69880(0x000110f8)* Layer: 11 (Row) opc: 1 rci 0x00col 1: [ 1] 41

2

“A”

“B”

“A”

UPDATE T1

SET c2 = ‘B’

Buffer Cache


Undo Segment

Data Block

2. UPDATE SYSTEM.T1 SET C2= ' C ' ; COMMIT ;



1


“A”

BH (0x657d8100) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x65438000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0027.000.000000c4 0x00000000.0000.00 C--- 0 scn 0x0000.0021a1100x02 0x0025.000.00000138 0x018000a2.009b.01 ---- 1 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 42

2

“B”

“A”

UPDATE T1

SET c2 = ‘C’

BH (0x653fa918) file#: 6 rdba: 0x01800092 (6/146) class: 86 ba: 0x653c2000xid: 0x0023.000.00000132 seq: 0xa2 * Rec #0x1 slt: 0x00 objn: 69880(0x000110f8) * Layer: 11 (Row) opc: 1 rci 0x00col 1: [ 1] 42

“B”

st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1BH (0x653fab88) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653c6000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0027.000.000000c4 0x00000000.0000.00 C--- 0 scn 0x0000.0021a1100x02 0x0025.000.00000138 0x018000a2.009b.01 C--- 0 scn 0x0000.0021a11e0x03 0x0023.000.00000132 0x01800092.00a2.01 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x3 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 43

3

“C”

Buffer Cache


Undo Segment

Data Block

3. UPDATE SYSTEM.T1 SET C2= ' D ' ; COMMIT ;



1


“A”

“A”


“B”

BH (0x657d8100) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x65438000st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0027.000.000000c4 0x00000000.0000.00 C--- 0 scn 0x0000.0021a1100x02 0x0025.000.00000138 0x018000a2.009b.01 ---- 1 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 42

2

“B”

BH (0x653fa570) file#: 6 rdba: 0x01800082 (6/130) class: 18 ba: 0x653bc000xid: 0x0001.000.000009ef seq: 0x352 * Rec #0x1 slt: 0x00 objn: 69880(0x000110f8)* Layer: 11 (Row) opc: 1 rci 0x00col 1: [ 1] 43

“C”

BH (0x653fab88) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653c6000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0027.000.000000c4 0x00000000.0000.00 C--- 0 scn 0x0000.0021a1100x02 0x0025.000.00000138 0x018000a2.009b.01 C--- 0 scn 0x0000.0021a11e0x03 0x0023.000.00000132 0x01800092.00a2.01 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x3 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 43

3

“C”

st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1BH (0x653fa7e0) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653c0000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.000.000009ef 0x01800082.0352.01 ---- 1 fsc 0x0000.000000000x02 0x0025.000.00000138 0x018000a2.009b.01 C--- 0 scn 0x0000.0021a11e0x03 0x0023.000.00000132 0x01800092.00a2.01 C--- 0 scn 0x0000.0021a121tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 44

4

“D”

UPDATE T1

SET c2 = ‘D’

Buffer Cache


Undo Segment

Data Block

4. UPDATE SYSTEM.T1 SET C2= ' E ' ; COMMIT ;



1


“A”

“A”


“B”


2

“B”


“C”

BH (0x653fab88) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653c6000st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0027.000.000000c4 0x00000000.0000.00 C--- 0 scn 0x0000.0021a1100x02 0x0025.000.00000138 0x018000a2.009b.01 C--- 0 scn 0x0000.0021a11e0x03 0x0023.000.00000132 0x01800092.00a2.01 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x3 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 43

3

“C”

BH (0x653fa1c8) file#: 6 rdba: 0x01800112 (6/274) class: 104 ba: 0x653b6000xid: 0x002c.001.000000c4 seq: 0x69* Rec #0x11 slt: 0x01 objn: 69880(0x000110f8)* Layer: 11 (Row) opc: 1 rci 0x00col 1: [ 1] 44

“D”

BH (0x653fa7e0) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653c0000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.000.000009ef 0x01800082.0352.01 ---- 1 fsc 0x0000.000000000x02 0x0025.000.00000138 0x018000a2.009b.01 C--- 0 scn 0x0000.0021a11e0x03 0x0023.000.00000132 0x01800092.00a2.01 C--- 0 scn 0x0000.0021a121tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 44

4

“D”

st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1

BH (0x653fa438) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653ba000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.000.000009ef 0x01800082.0352.01 C--- 0 scn 0x0000.0021a1240x02 0x002c.001.000000c4 0x01800112.0069.11 ---- 1 fsc 0x0000.000000000x03 0x0023.000.00000132 0x01800092.00a2.01 C--- 0 scn 0x0000.0021a121tl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 45

5

“E”

UPDATE T1

SET c2 = ‘E’

Buffer Cache


Undo Segment

Data Block

5. UPDATE SYSTEM.T1 SET C2= ' F ' ; COMMIT ;



1


“A”

“A”


“B”


2

“B”


“C”


3

“C”


“D”

BH (0x653f9e20) file#: 6 rdba: 0x01800102 (6/258) class: 102 ba: 0x653b0000xid: 0x002b.001.000000c6 seq: 0x62 * Rec #0x2 slt: 0x01 objn: 69880(0x000110f8) * Layer: 11 (Row) opc: 1 rci 0x00col 1: [ 1] 45

“E”

BH (0x653fa7e0) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653c0000st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.000.000009ef 0x01800082.0352.01 ---- 1 fsc 0x0000.000000000x02 0x0025.000.00000138 0x018000a2.009b.01 C--- 0 scn 0x0000.0021a11e0x03 0x0023.000.00000132 0x01800092.00a2.01 C--- 0 scn 0x0000.0021a121tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 44

4

“D”

BH (0x653fa438) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653ba000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.000.000009ef 0x01800082.0352.01 C--- 0 scn 0x0000.0021a1240x02 0x002c.001.000000c4 0x01800112.0069.11 --U- 1 fsc 0x0000.0021a1270x03 0x0023.000.00000132 0x01800092.00a2.01 C--- 0 scn 0x0000.0021a121tl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 45

5

“E”


BH (0x653fa090) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653b4000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.000.000009ef 0x01800082.0352.01 C--- 0 scn 0x0000.0021a1240x02 0x002c.001.000000c4 0x01800112.0069.11 C--- 0 scn 0x0000.0021a1270x03 0x002b.001.000000c6 0x01800102.0062.02 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x3 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 46

6

“F”

UPDATE T1

SET c2 = ‘F’

Buffer Cache


Undo Segment

Data Block


1

“A”

6. UPDATE SYSTEM.T1 SET C2= ' G ' ; COMMIT ; Reuse BH ‘8283’



BH (0x657d8238) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x6543a000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x002a.001.000000c6 0x018000f2.0062.03 --U- 1 fsc 0x0000.0021a12d0x02 0x002c.001.000000c4 0x01800112.0069.11 C--- 0 scn 0x0000.0021a1270x03 0x002b.001.000000c6 0x01800102.0062.02 C--- 0 scn 0x0000.0021a12atl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 47

1

“G”

“A”


“B”


2

“B”


“C”


3

“C”


“D”


“E”


4

“D”

BH (0x653f9bb0) file#: 6 rdba: 0x018000f2 (6/242) class: 100 ba: 0x653ac000xid: 0x002a.001.000000c6 seq: 0x62 * Rec #0x3 slt: 0x01 objn: 69880(0x000110f8)* Layer: 11 (Row) opc: 1 rci 0x00col 1: [ 1] 46

“F”BH (0x653fa438) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653ba000st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.000.000009ef 0x01800082.0352.01 C--- 0 scn 0x0000.0021a1240x02 0x002c.001.000000c4 0x01800112.0069.11 --U- 1 fsc 0x0000.0021a1270x03 0x0023.000.00000132 0x01800092.00a2.01 C--- 0 scn 0x0000.0021a121tl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 45

5

“E”

BH (0x653fa090) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653b4000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.000.000009ef 0x01800082.0352.01 C--- 0 scn 0x0000.0021a1240x02 0x002c.001.000000c4 0x01800112.0069.11 C--- 0 scn 0x0000.0021a1270x03 0x002b.001.000000c6 0x01800102.0062.02 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x3 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 46

6

“F”


UPDATE T1

SET c2 = ‘G’

Buffer Cache


Undo Segment

Data Block


1

“A”


2

“B”

7. UPDATE SYSTEM.T1 SET C2= ' H ' ; COMMIT ; Reuse BH ‘8100’



“A”


“B”

BH (0x657d8100) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x65438000sst: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x002a.001.000000c6 0x018000f2.0062.03 C--- 0 scn 0x0000.0021a12d0x02 0x0029.001.00000107 0x018000e2.00bc.04 --U- 1 fsc 0x0000.0021a1300x03 0x002b.001.000000c6 0x01800102.0062.02 C--- 0 scn 0x0000.0021a12atl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 48

2

“H”


“C”


3

“C”


“D”


“E”


4

“D”



5

“E”

BH (0x653fa090) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653b4000st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.000.000009ef 0x01800082.0352.01 C--- 0 scn 0x0000.0021a1240x02 0x002c.001.000000c4 0x01800112.0069.11 C--- 0 scn 0x0000.0021a1270x03 0x002b.001.000000c6 0x01800102.0062.02 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x3 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 46

6

“F”

BH (0x653f9940) file#: 6 rdba: 0x018000e2 (6/226) class: 98 ba: 0x653a8000xid: 0x0029.001.00000107 seq: 0xbc* Rec #0x4 slt: 0x01 objn: 69880(0x000110f8) * Layer: 11 (Row) opc: 1 rci 0x00col 1: [ 1] 47

“G”

BH (0x657d8238) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x6543a000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x002a.001.000000c6 0x018000f2.0062.03 --U- 1 fsc 0x0000.0021a12d0x02 0x002c.001.000000c4 0x01800112.0069.11 C--- 0 scn 0x0000.0021a1270x03 0x002b.001.000000c6 0x01800102.0062.02 C--- 0 scn 0x0000.0021a12atl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 47

1

“G”


UPDATE T1

SET c2 = ‘H’

Buffer Cache


Undo Segment

Data Block


1

“A”


2

“B”


3

“C”

8. UPDATE SYSTEM.T1 SET C2= ' I ' ; COMMIT ; Reuse BH‘ab88’



“A”


“B”


“C”

BH (0x653fab88) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653c6000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x002a.001.000000c6 0x018000f2.0062.03 C--- 0 scn 0x0000.0021a12d0x02 0x0029.001.00000107 0x018000e2.00bc.04 C--- 0 scn 0x0000.0021a1300x03 0x0028.004.000000db 0x018000d3.00b2.08 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x3 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 49

3

“I”


“D”


“E”


4

“D”



5

“E”

BH (0x653fa090) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x653b4000st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.000.000009ef 0x01800082.0352.01 C--- 0 scn 0x0000.0021a1240x02 0x002c.001.000000c4 0x01800112.0069.11 C--- 0 scn 0x0000.0021a1270x03 0x002b.001.000000c6 0x01800102.0062.02 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x3 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 46

6

“F”

BH (0x653f9940) file#: 6 rdba: 0x018000e2 (6/226) class: 98 ba: 0x653a8000xid: 0x0029.001.00000107 seq: 0xbc* Rec #0x4 slt: 0x01 objn: 69880(0x000110f8) * Layer: 11 (Row) opc: 1 rci 0x00col 1: [ 1] 47

“G”

BH (0x657d8238) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x6543a000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc0x01 0x002a.001.000000c6 0x018000f2.0062.03 --U- 1 fsc 0x0000.0021a12d0x02 0x002c.001.000000c4 0x01800112.0069.11 C--- 0 scn 0x0000.0021a1270x03 0x002b.001.000000c6 0x01800102.0062.02 C--- 0 scn 0x0000.0021a12atl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 47

1

“G”

BH (0x653f96d0) file#: 6 rdba: 0x018000d3 (6/211) class: 96 ba: 0x653a4000xid: 0x0028.004.000000db seq: 0xb2* Rec #0x8 slt: 0x04 objn: 69880(0x000110f8)* Layer: 11 (Row) opc: 1 rci 0x00col 1: [ 1] 48

“H”

BH (0x657d8100) file#: 5 rdba: 0x0140dcab (5/56491) class: 1 ba: 0x65438000sst: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x002a.001.000000c6 0x018000f2.0062.03 C--- 0 scn 0x0000.0021a12d0x02 0x0029.001.00000107 0x018000e2.00bc.04 --U- 1 fsc 0x0000.0021a1300x03 0x002b.001.000000c6 0x01800102.0062.02 C--- 0 scn 0x0000.0021a12atl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 48

2

“H”


UPDATE T1

SET c2 = ‘I’


Inside of Oracle via ODI tool When _db_block_max_cr_dba = 6

Buffer Cache

CRcol 1: [ 1] 41

(A)

BH (0x657d8238)

CRcol 1: [ 1] 42

(B)

BH (0x657d8100)

CRcol 1: [ 1] 43

(C)

BH (0x653fab88)

CRcol 1: [ 1] 44

(D)

BH (0x653fa7e0)

CRcol 1: [ 1] 45 (E)

BH (0x653fa438)

CRcol 1: [ 1] 46 (F)

BH (0x653fa090)

New CU block

New CU block

New CU Block

XCURRENT

XCURRENTXCURRENT

XCURRENT

XCURRENT

New CU Block


(I)

XCURRENT

CR Block Reuse

CR Block Reuse

block1

block2

block3

block4

block5

block6

block1

block2

block3

New CU block

CR Block Reuse


(G)

CRcol 1: [ 1] 47

(G)


(H)

CRcol 1: [ 1] 48

(H)

When _db_block_max_cr_dba equals 6,a maximum of 6 data blocks are allocated and rotate among them for updates.

Oracle Deep Internal

Blog

Video

E-mail

NAVER http://cafe.naver.com/playexem

ITPUB http://blog.itpub.net/31135309/

Wordpress https://playexem.wordpress.com/

Slideshare http://www.slideshare.net/playexem

Youtube https://www.youtube.com/channel/UC5wKR_-A0eL_Pn_EMzoauJg

Research & Contents Team Sook jin, [email protected]

For more information, or to schedule an on-site education, contact via blog or e-mail

http://cafe.naver.com/playexem

http://blog.itpub.net/31135309/

https://playexem.wordpress.com/

http://www.slideshare.net/playexem

https://www.youtube.com/channel/UC5wKR_-A0eL_Pn_EMzoauJg

mailto:[email protected]

oracle deep internal 3 (ver.2)

Technology