Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘CURRENT block

Simple and stupid test on consistent read

with 6 comments

One of the most beautiful and important features of Oracle database is consistent read. Yes, that is what makes Oracle be Oracle.

But I always see many misconceptions and confusions on the consistent read among engineers.

So I made a very simple and stupid demonstration to show how Oracle’s consistent read works.

1. Create objects.

-- Create simple table whose one row occupies one block
UKJA@ukja102> create table 
    t1(c1 int, c2 char(2000), c3 char(2000), c4 char(2000));

Table created.

-- Insert one row
UKJA@ukja102> insert into t1 values(1, 'x', 'x', 'x');

1 row created.

UKJA@ukja102> commit;

Commit complete.




2. Get the block address and browse X$BH view for that block.(bh.sql is here)

UKJA@ukja102> col f# new_value file_no
UKJA@ukja102> col b# new_value blk_no
UKJA@ukja102>
UKJA@ukja102> select dbms_rowid.rowid_relative_fno(rowid) as f#,
  2        dbms_rowid.rowid_block_number(rowid) as b#
  3  from t1
  4  ;

        F#         B#
---------- ----------
        10      35470

-- We have one CURRENT block(xcur). CURRENT block is always one and only!
UKJA@ukja102> @bh &file_no &blk_no
DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL
---------- ---------- ---------- ---------- ---------- ---------- ----------
CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- --------------------
        10      35470          1 xcur                0          0          0
         0          0 T1




3. Now, let’s see how consecutive DML statements generate CR blocks.

UKJA@ukja102> -- update and monitor cr block
UKJA@ukja102> -- 1st update
UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> @bh &file_no &blk_no
DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL
---------- ---------- ---------- ---------- ---------- ---------- ----------
CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- --------------------
        10      35470          1 xcur                0          0          0
         0          0 T1

        10      35470          1 cr          599680015       2015          0
         0          0 T1


UKJA@ukja102> -- 2nd update
UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> @bh &file_no &blk_no
old  14:   dbarfil = &1 and
new  14:   dbarfil = 10 and
old  15:   dbablk = &2
new  15:   dbablk = 35470

   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL
---------- ---------- ---------- ---------- ---------- ---------- ----------
CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- --------------------
        10      35470          1 xcur                0          0          0
         0          0 T1

        10      35470          1 cr          599680017       2015          0
         0          0 T1

        10      35470          1 cr          599680015       2015          0
         0          0 T1


UKJA@ukja102> -- 3rd update
UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> @bh &file_no &blk_no
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL
---------- ---------- ---------- ---------- ---------- ---------- ----------
CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- --------------------
        10      35470          1 xcur                0          0          0
         0          0 T1

        10      35470          1 cr          599680019       2015          0
         0          0 T1

        10      35470          1 cr          599680017       2015          0
         0          0 T1

        10      35470          1 cr          599680015       2015          0
         0          0 T1


UKJA@ukja102> -- 4th update
UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> @bh &file_no &blk_no
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL
---------- ---------- ---------- ---------- ---------- ---------- ----------
CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- --------------------
        10      35470          1 xcur                0          0          0
         0          0 T1

        10      35470          1 cr          599680021       2015          0
         0          0 T1

        10      35470          1 cr          599680019       2015          0
         0          0 T1

        10      35470          1 cr          599680017       2015          0
         0          0 T1

        10      35470          1 cr          599680015       2015          0
         0          0 T1

UKJA@ukja102> -- 5th update
UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> @bh &file_no &blk_no
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL
---------- ---------- ---------- ---------- ---------- ---------- ----------
CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- --------------------
        10      35470          1 xcur                0          0          0
         0          0 T1

        10      35470          1 cr          599680023       2015          0
         0          0 T1

        10      35470          1 cr          599680021       2015          0
         0          0 T1

        10      35470          1 cr          599680019       2015          0
         0          0 T1

        10      35470          1 cr          599680017       2015          0
         0          0 T1

        10      35470          1 cr          599680015       2015          0
         0          0 T1


6 rows selected.

UKJA@ukja102> -- 6th update
UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> @bh &file_no &blk_no
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL
---------- ---------- ---------- ---------- ---------- ---------- ----------
CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- --------------------
        10      35470          1 xcur                0          0          0
         0          0 T1

        10      35470          1 cr          599680025       2015          0
         0          0 T1

        10      35470          1 cr          599680023       2015          0
         0          0 T1

        10      35470          1 cr          599680021       2015          0
         0          0 T1

        10      35470          1 cr          599680019       2015          0
         0          0 T1

        10      35470          1 cr          599680017       2015          0
         0          0 T1


6 rows selected.

UKJA@ukja102> commit;

Commit complete.



Oracle seems to generate CR blocks in circual way. The maximum number of CR blocks per CURRENT block is controlled by this hidden parameter. It’s default value is 6 which seems to mean 5 CR block + 1 CURRENT block.

UKJA@ukja102> @para _db_block_max_cr_dba
NAME                           VALUE                SES_MODIFI SYS_MODIFI
------------------------------ -------------------- ---------- ----------
DESCRIPTION
--------------------------------------------------------------------------------
_db_block_max_cr_dba           6                    false      false
Maximum Allowed Number of CR buffers per dba



Oracle needs to make a balance here. More CR blocks would enable logical reads without rollback but waste the buffer cache.


4. We need only 1 logical reads without rollback.

UKJA@ukja102> col rid new_value v_rid
UKJA@ukja102> select rowid as rid from t1;

RID
------------------
AAAUa4AAKAAAIqOAAA

UKJA@ukja102> select * from t1 where rowid = '&v_rid';

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
       6591  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
UKJA@ukja102> @bh &file_no &blk_no
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL
---------- ---------- ---------- ---------- ---------- ---------- ----------
CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- --------------------
        10      35470          1 xcur                0          0          0
         0          0 T1





5. But 2 logical reads with rollback. This is the overhead of Oracle’s consistent implementation.

UKJA@ukja102> -- Session #1. make DML statements on the block.
We need at least 6 consecutive DMLs to make a full rotation of CR blocks. 
This would force Oracle to read undo block to build CR block for select query of session #2.

UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> update t1 set c2 = 'y', c3 = 'y', c4 = 'y' where c1 = 1;

1 row updated.

UKJA@ukja102> commit;

Commit complete.

UKJA@ukja102> -- Session #2 (This query should be started before updates of session #1 
and should not end until the update completes).

UKJA@ukja102> select * from t1 where rowid = '&v_rid';

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       6591  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


UKJA@ukja102> -- Which undo is being read? 
Note that Oracle read (file#=2,block#2966) undo block to rollback successfully.

UKJA@ukja102> @bh &file_no &blk_no
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL
---------- ---------- ---------- ---------- ---------- ---------- ----------
CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- --------------------
        10      35470          1 cr          599680154       2015          2
      2966       6468 T1

        10      35470          1 xcur                0          0          0
         0          0 T1

        10      35470          1 cr          599680163       2015          0
         0          0 T1

        10      35470          1 cr          599680161       2015          0
         0          0 T1

        10      35470          1 cr          599680159       2015          0
         0          0 T1

        10      35470          1 cr          599680157       2015          0
         0          0 T1


6 rows selected.



The overhead of rollback in consistent read is negligible at most time, but under specific situation, we get an unbelievable logical reads on undo blocks and/or ORA-1555 error.



6. 10200 event(consistent read buffer status) shows another interesting(but not that useful) information. When rollback is being activated by consistent read, Oracle seems to print all the related CR block information.

-- without rollback
Consistent read started for block 7 : 02808a8e
  env: (scn: 0x07df.23be65f7  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
Consistent read finished for block 7 : 2808a8e
Consistent read finished for block 7 : 2808a8e

-- with rollback
Consistent read started for block 7 : 02808a8e
  env: (scn: 0x07df.23be659f  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  03C44148  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
CR exa ret 9 on:  10BE8D20  scn: 0x07df.23be65ab  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0x07df.23be65ab  sfl: 0
CR exa ret 9 on:  107EFA70  scn: 0x07df.23be65a9  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0x07df.23be65a9  sfl: 0
CR exa ret 9 on:  107EE3C0  scn: 0x07df.23be65a7  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0x07df.23be65a7  sfl: 0
CR exa ret 9 on:  107F3D20  scn: 0x07df.23be65a5  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0x07df.23be65a5  sfl: 0
CR exa ret 9 on:  107F8810  scn: 0x07df.23be65a3  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0x07df.23be65a3  sfl: 0
Consistent read finished for block 7 : 2808a8e
Consistent read finished for block 7 : 2808a8e





Well, I meant simple test case(which is actually simple), but the result was too lengthy post to be blogged. :(

As mentioned above, I just wanted to draw a simplified picture of how Oracle’s consistent read works. This basic demonstration would help you to understand how to interpret the pros and cons of consistent read in Oracle.

Advertisements

Written by Dion Cho

April 18, 2009 at 3:20 pm