In this post I will demonstrate that flushing the buffer cache causes dirty blocks to be written to disk .
– Create simple table whose one row occupies one block
HR1> drop table t1 purge;
create table t1
(c1 int, c2 char(2000), c3 char(2000), c4 char(2000));
– Insert one row
HR1> insert into t1 values(1, ‘pre update’, ‘x’, ‘x’);
commit;
– Get the block address and browse X$BH view for that block.
HR1> select dbms_rowid.rowid_relative_fno(rowid) as file#,
dbms_rowid.rowid_block_number(rowid) as block#
from t1;
File# Block#
—— ——
4 550
– Check in x$bh that We have one CURRENT block(state=xcur).
Enter file#/block# found above when prompted
SYS1>select b.dbarfil, b.dbablk, b.class,
decode(state,0,’free’,1,’xcur’,2,’scur’,3,’cr’,
4,’read’,5,’mrec’,6,’irec’,7,’write’,8,’pi’, 9,’memory’,10,’mwrite’,11,’donated’) as state,
cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq,
(select object_name from dba_objects where object_id = b.obj) as object_name
from sys.x$bh b
where dbarfil = &file_no and
dbablk = &block_no;
Enter value for file_no: 4
old 6: where dbarfil = &file_no and
new 6: where dbarfil = 4 and
Enter value for block_no: 550
old 7: dbablk = &block_no
new 7: dbablk = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———-
———- ——————–
4 550 1 xcur 0 0 0 0
0 T1
– Update the table so that the block becomes dirty.
Do not commit the transaction so that info about the change is not written to redo logs.
HR1>update t1 set c2=’updated';
– Check that two versions(current and cr clone) of the block are there in buffer cache
SYS1>/
Enter value for file_no: 4
old 6: where dbarfil = &file_no and
new 6: where dbarfil = 4 and
Enter value for block_no: 550
old 7: dbablk = &block_no
new 7: dbablk = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———-
———- ——————–
4 550 1 xcur 0 0 0 0
0 T1
4 550 1 cr 24279860 0 0 0
0 T1
– Flush the buffer cache –
SYS2>alter system flush buffer_cache;
– Check that there are not any versions of the block (state=free) in buffer cache
SYS1>/
Enter value for file_no: 4
old 6: where dbarfil = &file_no and
new 6: where dbarfil = 4 and
Enter value for block_no: 550
old 7: dbablk = &block_no
new 7: dbablk = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———-
———- ——————–
4 550 1 free 0 0 0 0
0 T1
4 550 1 free 0 0 0 0
0 T1
– Query the table from the same session from where update was issued
Autotrace the query to verify that blocks were read from the disk (physical reads > 0)
Note that the updated row is displayed.
HR1>set autotrace on
col c2 for a20;
select c2 from t1;
C2
——————–
updated
Statistics
———————————————————-
0 recursive calls
0 db block gets
7 consistent gets
6 physical reads
0 redo size
2423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
– Check that current version of the block (state=xcur) has been read into the buffer cache
SYS1>/
Enter value for file_no: 4
old 6: where dbarfil = &file_no and
new 6: where dbarfil = 4 and
Enter value for block_no: 550
old 7: dbablk = &block_no
new 7: dbablk = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———-
———- ——————–
4 550 1 xcur 0 0 0 0
0 T1
4 550 1 free 0 0 0 0
0 T1
– Query the table from another session. This session should see the pre update value of the
column.
Check that physical reads = 0 as current block is already there in buffer cache
Consistent reads > 0 as undo tablespace has been read to construct cr block
HR2>set autotrace on
col c2 for a20
set line 80
select c2 from t1;
C2
——————–
pre update
Statistics
———————————————————-
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
108 redo size
2423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
– Check that cr version of the block has been created in buffer cache (state=cr)
after reading the undo blocks (CR_UBA_FIL = 3, CR_UBA_BLK = 189980)
SYS1>/
Enter value for file_no: 4
old 6: where dbarfil = &file_no and
new 6: where dbarfil = 4 and
Enter value for block_no: 550
old 7: dbablk = &block_no
new 7: dbablk = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———-
———- ——————–
4 550 1 cr 24294089 0 3 189980
941 T1
4 550 1 xcur 0 0 0 0
0 T1
Conclusion:
Flushing the buffer cache causes dirty blocks to be written to disk .
———————————————————————————————————-
Related links: