Dion Cho – Oracle Performance Storyteller

We are natural born scientists

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.

Written by Dion Cho

April 18, 2009 at 3:20 pm

6 Responses

Subscribe to comments with RSS.

  1. Very nice Dion, this is what I am studying at the moment.I shall post the results testing the same over my system.

    Regards
    Aman…..

    Aman....

    April 18, 2009 at 8:50 pm

  2. Just a quick doubt Dion, isn’t the state of the CR block is shown as STATE=3?

    Regards
    Aman….

    Aman....

    April 18, 2009 at 9:02 pm

  3. OOPS , so sorry, its the class=1 not the state, please ignore!

    regards

    Aman....

    April 18, 2009 at 9:03 pm

  4. Hi Dion

    I have looked this in the past because of perfomance problemas with batch jobs.

    Say you have one session querying and one session modifying data like your example, it is not that bad compared to higer number of modifying session (number of consistent gets increases exponentially for the querying session) so it looks that for some problematic jobs it is actually worse parallelize them!

    I remember in old Oracle 8 days parallel query used rowid to partition the data into number of slaves, do you think is to avoid the problem I have described?

    LSC

    June 13, 2009 at 11:49 pm

  5. Thank you for the scenario.

    But why oracle keeps more than one CS block ?
    1 looks enough to have a consistent view of the block!

    Thank you

    Auge

    August 16, 2009 at 12:20 pm

    • Auge.
      You might mean CR block not CS block.

      The main reason might be to reduce the rollback. Oracle needs to rollback the current image from undo without the cr block in the buffer cache.

      Dion Cho

      August 18, 2009 at 6:27 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: