Consistent Reads and Multiversioning

Came up with this demo for a class last week and I think that it’s useful.

The demo illustrates one of the most important foundational concepts in an Oracle database: how Oracle provides isolation and consistency. For my favorite technical illustration of undo and redo check out slide #22 of the Redo Internals presentation on Julian Dyke’s website. (A picture is worth… you know!) For a very good description of isolation and consistency read Tom Kyte’s column in Oracle Magazine from Nov 2005.

The database engine must guarantee that the results of your queries are consistent. (The “C” in the “ACID” transactional database model.) Some SQL statements take a long time to run. It is also possible to put a transaction into a read-only or serializable mode. In both of these situations, regardless of how many transaction commit and how much data changes, your statement or transaction must always see the database as if everything happened at the very instant you started it. You need to ignore committed data. If oracle is constantly changing the data in tables and committing this data then how can your statement see the old data? The key is undo (or rollback segments) and multiversioning.

Now I haven’t worked for Oracle Corporation or seen their source code. But logically (in order to avoid dirty reads), before it ever reads any data from a block, Oracle must always check at least two things from the block header: the SCN and the ITL. The ITL is a list of transactions active on the block and the SCN is updated any time a transaction that updated the block COMMITs. By examining the ITL Oracle can determine if there are any uncommitted changes on the block. By examining the SCN Oracle can determine if any transactions have committed that need to be “ignored”.

If either of these conditions is true then Oracle must create a past image of the block and read that, rather than the current image of the block. Oracle allocates a new block from memory, copies the current block into it, and rolls back the new block using the undo records. It can then use this past image for the query.

I’m going to demonstrate the block copy and rollback in two different ways. First, by directly observing the buffers in memory. You can see new “past image” buffers being created on the fly to satisfy queries. Second, I am going to use Tom Kyte’s runstats package to show the statistics that reflect these in-memory rollback operations happening. The cool thing about this demonstration is that you will see how the exact same SQL statement executed twice behaves differently – because the second time it can just reuse the blocks that were already rolled back. You will also see how statistics tell you how much rollback is happening in the background.

Consistent Reads in Action – Buffers

First we’ll create and populate a test table then write a query to show the table’s blocks in memory. (Notes: First, you will need DBA access to query the V$BH view which shows buffer headers. Second, I performed this test on Oracle 10.2.0.1 – your exact results may vary slightly depending on your version.)

SESSION 1
============
SQL> create table test3 (
  2  n number,
  3  data varchar2(4000) default rpad('*',3999,'*'))
  4  pctfree 99 pctused 1;

Table created.

SQL> insert into test3(n)
  2  select 1 from all_objects where rownum<=100;

100 rows created.

SQL> commit;

Commit complete.

SQL> create table test3rows as
  2  select rowid r from test3;

Table created.

SQL> select object_id from obj where object_name='TEST3';

 OBJECT_ID
----------
     52761

SQL> select class#, status, dirty, count(*) from v$bh
  2  where objd=52761 and status<>'free'
  3  group by class#, status, dirty
  4  order by class#, status;

    CLASS# STATUS  D   COUNT(*)
---------- ------- - ----------
         1 xcur    Y        103
         4 xcur    Y          1
         8 xcur    Y          7
         9 xcur    Y          1

After creating and populating the table you can see that I have just over 100 data blocks (class 1) in memory. All of these are current images of the blocks.

Now in order to easily observe the oracle copying a block and rolling it back, I’m going to start a READ ONLY transaction in this session. This transaction will always see the database as it was when I started the transaction. After that I will modify the data from a second session. Then when I query the data from the first session, Oracle will copy and roll back the changes.

SESSION 1
============
SQL> set transaction read only;

Transaction set.

                              SESSION 2
                              ============
                              SQL> update test3 set n=2
                                2  where rowid in (
                                3    select r from test3rows
                                4  );

                              100 rows updated.

                              SQL> commit;

                              Commit complete.

SESSION 1
============
SQL> select class#, status, dirty, count(*) from v$bh
  2  where objd=52761 and status<>'free'
  3  group by class#, status, dirty
  4  order by class#, status;

    CLASS# STATUS  D   COUNT(*)
---------- ------- - ----------
         1 xcur    N          3
         1 xcur    Y        100
         4 xcur    Y          1
         8 xcur    N          7
         9 xcur    N          1

SQL> select max(n) from test3;

    MAX(N)
----------
         1

SQL> select class#, status, dirty, count(*) from v$bh
  2  where objd=52761 and status<>'free'
  3  group by class#, status, dirty
  4  order by class#, status;

    CLASS# STATUS  D   COUNT(*)
---------- ------- - ----------
         1 cr      N        100
         1 xcur    N          3
         1 xcur    Y        100
         4 xcur    Y          1
         8 xcur    N          7
         9 xcur    N          1

