oracle 延迟块清楚,oracle_延迟块清理(delayed block cleanout)

此篇文章已经拖了好久，因为是无从下手，看了很多文章和试验很多次，都无法达到理解的情况；

在做前面试验时，对data block dump的输出文件ITL.Flag可以看到不同的值；

Itl  Xid

Uba

Flag  Lck  Scn/Fsc

0x01  0x0008.013.0000024a

0x00c00de4.014f.08  C---

0  scn

0x0000.0022e1a6

0x02  0x0005.014.00000264

0x00c001e2.006b.30  ----

1  fsc

0x0000.00000000

Flag： 事务标志位。记录当前事务的操作状态。

Flag: C = Commited; U=Commit Upper Bound; T=active at

csc

C=Transaction has been commited and locks cleaned out.

事务已提交，锁已被清除；

B=this undo record contains the undo for this ITL entry

undo记录包含撤销的TIL条目；

U=transaction commited(maybe long ago); SCN is an upper

bound.

事务已提交(可能很久之前)，SCN是最大范围值，锁还没清除；

T=Transaction was still active at block cleanout SCN.

块清除的SCN被记录时，该事务仍然是活动的，块上如果有已经提交的事务，

那么在cleanout的时候，块会被进行清除，但是这个块里面的事务不会被清除.

Scn/Fsc：Commit SCN或者快速提交(Fast Commit Fsc)的SCN.

U=transaction commited(maybe long ago); SCN is an upper

bound.

----创建测试表

SQL> set line 290 pages 2000

SQL> select rowid,

2

Dbms_rowid.rowid_object(rowid) objid,

3

dbms_rowid.rowid_relative_fno(rowid)rel_fno,

4

dbms_rowid.rowid_block_number(rowid)

blockno,

5

dbms_rowid.rowid_row_number(rowid)

rownumb

6  from sun.itltab;

ROWID  OBJID

REL_FNO  BLOCKNO  ROWNUMB

------------------ ---------- ---------- ----------

----------

AAAFG4AAGAAAAQRAAA  20920  6

1041

0

AAAFG4AAGAAAAQRAAB  20920  6

1041

1

------段在buffer中的情况

SQL> SELECT class#,status,dirty FROM v$bh WHERE file#=6 AND

block#=1041;

CLASS# STATUS

D

---------- ---------- -

1 xcur  N

status状态的意义分别是：

xcur：(exclusive current)的意思，表示该数据块处于排外模式；

cr：表示该数据块是一个克隆(clone)的数据块，可以执行共享的只读操作；

free：表示这是一个空闲的数据块，oracle现在没有使用它；

read：表示该数据块正在从磁盘读取数据；

write：表示数据库正在往磁盘写入数据；

mrec：(media recovery)表示数据块处于介质恢复模式；

irec：(instance recovery)表示数据块处于实例恢复模式；

scur

(shared current)，表示该数据库正在和其他实例共享数据；

CLASS#  NUMBER

1,'data block',

2,'sort block',

3,'save undo block',

4,'segment header',

5,'save undo header',

6,'free list',

7,'extent map',

8,'1st

level bmb',

9,'2nd

level bmb',

10,'3rd level bmb',

11,'bitmap block',

12,'bitmap index block',

13,'file header block',

14,'unused',

15,'system undo header',

16,'system undo block',

17,'undo header',

18,'undo block'

--session1

SQL> update sun.itltab set name='SUN1' WHERE id=1;

1 row updated.

SQL>commit;

--session2

select xidusn,xidslot,xidsqn,to_char(start_scnw,'xxxxxxxx')

start_scnw,

to_char(start_scnb,'xxxxxxxx') start_scnb,

start_scnb+start_scnw*power(2,32) start_scn

from v$transaction;

SQL> select

xidusn,xidslot,xidsqn,to_char(start_scnw,'xxxxxxxx') start_scnw,

2  to_char(start_scnb,'xxxxxxxx') start_scnb,

