【中文】 odi no.004 analysis of oracle performance degradation caused by inefficient block...

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

Research & Contents Team

Analysis of Oracle Performance

Degradation caused by

Inefficient block cleanout

Oracle Deep Internal report no. 004 (2016.05.20)


Ⅰ. Transaction slot Before-image Chaining

Ⅰ-1. Questions

Ⅰ-2. Architecture of transaction slot before-image chaining

Ⅰ-3. Flow chart of delayed block cleanout

Ⅱ. Block cleanout Analysis

Ⅱ-1. Fast block cleanout (Fast commit)

Ⅱ-2. Delayed block cleanout using “commit SCN” of transaction slot

Ⅱ-3. Efficient Delayed Block Cleanout

(Delayed block cleanout using “commit SCN” of TRN CTL)

Ⅱ-4. Inefficient Delayed Block Cleanout

(Delayed block cleanout using ”commit SCN” of transaction slot before-image chaining)

Table of Agenda


Ⅰ. Transaction slot Before-image Chaining

Oracle Deep Internal


Ⅰ-1. Questions

进行 Select 操作时，容易发现现在使用的大容量 Oracle 数据中常常存在owi ‘db file sequential read’。

Undo file Undo block


把transaction slot的commit scn在data block itl的 scn/fsc column中更新

Data blockData block

“清除（Cleanout）”的意思是什么?

Question 1

BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1

Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 C--- 0 scn 0x0000.003dbe3a0x02 0x0007.000.000004ed 0x018000a2.01d5.01 ---- 1 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 (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1

Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 C--- 0 scn 0x0000.003dbe3a0x02 0x0007.000.000004ed 0x018000a2.01d5.01 --U- 1 fsc 0x0000.003dbe4etl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 42

Undo Headerindex state cflags wrap# uel scn dba-----------------------------------------------------------------------------------

0x00 10 0x80 0x04ed 0x0000 0x0000.003dbe4e 0x018000a20x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000

Transaction 状态 Cleanout 状态

Answer 1

Ⅰ-1. Questions

Commit SCN

同步 Commit SCN 前

同步 Commit SCN 后

Transaction 状态


瞬息间完成。

先cleanout一部分，然后以后做cleanout，花了很短的时间。

如果 update 100亿个语句后进行commit操作，完成这100亿个

语句需要多长时间?

Question 2

Answer 2

Ⅰ-1. Questions


Undo是有限的资源，所以Transaction slot被覆盖。为了以后的使

用, 你觉得正在等待的 commit SCN 会发生什么变化呢?

Question 3

• Undo是有限的资源, 不能永远保存。过一段时间， undo 会变成

aging out状态。

• Undo slot的前镜像在 chaining 的过程中被储存。

• 参照后页

Answer 3

Ⅰ-1. Questions


TRN CTLseq: 0x0485 chd: 0x0006 ctl: 0x0005uba: 0x0180011c.0485.31 scn: 0x0000.0022c7ffindex state cflags wrap# uel scn dba-------------------------------------------------------------------------------0x05 9 0x00 0x0ad4 0xffff 0x0000.0022c818 0x0180011e0x06 9 0x00 0x0ad6 0x0005 0x0000.0022c811 0x0180011c

rdba: 0x01800160 (6/352)

TRN CTLseq: 0x0485 chd: 0x0005 ctl: 0x0006 uba: 0x0180011e.0485.02 scn: 0x0000.0022c811index state cflags wrap# uel scn dba--------------------------------------------------------------------------------------0x05 9 0x00 0x0ad4 0x0006 0x0000.0022c818 0x0180011e0x06 10 0x80 0x0ad7 0xffff 0x0000.0022c821 0x0180011f

UNDO BLKrdba: 0x0180011e seq: 0x485 Rec # 0x2 slt: 0x06 xid: 0x0001.006.00000ad7uba: 0x0180011c.0485.31 ctl max scn: 0x0000.0022c7ff prv tx scn: 0x0000.0022c811

即将使用 chd 0x0006 slot，所以uba、 trn ctl SCN、 0x06 slot SCN

会以前镜像的方式储存。

Ⅰ-2. Architecture of transaction slot before image chaining (1/3)

• Chd (chain header): linked list的初始，下次的事务即将使用的transaction table slot

• Ctl (chain tail): linked list的最后，相关undo segment中最后使用的transaction table slot

0x06 slot 更新(state 10, cflags 0x80, SCN 更新)

1

3

rdba: 0x0180011e (6/286)

rdba: 0x01800160 (6/352)

更新过的undo header block在TRN CTL uba 中记录前镜像的位置，

在SCN中记录之前slot的SCN。

2

file#=6 block#=352

1 1 2

2

1




rdba: 0x0180011e (6/286)UNDO BLKrdba: 0x0180011e seq: 0x485 Rec # 0x2 slt: 0x06 xid: 0x0001.006.00000ad7uba: 0x0180011c.0485.31 ctl max scn: 0x0000.0022c7ff prv tx scn: 0x0000.0022c811rdba: 0x0180011f (6/287)

UNDO BLKrdba: 0x0180011f seq: 0x485 Rec # 0x2 slt: 0x05 xid: 0x0001.006.00000ad7uba: 0x0180011e.0485.02 ctl max scn: 0x0000.0022c811 prv tx scn: 0x0000.0022c818

TRN CTLseq: 0x0485 chd: 0x0006 ctl: 0x0005 uba: 0x0180011f.0485.02 scn: 0x0000.0022c818index state cflags wrap# uel scn dba--------------------------------------------------------------------------------------0x05 10 0x80 0x0ad5 0xffff 0x0000.0022c829 0x018001200x06 9 0x00 0x0ad7 0x0005 0x0000.0022c821 0x0180011f



3

rdba: 0x01800160 (6/352)

rdba: 0x01800160 (6/352)

即将使用 chd 0x0005 slot，所以uba, trn ctl SCN, 0x05 slot SCN 会

以前镜像的方式储存。

1

rdba: 0x01800160 (6/352)

更新过的undo header block在TRN CTL uba 中记录前镜像的位置，

在SCN中记录之前slot的SCN。

2

1 11

2

2




UNDO BLKrdba: 0x0180011e seq: 0x485 Rec # 0x2 slt: 0x06 xid: 0x0001.006.00000ad7uba: 0x0180011c.0485.31 ctl max scn: 0x0000.0022c7ff prv tx scn: 0x0000.0022c811

UNDO BLKrdba: 0x0180011f seq: 0x485 Rec # 0x2 slt: 0x05 xid: 0x0001.006.00000ad7uba: 0x0180011e.0485.02 ctl max scn: 0x0000.0022c811 prv tx scn: 0x0000.0022c818

TRN CTLseq: 0x0485 chd: 0x0006 ctl: 0x0005 uba: 0x0180011f.0485.02 scn: 0x0000.0022c818index state cflags wrap# uel scn dba--------------------------------------------------------------------------------------0x05 9 0x00 0x0ad5 0xffff 0x0000.0022c829 0x018001200x06 9 0x00 0x0ad7 0x0005 0x0000.0022c821 0x0180011f

TRN CTLseq: 0x0486 chd: 0x0005 ctl: 0x0006 uba: 0x01800120.0486.02 scn: 0x0000.0022c821index state cflags wrap# uel scn dba--------------------------------------------------------------------------------------0x05 9 0x00 0x0ad5 0x0006 0x0000.0022c829 0x01800120 0x06 10 0x80 0x0ad8 0xffff 0x0000.0022c832 0x01800121

UNDO BLKrdba: 0x01800120 seq: 0x486 Rec # 0x2 slt: 0x06 xid: 0x0001.005.00000ad5 uba: 0x0180011f.0485.02 ctl max scn: 0x0000.0022c818 prv tx scn: 0x0000.0022c821



3

Endless loop..

rdba: 0x01800160 (6/352)

rdba: 0x01800160 (6/352)

rdba: 0x01800160 (6/352)

rdba: 0x01800160 (6/352)

rdba: 0x0180011e (6/286)

rdba: 0x0180011f (6/287)

rdba: 0x01800120 (6/288)


会以前镜像的方式储存。更新过的undo header block在

TRN CTL uba 中记录前镜像的位置，在SCN中记录之前slot的SCN。

1

2

1 11

2

2


是

不

不

是

是

不

Wrap number 一样吗?

前镜像 SCN 比Query SCN 更高吗?

同步 TRN CTL SCN

重复进行跟前镜像的比较

同步相关 SCN

同步相关 SCN

<延迟块清除的处理过程>

高效的延迟块清除

低效的延迟块清除

delayed block cleanout using “commit SCN” of transaction slot


Query SCN 比 TRNCTL SCN更高吗?



<低效的延迟块清除> Undo Header

buffer tsn: 6 rdba: 0x01800160 (6/352)TRN CTL:: seq: 0x0486 chd: 0x0005 ctl: 0x0006 inc: 0x00000000 nfb: 0x0002

mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x01800120.0486.02 scn: 0x0000.0022d821

index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad5 0x0006 0x0000.0022d829 0x01800120 0x06 9 0x00 0x0ad8 0xffff 0x0000.0022d832 0x01800121

Undo Block

buffer tsn: 6 rdba: 0x01800120 (6/288)UNDO BLK:xid: 0x0001.005.00000ad5 seq: 0x486 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000* Rec #0x2 slt: 0x06 objn: 70146(0x00011202) objd: 70146 tblspc: 5(0x00000005)*-----------------------------uba: 0x0180011f.0485.02 ctl max scn: 0x0000.0022d818 prv tx scn: 0x0000.0022d821txn start scn: scn: 0x0000.0022d831 logon user: 5prev brb: 25166111 prev bcl: 0

Undo Block

buffer tsn: 6 rdba: 0x0180011f (6/287)UNDO BLK:xid: 0x0001.006.00000ad7 seq: 0x485 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000* Rec #0x2 slt: 0x05 objn: 70146(0x00011202) objd: 70146 tblspc: 5(0x00000005)*-----------------------------uba: 0x0180011e.0485.02 ctl max scn: 0x0000.0022d811 prv tx scn: 0x0000.0022d818txn start scn: scn: 0x0000.0022d828 logon user: 5prev brb: 25166110 prev bcl: 0

1

2

3

4

Wrap number 比较

把 TRN CTL SCN 跟Query SCN 比较

把前镜像 SCN 跟Query SCN 比较

同步相关前镜像 SCN

NO

NO

NO

Wrap number 一样吗?

Query SCN 比 TRNCTL SCN更高吗?

前镜像 SCN 比Query SCN 更高吗?

同步相关 SCN

0x0ad6 ≠0x0ad8

• Query SCN: 0x0000.0022d811• Wrap#: 0x0ad6 (itl의 xid)

1

2

3

4

d811 < d821

Query SCN TRN CTL SCN

d811 < d818


d811 ≥ d811




Ⅱ. Block cleanout Analysis


Commit SCN↓

9 (Committed)

10 (Active)

0x01 0x0008.013.000018a8

1--U- Commit SCN(Base#)

0x13 0x008004f8

0x008004f8.00cf.06

如果当事务修改数据块的数据量没有超过

Buffer Cache的10%的话，会在会话内存

中生成新列表。

Fast block cleanout“ ”

0x18a8




Ⅱ-2. Delayed block cleanout using “commit SCN” of Transaction slot


Ⅱ-2. Delayed block cleanout using “commit SCN” of transaction slot

Delayed block cleanout

“在commit的时候，没被清除的块（当修改的数据块的数据量超过 buffer cache 的10%）、

在内存上经过 flush 而没有被清除,并且记录在 datafile上的块会在下一次会话时被清除。”

0x13 0x0080006c

0x01 0C--- Commit SCN0x008004f800cf.06

9 (Committed) Commit SCN0x18a8

0x0008.013.000018a8

Row Lock

执行delayed block cleanout的会话使用没有cleanout的事务的xid，确认undo header

block的transaction table（transaction是否完成，SCN）之后，在相关block header的

ITL中进行Lock byte解除, SCN设定，将Flag设定为C---。随后生成相关的redo log。


* Upper bound commit

Upper bound commit 就是…

“一般来说，Oracle系统会使用当下的 commit SCN，但如果发生了overwrite的话，

则为从产生 commit 的时间(811)开始,一直到即将清除的 query SCN(818)的这段时间里的最大值。

Upper bound commit



Update Table T1

Flush Buffer

Commit

Update & Commit

Update & Commit

Update & Commit

Update & Commit

Select Table T1

t1

t2

t3

S1 S2Time

• 让 10个 Undo segments 中 9个处于 offline状态，一个处于 online 状态。

• 让 34个 Undo segments 中 32个处于 active状态，仅仅使用两个。测试

SGA

Undo SegmentData Block


Rows

1. 初始状态

ITL

0x01 0x002c.002.000000b4 0x00000000.0000.00 C--- 0 scn 0x0000.006e84260x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

Undo Header

block_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

buffer tsn: 6 rdba: 0x01800120 (6/288)TRN CTL:: seq: 0x1cc1 chd: 0x0014 ctl: 0x0004 inc: 0x00000000 nfb: 0x0001

mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011c.1cc1.05 scn: 0x0000.006e8ee1

index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x04 9 0x00 0x3538 0xffff 0x0000.006e8ee4 0x0180011b0x14 9 0x00 0x353a 0x0004 0x0000.006e8ee3 0x0180011c

Undo Block

Itl Xid Uba Flag Lck Scn/fsc

ASCII code 41=> Value ‘A’

SGA



Rows

ITL

2. update t1 set col2='B';

Undo Header








ㅁ




Undo Block

buffer tsn: 6 rdba: 0x0180011b (6/283)UNDO BLK:xid: 0x0006.014.0000353b seq: 0x1cc1 cnt: 0x31 irb: 0x31 icl: 0x0 flg: 0x0000* Rec #0xa slt: 0x14 objn: 78003(0x000130b3) objd: 78003 tblspc: 5(0x00000005)*-----------------------------uba: 0x0180011c.1cc1.05 ctl max scn: 0x0000.006e8ee1 prv tx scn: 0x0000.006e8ee3txn start scn: scn: 0x0000.006e8efa logon user: 5prev brb: 25166108 prev bcl: 0

将 undo 段头块上传至内存

SGA



Rows

ITL


Undo Header











Undo Block



会以前镜像方式储存。

SGA



Rows

ITL

Undo Block



Undo Header











BH (0x63fe3de8) file#: 6 rdba: 0x01800120 (6/288) class: 27 ba: 0x63d6e000st: XCURRENT md: NULL tch: 1buffer tsn: 6 rdba: 0x01800120 (6/288)…TRN CTL:: seq: 0x1cc1 chd: 0x0004 ctl: 0x0004 inc: 0x00000000 nfb: 0x0000

mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011b.1cc1.0a scn: 0x0000.006e8ee3

…index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x04 9 0x00 0x3538 0xffff 0x0000.006e8ee4 0x0180011b 0x14 10 0x80 0x353b 0x0005 0x0000.006e8efa 0x0180011b

更新过的undo header block在TRN CTLuba 中记录前镜像的位置，在SCN中记录

之前slot的SCN。

有前镜像的Undo block 位置

0x14 slot 更新(state, cflag, wrap#, scn)

SGA



Rows

ITL

Undo Block






Undo Header








Rows

ITL



Itl Xid Uba Flag Lck Scn/fscItl Xid Uba Flag Lck Scn/fsc

SGA



Rows

ITL

Undo Block






Undo Header








Rows

ITL




0x01 0x002c.002.000000b4 0x00000000.0000.00 C--- 0 scn 0x0000.006e84260x02 0x0006.014.0000353b 0x0180011b.1cc1.28 ---- 3 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000



ASCII code 42=> Value ‘B’

SGA



Rows


ITL



3. alter system flush buffer_cache ;

Undo Header

Undo Block



index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x04 9 0x00 0x3538 0xffff 0x0000.006e8ee4 0x0180011b0x14 10 0x80 0x353b 0x0005 0x0000.006e8efa 0x0180011b


flush


SGA



4. commit;

BH (0x637f5020) file#: 6 rdba: 0x01800120 (6/288) class: 27 ba: 0x63730000st: XCURRENT md: NULL fpin: 'ktuwh61: ktugus:ktucmt' tch: 1buffer tsn: 6 rdba: 0x01800120 (6/288)…TRN CTL:: seq: 0x1cc1 chd: 0x0004 ctl: 0x0014 inc: 0x00000000 nfb: 0x0001


…index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x04 9 0x00 0x3538 0x0014 0x0000.006e8ee4 0x0180011b 0x14 9 0x00 0x353b 0xffff 0x0000.006e8efd 0x0180011b

Rows


ITL



Undo Header

Undo Block






SGA



5.

Undo Block

Undo Header

Rows


ITL




mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011b.1cc1.32 scn: 0x0000.006e8ee4

…index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------

0x04 10 0x80 0x3539 0x0005 0x0000.006e8f05 0x0180011d0x14 9 0x00 0x353b 0xffff 0x0000.006e8efd 0x0180011b




buffer tsn: 6 rdba: 0x0180011d (6/285)UNDO BLK:xid: 0x0006.004.00003539 seq: 0x1cc1 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000* Rec #0x1 slt: 0x04 objn: 78004(0x000130b4) objd: 78004 tblspc: 5(0x00000005)col 1: [2000]41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20


Session2> Update t2 set col2=‘B’;

Session2> Commit;

SGA



6.

Rows


ITL



Undo Block

Undo Header


mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011d.1cc1.02 scn: 0x0000.006e8efd

…index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x04 9 0x00 0x3539 0xffff 0x0000.006e8f06 0x0180011d 0x14 10 0x80 0x353c 0x0005 0x0000.006e8f0d 0x0180011e




buffer tsn: 6 rdba: 0x0180011e (6/286)UNDO BLK:xid: 0x0006.014.0000353c seq: 0x1cc1 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000* Rec #0x1 slt: 0x14 objn: 78004(0x000130b4) objd: 78004 tblspc: 5(0x00000005)col 1: [2000]50 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20



Session2> Commit;

SGA



7.

Rows


ITL



Undo Block

Undo Header


mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011e.1cc1.02 scn: 0x0000.006e8f06

…index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x04 10 0x80 0x353a 0x0005 0x0000.006e8f15 0x0180011f0x14 9 0x00 0x353c 0xffff 0x0000.006e8f0e 0x0180011e




buffer tsn: 6 rdba: 0x0180011f (6/287)UNDO BLK:xid: 0x0006.004.0000353a seq: 0x1cc1 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000* Rec #0x1 slt: 0x04 objn: 78004(0x000130b4) objd: 78004 tblspc: 5(0x00000005)col 1: [2000]51 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20



Session2> Commit;

SGA



8.

Rows


ITL



Undo Block

Undo Header


mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011f.1cc1.02 scn: 0x0000.006e8f0e

…index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x04 9 0x00 0x353a 0xffff 0x0000.006e8f16 0x0180011f 0x14 10 0x80 0x353d 0x0005 0x0000.006e8f1c 0x01800130




buffer tsn: 6 rdba: 0x01800130 (6/304)UNDO BLK:xid: 0x0006.014.0000353d seq: 0x1cc2 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000

* Rec #0x1 slt: 0x14 objn: 78004(0x000130b4) objd: 78004 tblspc: 5(0x00000005)col 1: [2000]52 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20



Session2> Commit;

SGA



BH (0x647efe78) file#: 6 rdba: 0x01800120 (6/288) class: 27 ba: 0x646aa000st: XCURRENT md: NULL fpin: 'ktuwh05: ktugct' tch: 1buffer tsn: 6 rdba: 0x01800120 (6/288)…TRN CTL:: seq: 0x1cc2 chd: 0x0004 ctl: 0x0014 inc: 0x00000000 nfb: 0x0001


…index state cflags wrap# uel scn dba

-----------------------------------------------------------------------------------------0x04 9 0x00 0x353a 0x0014 0x0000.006e8f16 0x0180011f 0x14 9 0x00 0x353d 0xffff 0x0000.006e8f1e 0x01800130

9. select /*+ full(t1) */count(*) from t1 where rownum <= 1;

BH (0x657f85c0) file#: 5 rdba: 0x01418413 (5/99347) class: 1 ba: 0x65788000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1buffer tsn: 5 rdba: 0x01418413 (5/99347)…

0x01 0x002c.002.000000b4 0x00000000.0000.00 C--- 0 scn 0x0000.006e84260x02 0x0006.014.0000353b 0x0180011b.1cc1.28 C-U- 0 scn 0x0000.006e8f0e0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000…block_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] 42

Rows


ITL



Undo Block

Undo Header



index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x04 9 0x00 0x353a 0x0014 0x0000.006e8f16 0x0180011f 0x14 9 0x00 0x353d 0xffff 0x0000.006e8f1e 0x01800130






Begin cleaning out block ...Block header dump: 0x01418413…Itl Xid Uba Flag Lck Scn/Fsc0x01 0x002c.002.000000b4 0x00000000.0000.00 C--- 0 scn 0x0000.006e84260x02 0x0006.014.0000353b 0x0180011b.1cc1.28 ---- 3 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

WAIT #139971596639184: nam='db file sequential read' ela= 15 file#=6 block#=288 blocks=1 obj#=0 tim=1460423039184663…Block cleanout record, scn: 0xffff.ffffffff ver: 0x01 opt: 0x01, entries follow...

itli: 2 flg: 1 scn: 0x0000.006e8f0eBlock header dump: 0x01418413…Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x002c.002.000000b4 0x00000000.0000.00 C--- 0 scn 0x0000.006e84260x02 0x0006.014.0000353b 0x0180011b.1cc1.28 C-U- 0 scn 0x0000.006e8f0e0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

Undo header Block

SGA



BH (0x647efe78) file#: 6 rdba: 0x01800120 (6/288) class: 27 ba: 0x646aa000st: XCURRENT md: NULL fpin: 'ktuwh05: ktugct' tch: 1buffer tsn: 6 rdba: 0x01800120 (6/288)…TRN CTL:: seq: 0x1cc2 chd: 0x0004 ctl: 0x0014 inc: 0x00000000 nfb: 0x0001



-----------------------------------------------------------------------------------------0x04 9 0x00 0x353a 0x0014 0x0000.006e8f16 0x0180011f 0x14 9 0x00 0x353d 0xffff 0x0000.006e8f1e 0x01800130


BH (0x657f85c0) file#: 5 rdba: 0x01418413 (5/99347) class: 1 ba: 0x65788000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1buffer tsn: 5 rdba: 0x01418413 (5/99347)…

0x01 0x002c.002.000000b4 0x00000000.0000.00 C--- 0 scn 0x0000.006e84260x02 0x0006.014.0000353b 0x0180011b.1cc1.28 C-U- 0 scn 0x0000.006e8f0e0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000…block_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] 42

Rows


ITL



Undo Block

Undo Header



index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x04 9 0x00 0x353a 0x0014 0x0000.006e8f16 0x0180011f 0x14 9 0x00 0x353d 0xffff 0x0000.006e8f1e 0x01800130







Ⅱ-4. Inefficient Delayed Block Cleanout.



Ⅱ-4. Inefficient Delayed Block Cleanout

• 让 10个 Undo segments 中 9个处于 offline状态，一个处于 online 状态。

• 让 34个 Undo segments 中 32个处于 active状态，仅仅使用两个。测试

Update Table T1

Flush Buffer

Commit

Select Table T1

t1

t2

t4

S1 S2Time

t3Update & Commit

Update & Commit

Update & Commit

Update & Commit

Set transaction

read only

SGA



Rows


1.

ITL

0x01 0x001c.005.00000010 0x00000000.0000.00 C--- 0 scn 0x0000.0022bf230x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

Undo Header


mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011c.0485.05 scn: 0x0000.0022c7fb

index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad3 0xffff 0x0000.0022c7ff 0x0180011d 0x06 9 0x00 0x0ad5 0x0005 0x0000.0022c7fe 0x0180011c


Undo Block


初始状态

SGA



将 undo 段头块上传至内存

Rows


ITL


0x01 0x001e.01b.0000000d 0x00000000.0000.00 C--- 0 scn 0x0000.002263d90x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

Undo Block

buffer tsn: 6 rdba: 0x0180011c (6/284)UNDO BLK:xid: 0x0001.006.00000ad6 seq: 0x485 cnt: 0x30 irb: 0x30 icl: 0x0 flg: 0x0000* Rec #0x9 slt: 0x06 objn: 70145(0x00011201) objd: 70145 tblspc: 5(0x00000005)

uba: 0x0180011c.0485.05 ctl max scn: 0x0000.0022c7fb prv tx scn: 0x0000.0022c7fetxn start scn: scn: 0x0000.0022c809 logon user: 5prev brb: 25166108 prev bcl: 0


Undo Header








SGA



Rows


ITL



Undo Block

buffer tsn: 6 rdba: 0x0180011c (6/284)UNDO BLK:xid: 0x0001.006.00000ad6 seq: 0x485 cnt: 0x30 irb: 0x30 icl: 0x0 flg: 0x0000* Rec #0x9 slt: 0x06 objn: 70145(0x00011201) objd: 70145 tblspc: 5(0x00000005)

uba: 0x0180011c.0485.05 ctl max scn: 0x0000.0022c7fb prv tx scn: 0x0000.0022c7fetxn start scn: scn: 0x0000.0022c809 logon user: 5prev brb: 25166108 prev bcl: 0


Undo Header








即将使用 chd 0x0006 slot，所以uba、 trn ctl SCN、 0x06 slot SCN会以前镜像的方式储存。

SGA






Rows


ITL



Undo Block

buffer tsn: 6 rdba: 0x0180011c (6/284)UNDO BLK:xid: 0x0001.006.00000ad6 seq: 0x485 cnt: 0x30 irb: 0x30 icl: 0x0 flg: 0x0000* Rec #0x9 slt: 0x06 objn: 70145(0x00011201) objd: 70145 tblspc: 5(0x00000005)*-----------------------------uba: 0x0180011c.0485.05 ctl max scn: 0x0000.0022c7fb prv tx scn: 0x0000.0022c7fetxn start scn: scn: 0x0000.0022c809 logon user: 5prev brb: 25166108 prev bcl: 0


Undo Header





BH (0x663ea0a0) file#: 6 rdba: 0x01800160 (6/352) class: 17 ba: 0x66210000st: XCURRENT md: NULL tch: 1buffer tsn: 6 rdba: 0x01800160 (6/352)…TRN CTL:: seq: 0x0485 chd: 0x0005 ctl: 0x0005 inc: 0x00000000 nfb: 0x0001

mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011c.0485.09 scn: 0x0000.0022c7fe


-----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad3 0xffff 0x0000.0022c7ff 0x0180011d 0x06 10 0x80 0x0ad6 0x0005 0x0000.0022c809 0x0180011c

有前镜像的Undo block 位置

更新过的undo header block在TRN CTLuba 中记录前镜像的位置，在SCN中记录

之前slot的SCN。

0x06 slot 更新(state, cflag, wrap#, scn)

SGA






Rows


ITL



Undo Block



Undo Header









Rows


ITL



SGA






Rows


ITL



Undo Block



Undo Header









Rows


ITL


Itl Xid Uba Flag Lck Scn/fsc0x01 0x001c.005.00000010 0x00000000.0000.00 C--- 0 scn 0x0000.0022bf230x02 0x0001.006.00000ad6 0x0180011c.0485.27 ---- 3 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000




SGA



Rows


ITL


0x01 0x001c.005.00000010 0x00000000.0000.00 C--- 0 scn 0x0000.0022bf230x02 0x0001.006.00000ad6 0x0180011c.0485.27 ---- 3 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

3. alter system flush buffer_cache ;

Undo Header



index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad3 0xffff 0x0000.0022c7ff 0x0180011d 0x06 10 0x80 0x0ad6 0x0005 0x0000.0022c809 0x0180011c

Undo Block


flush


SGA



4. commit;

Rows


ITL



Undo Header




BH (0x66be9f68) file#: 6 rdba: 0x01800160 (6/352) class: 17 ba: 0x66a0e000st: XCURRENT md: NULL fpin: 'ktuwh61: ktugus:ktucmt' tch: 1buffer tsn: 6 rdba: 0x01800160 (6/352)…TRN CTL:: seq: 0x0485 chd: 0x0005 ctl: 0x0006 inc: 0x00000000 nfb: 0x0002


…index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad3 0x0006 0x0000.0022c7ff 0x0180011d 0x06 9 0x00 0x0ad6 0xffff 0x0000.0022c811 0x0180011c

Undo Block



SGA



5. Set transaction read only ;

Rows


ITL



Undo Header




Undo Block



<Read only >

• Commit (0x0000.0022c811)

• Commit (0x0000.0022c818)

SGA



6.

Rows


ITL


Undo Block

Undo Header




Session2> Commit;


mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011c.0485.31 scn: 0x0000.0022c7ff

index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad4 0xffff 0x0000.0022c818 0x0180011e0x06 9 0x00 0x0ad6 0x0005 0x0000.0022c811 0x0180011c

buffer tsn: 6 rdba: 0x0180011c (6/284)UNDO BLK:xid: 0x0001.005.00000ad4 seq: 0x485 cnt: 0x32 irb: 0x32 icl: 0x0 flg: 0x0000* Rec #0x31 slt: 0x05 objn: 70146(0x00011202) objd: 70146 tblspc: 5(0x00000005)*-----------------------------uba: 0x0180011c.0485.09 ctl max scn: 0x0000.0022c7fe prv tx scn: 0x0000.0022c7fftxn start scn: scn: 0x0000.0022c816 logon user: 5prev brb: 25166109 prev bcl: 0

SGA



7.

Rows


ITL


Undo Block

Undo Header




Session2> Commit;


mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011e.0485.02 scn: 0x0000.0022c811

index state cflags wrap# uel scn dba -----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad4 0x0006 0x0000.0022c818 0x0180011e0x06 9 0x00 0x0ad7 0xffff 0x0000.0022c821 0x0180011f

buffer tsn: 6 rdba: 0x0180011e (6/286)UNDO BLK:xid: 0x0001.006.00000ad7 seq: 0x485 cnt: 0x3 irb: 0x3 icl: 0x0 flg: 0x0000* Rec #0x2 slt: 0x06 objn: 70146(0x00011202) objd: 70146 tblspc: 5(0x00000005)*-----------------------------uba: 0x0180011c.0485.31 ctl max scn: 0x0000.0022c7ff prv tx scn: 0x0000.0022c811txn start scn: scn: 0x0000.0022c820 logon user: 5prev brb: 25166108 prev bcl: 0

SGA



8.

Rows


ITL



Undo Block

Undo Header



Session2> Commit;


mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x0180011f.0485.02 scn: 0x0000.0022c818

index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad5 0xffff 0x0000.0022c829 0x018001200x06 9 0x00 0x0ad7 0x0005 0x0000.0022c821 0x0180011f

buffer tsn: 6 rdba: 0x0180011f (6/287)UNDO BLK:xid: 0x0001.006.00000ad7 seq: 0x485 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000* Rec #0x2 slt: 0x05 objn: 70146(0x00011202) objd: 70146 tblspc: 5(0x00000005)*-----------------------------uba: 0x0180011e.0485.02 ctl max scn: 0x0000.0022c811 prv tx scn: 0x0000.0022c818txn start scn: scn: 0x0000.0022c828 logon user: 5prev brb: 25166110 prev bcl: 0

SGA



9.

Rows


ITL



Undo Block

Undo Header



Session2> Commit;


mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)uba: 0x01800120.0486.02 scn: 0x0000.0022c821

index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad5 0x0006 0x0000.0022c829 0x01800120 0x06 9 0x00 0x0ad8 0xffff 0x0000.0022c832 0x01800121

buffer tsn: 6 rdba: 0x01800120 (6/288)UNDO BLK:xid: 0x0001.005.00000ad5 seq: 0x486 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000* Rec #0x2 slt: 0x06 objn: 70146(0x00011202) objd: 70146 tblspc: 5(0x00000005)*-----------------------------uba: 0x0180011f.0485.02 ctl max scn: 0x0000.0022c818 prv tx scn: 0x0000.0022c821txn start scn: scn: 0x0000.0022c831 logon user: 5prev brb: 25166111 prev bcl: 0

SGA



BH (0x623e85d0) file#: 6 rdba: 0x01800160 (6/352) class: 17 ba: 0x621e4000st: XCURRENT md: NULL fpin: 'ktuwh05: ktugct' tch: 0buffer tsn: 6 rdba: 0x01800160 (6/352)…TRN CTL:: seq: 0x0486 chd: 0x0005 ctl: 0x0006 inc: 0x00000000 nfb: 0x0002


…index state cflags wrap# uel scn dba -----------------------------------------------------------------

0x05 9 0x00 0x0ad5 0x0006 0x0000.0022c829 0x01800120 0x06 9 0x00 0x0ad8 0xffff 0x0000.0022c832 0x01800121


Rows


ITL



Undo Block

buffer tsn: 6 rdba: 0x01800121 (6/289)UNDO BLK:xid: 0x0001.006.00000ad8 seq: 0x486 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000* Rec #0x1 slt: 0x06 objn: 70146(0x00011202) objd: 70146 tblspc: 5(0x00000005)

Undo Header




BH (0x66bf7720) file#: 5 rdba: 0x014000e3 (5/227) class: 1 ba: 0x66b70000st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1buffer tsn: 5 rdba: 0x014000e3 (5/227)…

0x01 0x001c.005.00000010 0x00000000.0000.00 C--- 0 scn 0x0000.0022bf230x02 0x0001.006.00000ad6 0x0180011c.0485.27 C-U- 0 scn 0x0000.0022c8110x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000…block_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] 42