Notice when the Past Image (or Consistent Read) blocks were created: they were triggered by the SELECT statement in session 1. There is one other observation that I can make from this: my 800k table is now using 1600k of memory! Oracle’s multi-versioning implementation can consume significant additional memory! Maybe one statistic to keep an eye on is what percentage if your buffer cache is being used for CR blocks.

Consistent Reads in Action – Statistics

Now lets take a look at consistent reads from the perspective of system statistics. First we will start a new transaction in our first session. Then we will perform the same update again, but to change things up we won’t commit this time – to demonstrate that uncommitted transactions also cause rollback (in fact this is probably more common). Finally we will run the same select statement twice but use Tom Kyte’s runstats harness to measure the difference.

SESSION 1
============
SQL> commit;

Commit complete.

SQL> set transaction read only;

Transaction set.

                              SESSION 2
                              ============
                              SQL> update test3 set n=3
                                2  where rowid in (
                                3    select r from test3rows
                                4  );

                              100 rows updated.

SESSION 1
============
SQL> select class#, status, dirty, count(*) from v$bh
  2  where objd=52761 and status<>'free'
  3  group by class#, status, dirty
  4  order by class#, status;

    CLASS# STATUS  D   COUNT(*)
---------- ------- - ----------
         1 cr      N        100
         1 xcur    N          3
         1 xcur    Y        100
         4 xcur    N          1
         8 xcur    N          7
         9 xcur    N          1

SQL> set serveroutput on size 99999;
SQL> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

SQL> select count(n) from test3;

  COUNT(N)
----------
       100

SQL> exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed.

SQL> select count(n) from test3;

  COUNT(N)
----------
       100

SQL> exec runstats_pkg.rs_stop(85)
Name                                            Run1        Run2
STAT...consistent gets                           212         125
STAT...consistent gets from cache                212         125
STAT...consistent changes                        117          22
LATCH.cache buffers lru chain                    100           1
STAT...free buffer requested                     100           1
STAT...CR blocks created                         100           0
STAT...rollbacks only - consistent read          100           0
LATCH.In memory undo latch                       100           0
STAT...data blocks consistent reads - un         100           0
STAT...calls to kcmgas                           100           0
LATCH.undo global data                           207           4
LATCH.cache buffers chains                       814         316
        
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
1,624         620      -1,004    261.94%

PL/SQL procedure successfully completed.

SQL> select class#, status, dirty, count(*) from v$bh
  2  where objd=52761 and status<>'free'
  3  group by class#, status, dirty
  4  order by class#, status;

    CLASS# STATUS  D   COUNT(*)
---------- ------- - ----------
         1 cr      N        200
         1 xcur    N          3
         1 xcur    Y        100
         4 xcur    N          1
         8 xcur    N          7
         9 xcur    N          1

There are a few observations to make here. First, we ran the exact same SQL statement twice – but the statistics are quite different. The rollback operations required a number of latches. Second, the previously used CR blocks are still in memory – so my table is now using 2400k of memory. (Of course in a live system many of the blocks would probably get aged out.) Even more memory being used by my table which itself is only 800k!

Consistent Reads in Action – Updates

Let’s look at one last quick situation. I have been using the rowid’s to update the table; what if I were to update the table without them?

SESSION 1
============
SQL> commit;

Commit complete.

SQL> set transaction read only;

Transaction set.

SQL> select class#, status, dirty, count(*) from v$bh
  2  where objd=52761 and status<>'free'
  3  group by class#, status, dirty
  4  order by class#, status;

    CLASS# STATUS  D   COUNT(*)
---------- ------- - ----------
         1 cr      N        200
         1 xcur    N        103
         4 xcur    N          1
         8 xcur    N          7
         9 xcur    N          1

                              SESSION 2
                              ============
                              SQL> update test3 set n=n+1;

                              100 rows updated.

SESSION 1
============
SQL> select class#, status, dirty, count(*) from v$bh
  2  where objd=52761 and status<>'free'
  3  group by class#, status, dirty
  4  order by class#, status;

    CLASS# STATUS  D   COUNT(*)
---------- ------- - ----------
         1 cr      N        300
         1 xcur    N          3
         1 xcur    Y        100
         4 xcur    N          1
         8 xcur    N          7
         9 xcur    N          1

Wait, wait, wait!!! Now why did that happen? I never issued a SELECT statement but Oracle created 100 more CR blocks! Now my 800k table is using 3200k of memory! You may have guessed already; the update statement itself needed a consistent image of each block in order to calculate the new values for the table. This is also the reason that “INSERT INTO T SELECT * FROM T” does not result in a recursive loop.

The point is that Oracle uses consistent reads all the time – and you may not realize it! As a DBA or developer you need to know what the performance implications are of queries on tables with high activity – and what the explanation is for higher-than-expected I/O on these tables.