3  start_scnb+start_scnw*power(2,32) start_scn from v$transaction;

XIDUSN

XIDSLOT  XIDSQN START_SCN START_SCN

START_SCN

---------- ---------- ---------- --------- ---------

----------

1  24

636  0  288c1f  2657311

SQL> SELECT class#,status,dirty FROM v$bh WHERE file#=6 AND

block#=1041;

CLASS# STATUS

D

---------- ---------- -

1 cr  N

1 xcur  Y

SQL>alter system flush buffer_cache;

SQL>alter system dump datafile 6 block 1041;

Itl  Xid

Uba

Flag  Lck  Scn/Fsc

0x01  0x0001.005.0000027b

0x00c00082.00e9.07  C---

0  scn

0x0000.00288a3a

0x02  0x0001.018.0000027c

0x00c00082.00e9.08  --U-

1  fsc

0x0000.00288eb4

bdba: 0x01800411

dump块信息，可以看到块的ITL, flag表示为U，且lock信息还在，scn已经更新，此类为 fast

commit/block cleanout .

--session3

SQL> update sun.itltab set name='SUN2' WHERE id=1;

1 row updated.

SQL> commit;

Commit complete.

在commit前，在其他会话进行查询表，会发现有redo产生，此时进行了更改falg和lck操作，产生日志；(delayed

logging block cleanout)

---session1

SQL>  update sun.itltab set name='SUN3'

WHERE id=1;

1 row updated.

SQL> alter system flush buffe

SQL> COMMIT;

Itl  Xid

Uba

Flag  Lck  Scn/Fsc

0x01  0x0008.001.0000028c

0x00c00798.0157.19  C---

0  scn

0x0000.0028ddf2

0x02  0x0007.007.00000280

0x00c005b4.00cc.19  ----

1  fsc

0x0000.00000000

看到虽然是提交，但是在block上还是在事务状态，此时只是更改了undo segemnt

header的事务状态为0x9.

--session2

SELECT 操作也会产生redo， 而flush 后块上ITL为：

Itl  Xid

Uba

Flag  Lck  Scn/Fsc

0x01  0x0008.001.0000028c

0x00c00798.0157.19  C---

0  scn

0x0000.0028ddf2

0x02  0x0007.007.00000280

0x00c005b4.00cc.19  C---

0  scn

0x0000.0028de3e

总结：

在事务提交前将脏块部分写入到磁盘后，再进行SELECT操作,会产生redo,清理ITL,此时是delayed block

cleanout。

在事务提交前未将脏块部分写入到磁盘，SELECT不产生redo,当块上再进行DML后，SELECT会产生redo清理之前的ITL。

下面引用Lewis的09年文章：

clean:

A block (or, to be strictly accurate, a buffered copy of a

block) is "clean" if and only if the copy in memory is the same as

the version on disc. Conversly, if the copy in memory and the

version on disc differ (presumably because something has modified

the in-memory copy) then the buffer copy is "dirty". Moreover, we

have to remember that the concepts of "clean" and "dirty" are only

relevant to the current copy of the block.

You have to be  a little careful when

checking for dirty blocks, though. The buffer header structure

(x$bh) includes a column called flag which uses a bit string to

tell us about the state of the buffer. Bit zero in this flag is the

"buffer_dirty" bit so we can easily check which buffers are

considered dirty. There's a little trap, though. Oracle only needs

to write dirty buffers that are also "current" versions (CUR) and

you will find that it doesn't reset the "buffer_dirty" flag when it

makes a read-consistent (CR) copy of the block – or in various

other circumstances.  So if you're trying to count

the number of dirty buffers in x$bh, don't forget to restrict your

count to those where the "state" column is set to 1 or 2 (XCUR and

SCUR)

There is a common misunderstanding about what happens when

DBWR (the database writer) writes a block to disc. I have often

seen people say that the copy of the block is "flushed" from the

cache when the write takes place (i.e. implying that the block

disappears from the cache). Most of the time this is not true – the

dbwr has two main reasons for writing and only one of those two is

followed (I think) by a call to free the buffer that's just been

written.

One reason for writing is simply to keep the data files

reasonably up to date; in this case the buffered copy of the block

changes from dirty to clean but is not flushed from memory. The

other reason is when a session has been searching for a free

buffer, fails to find one soon enough, and calls DBWR to make some

free space in the buffer cache – and that's the case when DBWR may

flush blocks from memory or, to be accurate, marks the buffer as

free after copying them to disc (and I'm not even sure that I've

ever proved that that's really true). There are a couple of fairly

well-known special cases where blocks reallys flushed from disc

after the write: truncating tables, putting tablespaces offline,

and the explicit "flush buffer_cache" spring to mind.

commit cleanout:

When you modify some data you will make some buffered blocks

"dirty". It is quite possible that the database writer will copy

those blocks to disc (marking the buffers "clean") before you issue

your commit. When you issue the commit, your session will update

its transaction table slot (which is in an undo segment header

block), generating a little redo to describe this action, and then

call the log writer (lgwr) to write.

Optionally, your session may also revisit a few of the blocks

it has modified (nominally up to 10% of the buffer cache) and mark

the associated ITL (interested transaction list) entry in those

blocks with the "commit SCN". It is this activity that is called a

commit cleanout. The commit cleanout does not do a complete tidy up

on the block (it leaves lock bytes in place, for example), it

simply ensures that the next process to see the block knows that

your transaction has committed and when it committed. This feature

was introduced some around Oracle version 7.3 to reduce block

pinging in OPS  (Oracle Parallel Server, the

precursor to RAC).

Note that any of the blocks that had previously been written

by dbwr will have been made "dirty" again if your sessions does

applies a commit cleanout to them, so dbwr will have to write them

to disc again eventually. Although a commit cleanout changes blocks

it does not create any redo to describe those changes and,

strangely, it doesn't report db block gets for the block vists that

apply those changes.

[delay] block  cleanout:

It's possible that some of the blocks changed by your

transaction were written to disc by dbwr and were even flushed from

the buffer cache before you issued the commit. Your session will

not re-read these blocks in order to do a commit cleanout on them.

In fact, if your transaction was quite large and modified a lot of

blocks, it's quite possible that your session will not even do a

commit cleanout on all of the blocks that are still in memory –

Oracle doesn't want to make the user wait for a commit to complete,

so it's a bit lazy about commit cleanout.

At some later point in time another session may read one of

those blocks and discover that the ITL includes a transaction that

has committed but not been cleaned out. (It can work this out by

cross-checking the ITL entry with the relevant transaction table

slot in the undo segment header block).

This session will read the commit SCN from the transaction

table slot, tidy up the block's ITL entry, and clear all the

related lock bytes.  (And it will do this for all

committed transactions it finds on the block).

This process is known as block cleanout, and

because this full cleanout mechanism never takes place on the

commit it is more commonly known as delayed block cleanout.

The delayed block cleanout operation changes the block so it

generates redo – which is why you may see redo being generated on a

select statement, especially after a very big update.

(Interestingly, Oracle will report db block changes as this happens

– but doesn't record the block visits as db block gets.)

delay logging block cleanout:

Finally, let's go back to a block that was subject to commit

cleanout. If you query this block you can see the effects of the

committed transaction, but since the commit cleanout wrote the

commit SCN into its ITL slot you know when the transaction

committed and generally don't need to do anything to tidy the block

up as you read it. However if you want to update the block you

become responsible for finishing the cleanout of the block – in

fact you may even want to re-use that ITL entry and update some of

the rows which are still showing a lock byte. So, at this point,

you complete the block cleanout, and generate redo that describes

not only the changed you are now making but also the earlier commit

cleanout.  This process is known as ‘delayed

logging' block cleanout – because the redo log for the commit

cleanout has been delayed until this moment.