Begin cleaning out block ...Block header dump: 0x014000e3…Itl Xid Uba Flag Lck Scn/fsc

0x01 0x001c.005.00000010 0x00000000.0000.00 C--- 0 scn 0x0000.0022bf230x02 0x0001.006.00000ad6 0x0180011c.0485.27 ---- 3 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000WAIT #140004262172792: nam='db file sequential read' ela= 9 file#=6 block#=352 blocks=1 obj#=0 tim=1459261058673224WAIT #140004262172792: nam='db file sequential read' ela= 13 file#=6 block#=288 blocks=1 obj#=0 tim=1459261058676417WAIT #140004262172792: nam='db file sequential read' ela= 10 file#=6 block#=287 blocks=1 obj#=0 tim=1459261058677687…Block cleanout record, scn: 0x0000.0022c834 ver: 0x01 opt: 0x01, entries follow...

itli: 2 flg: 1 scn: 0x0000.0022c811Block header dump: 0x014000e3…Itl Xid Uba Flag Lck Scn/fsc

0x01 0x001c.005.00000010 0x00000000.0000.00 C--- 0 scn 0x0000.0022bf230x02 0x0001.006.00000ad6 0x0180011c.0485.27 C-U- 0 scn 0x0000.0022c8110x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

Undoheader Block

