FLUSH BUFFER CACHE

                   

  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:

 

5 thoughts on “FLUSH BUFFER CACHE

  1. hi
    i am new to oracle platform ,can any explain about oracle.., is
    where to start such as
    1.sql/pl or
    2.10g or
    11g or
    12c
    i am ver much confused
    from
    M. Deepak

    1. Hi Deepak,

      You need to learn SQL to start with. You will need to learn PL/SQL if you want to be a developer. For DBA profile, PL/SQL is not mandatory though it helps if you know.
      10g, 11g and 12c are various versions of oracle. You can start with 11g and then upgrade to 12c.

      Regards
      Anju

Leave a Reply to Anju Garg Cancel reply