Oracle block clearing instructions

1. Block Cleanout description

Article arrangement reference: http://www.orawh.com/60.html

Block clean out refers to changing the data in a block from dirty to clean. It is equivalent to telling the people behind the block that the data in the block is clean and can be safely used, in essence, it is used to change a flag in the block header.

 

When commit is used, if the data block to be committed is still cleanout in the data buffer, the block header (delay block cleanout) is not necessarily modified during commit ).

 

There are two types of Clean out: fast commitcleanout and delayed blockcleanout:

Oracle has a modified block list structure to record the modified blocks of each transaction. Each transaction can record the modified block of about 10% buffer cache. When a commit occurs, oracle can locate the Blocks Based on the modified block list and execute fast commit cleanout.

If a transaction modifies more than 10% buffer cache blocks, delayed block cleanout is executed for the blocks that exceed the cache limit. When used as fast commit cleanout, oracle will not clear the Row lockslb flag and the ITL lck flag.

Another case is delayed block cleanout. when the transaction is not commit or rollback, the modified block has been written back to the disk. When a commit occurs, Oracle does not read the block again for cleanout, the cost is too high. cleanout is reserved for the next access to this block (select, update.

When delayed cleanout, if the transaction table slot of the Undo segment header is not overwritten, you can retrieve the exact SCN submitted by the transaction. If the slot has been overwritten (ITL ), the control SCN In the Undo segment header is used as the upper bound SCN.

When fast commit cleanout occurs, the system uses the SCN of the transaction commit time as the commit SCN, updates the SCN on the transaction table slot of the ITL and undo segment headers on the block, and modifies the block SCN, the three are consistent.

When delayed block cleanout occurs, the previous transaction commit updates only the slot SCN on the Undo segment header transactiontable, but does not update the block. Wait for the next time you use this block, update the status of block SCN and ITL. Block SCN and ITL are updated in two situations:

(1) When delayedblock cleanout is not generated for slot reuse, the Scn/Fsc on blockscn and itl are updated based on the information in the Transactiontable.
(2) When slot reuse is generated, the scn on the corresponding itl is updated to the control scn on the undo segment, and the block scn is the scn at the time when the delayed block cleanout occurs.

 

Ii. Cleanout test 2.1 Fast commit cleanout

-- Create a table and insertinto data

SYS @ anqing2 (rac2)> create table dvd (idnumber );

Table created.

SYS @ anqing2 (rac2)> insert into dvdvalues (1 );

1 row created.

SYS @ anqing2 (rac2)> insert into dvdvalues (2 );

1 row created.

SYS @ anqing2 (rac2)> commit;

Commit complete.

SYS @ anqing2 (rac2)>

 

-- View the block information of a table

SYS @ anqing2 (rac2)> Selectdbms_rowid.rowid_block_number (rowid) block, dbms_rowid.rowid_relative_fno (rowid) fileno, ora_rowscn from dvd;

 

Block fileno ora_rowscn

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

305914 1 7316063

305914 1 7316063

 

-- Update and submit data

Sys @ anqing2 (rac2)> Update DVD set id = 77 Where id = 1;

1 row updated.

Sys @ anqing2 (rac2)> commit;

Commit complete.

 

-- Dump Block

Sys @ anqing2 (rac2)> oradebug setmypid

Statement processed.

Sys @ anqing2 (rac2)> alter system dumpdatafile 1 block 305914;

System altered.

Sys @ anqing2 (rac2)> oradebugtracefile_name

/U01/APP/Oracle/admin/Anqing/udump/anqing2_ora_31100.trc

 

The ITL information of the dump file is as follows:

ITL Xid UBA flag lck scn/FSC

0x01 0x0010. 011.000003c6 0x01400038. 00ae. 2D C --- 0 SCN 0x0000. 006fa25f

0x02 0x0011. 01c. 000004a9 0x01400070. 0111.26 -- U-1 FSC 0x0000. 006fa357

 

In this case, the operation is fast commit cleanout, And the lck and lb flags are not cleared.

 

2.2 Delayed block cleanout

When we update the data and there is no commit, we flush buffer cache to flush the modified data block to the hard disk. In this case, the delay block cleanout occurs.

 

SYS @ anqing2 (rac2)> update dvd set id = 168 where id = 2;

1 row updated.

SYS @ anqing2 (rac2)> Selectxidusn, xidslot, xidsqn from v $ transaction;

XIDUSN XIDSLOT XIDSQN

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

13 15 980

 

SYS @ anqing2 (rac2)> alter system flushbuffer_cache;

System altered.

-- Flush will directly flush the block we modified to the hard disk, although we do not have a commit.

 

SYS @ anqing2 (rac2)> commit;

Commit complete.

-- Now we are commit. Normally, the related SCN in the block will be modified. But in fact, Oracle does not go back to modify these blocks because the re-call is too costly. Oracle only updates the undosegment header slot. When you access this block again next time, update the SCN on the block SCN and ITL according to the Undo segment. If the corresponding undo segment does not exist at this time, it will start the ORA-01555, the snapshot of the old error.

 

-- At this time, the previous block has not been accessed again, that is, delayedblock clean has not occurred. Let's dump the data block.

Sys @ anqing2 (rac2)> alter system dumpdatafile 1 block 305914;

System altered.

 

ITL Xid UBA flag lck scn/FSC

0x01 0x000d. 00f. 000003d4 0x0140002e. 00aa. 21 ----
1 FSC 0x0000.00000000

0x02 0x0011. 01c. 000004a9 0x01400070. 0111.26 -- U-
1 fsc 0x0000. 006fa357

 

Tab 0, row 0, @ 0x1f9a

Tl: 6 fb: -- H-FL -- lb: 0x2 cc: 1

Col 0: [2] c1 4e

Tab 0, row 1, @ 0x1f8d

Tl: 7 fb: -- H-FL -- lb: 0x1 cc: 1

Col 0: [3] c2 02 45

-- The result is fast commit cleanout.

 

-- Access the previous block to generate delayedblock cleanout

SYS @ anqing2 (rac2)> select * from dvd;

 

ID

----------

77

168

 

-- In the next dump block

SYS @ anqing2 (rac2)> alter system dumpdatafile 1 block 305914;

System altered.

 

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000d. 00f. 000003d4 0x0140002e. 00aa. 21 C ---
0 scn 0x0000. 006fa4e6

0x02 0x0011. 01c. 000004a9 0x01400070. 0111.26 C ---
0 scn 0x0000. 006fa357

 

Tab 0, row 0, @ 0x1f9a

Tl: 6 fb: -- H-FL -- lb: 0x0 cc: 1

Col 0: [2] c1 4e

Tab 0, row 1, @ 0x1f8d

Tl: 7 fb: -- H-FL -- lb: 0x0 cc: 1

Col 0: [3] c2 02 45

-- After delayed block cleanout is implemented, ITL becomes SCN. In this case, the lck and lb labels are all cleared, and the SCN is also obtained from the Undo segment header transactiontable slot. If the slot on the undosegment header is overwritten, the control SCN on the Undo segment is used as the upper bound SCN.

 

 

(1) FB:

K = cluster key (flags may change meaning ifthis is set to show hash cluster)
C = cluster Table Member
H = head piece of row
D = deleted row
F = first data piece
L = last data piece
P = first column continues from previous piece
N = last column continues in next piece

(2) LB: corresponds to the LCK of the ITL to whether the row is locked.

 

On Wang Hai's blog, he also tested another conclusion:

When delayed block cleanout occurs, it depends on undo segment. If undo segment is deleted, Oracle will use the Undo $ base table in the system tablespace to ensure delayed block cleanout.

For this test process, refer to the blog: http://www.orawh.com/60.html

 

 

Iii. Description and test of Delayed block cleanout and select redo

In general, select does not produce redo. However, if delayed block cleanout occurs, a redo is generated. Of course, this is only one case. Enabling audit also causes select to generate redo.

There are two related links on itpub:

Http://www.itpub.net/thread-1467473-1-2.html

Http://www.itpub.net/thread-728163-1-1.html

 

Let's test it.

 

-- Insert some test data to the DVD first

Sys @ anqing2 (rac2)> declare

2 I number;

3 begin

4 For I in 1 .. 100 loop

5 Insert into dvd values (I );

End loop;

Commit;

End;

/

6 7 8 9

PL/SQL procedure successfully completed.

 

SYS @ anqing2 (rac2)> select count (*) fromdvd;

 

COUNT (*)

----------

102

 

-- Direct select

SYS @ anqing2 (rac2)> set timing on

SYS @ anqing2 (rac2)> set autot on stat

SYS @ anqing2 (rac2)> select count (*) fromdvd;

 

COUNT (*)

----------

102

 

Elapsed: 00:00:00. 00

 

Statistics

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

0 recursive cballs

0 db block gets

3 consistent gets

0 physical reads

0 redo size

412 bytes sent via SQL * Net toclient

400 bytes received via SQL * Netfrom client

2 SQL * Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (Disk)

1 rows processed

 

The redo generated by the test is 0.

 

-- Manufacture delayedblock cleanout

Sys @ anqing2 (rac2)> Update DVD set id = 0 where ID> 50;

52 rows updated.

Elapsed: 00:00:00. 02

Statistics

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

5 recursive cballs

52 dB block gets

8 consistent gets

0 physical reads

14424 redo size

667 bytes sent via SQL * Net toclient

564 bytes received via SQL * Netfrom client

3 SQL * Net roundtrips to/fromclient

1 sorts (memory)

0 sorts (disk)

52 rows processed

 

SYS @ anqing2 (rac2)> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00. 10

SYS @ anqing2 (rac2)> commit;

Commit complete.

Elapsed: 00:00:00. 02

 

-- Select again

Sys @ anqing2 (rac2)> select count (*) fromdvd;

 

Count (*)

----------

102

 

Elapsed: 00:00:00. 02

 

Statistics

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

0 recursive cballs

0 dB block gets

4 consistent gets

2 Physical reads

116 redo size

412 bytes sent via SQL * Net toclient

400 bytes received via SQL * Netfrom client

2 SQL * Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (disk)

1 rows processed

 

 

-- The third select

SYS @ anqing2 (rac2)> select count (*) fromdvd;

COUNT (*)

----------

102

 

Elapsed: 00:00:00. 00

 

Statistics

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

0 recursive cballs

0 db block gets

3 consistent gets

0 physical reads

0 redo size

412 bytes sent via SQL * Net toclient

400 bytes received via SQL * Netfrom client

2 SQL * Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (disk)

1 rows processed

 

For the second select, due to the reason of delayed block cleanout, some aftercare work is required, so redo is generated. When the third select operation is completed, the second select operation is complete, so no redo is generated.

 

Simply put, select also generates redo when delayed block cleanout.