第二次 update Undo Block

第一次 update Undo Block

BH (0x673f4b40) file#: 6 rdba: 0x01800160 (6/352) class: 17 ba: 0x67328000st: CR md: NULL tch: 1buffer tsn: 6 rdba: 0x01800160 (6/352)…TRN CTL:: seq: 0x0486 chd: 0x0005 ctl: 0x0006 inc: 0x00000000 nfb: 0x0002


…index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad4 0x0006 0x0000.0022c818 0x0180011e0x06 9 0x00 0x0ad7 0xffff 0x0000.0022c821 0x0180011f





Undo header Block



SGA








Rows


ITL



Undo Block

Undo Header













Undo header Block



Scn: 0x0000.0022c821

Scn: 0x0000.0022c818

Scn: 0x0000.0022c811

SGA








Rows


ITL



Undo Block

Undo Header













Undo header Block



BH (0x673f4b40) file#: 6 rdba: 0x01800160 (6/352) class: 17 ba: 0x67328000st: CR md: NULL tch: 1buffer tsn: 6 rdba: 0x01800160 (6/352)…TRN CTL:: seq: 0x0486 chd: 0x0005 ctl: 0x0006 inc: 0x00000000 nfb: 0x0002


…index state cflags wrap# uel scn dba-----------------------------------------------------------------------------------------0x05 9 0x00 0x0ad4 0x0006 0x0000.0022c818 0x0180011e0x06 9 0x00 0x0ad7 0xffff 0x0000.0022c821 0x0180011f

Scn: 0x0000.0022c821

Scn: 0x0000.0022c818

Scn: 0x0000.0022c811

适用 CR块



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

Tudou http://www.tudou.com/home/maxgauge/

[email protected]

[email protected]

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

http://www.tudou.com/home/maxgauge/

【中文】 odi no.004 analysis of oracle performance degradation caused by inefficient block...

Technology

Transcript of 【中文】 odi no.004 analysis of oracle performance degradation caused by inefficient block...