Archive

Posts Tagged ‘X$BH’

x$bh and consistency

March 6, 2015 2 comments

Oracle caches the data blocks in buffer cache in various modes depends on the block usage.  As per the Oracle documentation it can CR (Consistent mode – reads), XCUR (Current mode – updates), FREE etc.   I understand and other heard saying – whenever a block READs into memory will be in CR mode while if the block is fetching for UPDATE it will be in XCUR mode so that sessions can apply the transactions under the exclusive lock mode.  And the working set of the buffer cache is divided into multiple working set and I believe each working set has a cold and hot region based on the touch count algorithm.  Arming with these details, I did the following test and the results were little confusing – not just little!

My one row – column table TEST is as follws –

<pre>SQL> desc TEST

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER

SQL> select * from test;

        C1
----------
         6

Then I flush the buffer cache, to clear the TEST table blocks.  Also made sure there was no transactions active this point.

<pre>SQL> alter system flush buffer_cache;
System altered.

SQL> col object_name for a25
SQL> set linesize 132
SQL> select
  2  indx
  3  ,o.object_name
  4  ,decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec'
  5  ,6,'irec',7,'write',8,'pi') state
  6  , dbarfil
  7  , dbablk
  8  , ba
  9  ,tch
 10  from x$bh b , dba_objects o
 11  where b.obj = o.data_object_id
 12  and o.object_name = 'TEST'
 13  and state > 0;

no rows selected

SQL> select count(*) from v$transaction;


  COUNT(*)

----------

         0

 

Test 1:

I read one row to the memory using ROWID to enable single block read from the TEST table.  Since I am reading – I was expecting a CR read.

<pre>
SQL> conn abc/abc

Connected.

SQL> select * from test where rowid='AAAUxpAABAAA0MJAAA';

        C1

----------

         5

But to my surprise, the block under XCUR mode and the touch count was 1.

<pre>
SQL> select

indx

  2    3  ,o.object_name

  4  ,decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec'

  5  ,6,'irec',7,'write',8,'pi') state

  6  , dbarfil

  7  , dbablk

  8  , ba

  9  ,tch

 10  from x$bh b , dba_objects o

 11  where b.obj = o.data_object_id

 12  and o.object_name = 'TEST'

 13  and state > 0;



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43737 TEST                      xcur           1     213769 00000003A7DFA000          1



 

I repeated the read one more time, and the same XCUR block increased the touch count to 2.

 


SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43722 TEST                      xcur           1     213769 00000003A7DFA000          2



 

From the first session I have executed the SQL 4 more times and the count reached 6.

 

Note:  I did this with a 5 second interval – I will explain that in my next entry.

 


SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43722 TEST                      xcur           1     213769 00000003A7DFA000          6

 

I read from multiple sessions and found the touch count was increasing with the XCUR state.

 

Test 2:

 

From the first session, did an UPDATE to my TEST table from ABC user.

 


SQL> update test set c1=7 where rowid='AAAUxpAABAAA0MJAAA';



1 row updated.



Oracle applied that UDPATE to the same XCUR block and increased the touch count to 7



SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43722 TEST                      xcur           1     213769 00000003A7DFA000          7



 

From the second ABC session I read the row and that created a CR block and more interestingly the touch count did not increase.

 


SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43670 TEST                      cr             1     213769 00000003BBBCC000          1

     43671 TEST                      xcur           1     213769 00000003A7DFA000          7

 

I read 5 more times from the second session and that created 5 more CR blocks for the XCUR block.

 




SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43665 TEST                      cr             1     213769 00000003B4C5A000          1

     43666 TEST                      cr             1     213769 00000003ABCE0000          1

     43667 TEST                      cr             1     213769 00000003B6CD8000          1

     43668 TEST                      cr             1     213769 00000003AC36A000          1

     43669 TEST                      cr             1     213769 00000003A5030000          1

     43670 TEST                      cr             1     213769 00000003BBBCC000          1

     43671 TEST                      xcur           1     213769 00000003A7DFA000          7



7 rows selected.

 

When I read the 6th time, the count of CR blocks did not increase and that was expected.  At any point in time, a single block can have maximum of 7 versions in the buffer cache.  Any subsequent reads will use one of the existing CR block to build the new CR block.  The more interesting finding was, the touch count was never increased during a CR block build thus the access was unaccounted.  Each time Oracle build a new CR block and that block was send to the user, hence the original XCUR block touch count was not increased.  I am not sure why Oracle showing this discrimination to XCUR blocks which is having an active transaction.

 

Then I did execute the READ from the session where the transaction was active – and that increased the touch count again – to 8!

 


SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43665 TEST                      cr             1     213769 00000003B4C5A000          1

     43666 TEST                      cr             1     213769 00000003ABCE0000          1

     43667 TEST                      cr             1     213769 00000003B6CD8000          1

     43668 TEST                      cr             1     213769 00000003AC36A000          1

     43669 TEST                      cr             1     213769 00000003A5030000          1

     43670 TEST                      xcur           1     213769 00000003A7DFA000          8



6 rows selected.

 

I will not give much importance to reduction of 1 CR block as the temporary CR blocks can re-use any time.

 

So, my assumptions are –

  • When Oracle reads a block in to buffer cache first time it will be in XCUR mode not in CR mode
  • I consider this XCUR list as Master List of blocks and all the updates will go the master XCUR list only. Master List can be read directly if no transactions are active.
  • Under no transactions – any READ will reuse the XCUR block and increase the touch count
  • Too many consistent reads leads to CBC Latch waits as everyone will be contending on the XCUR block list
  • A transaction will modify the XCUR block and will increase the touch count
  • Any other session reading a XCUR block will create fresh CR block and rollback the transactions. There are can be maximum of 7 versions of a block can exist.  During the CR block access, the touch count will not increase
  • Transaction session read directly from the XCUR block without creating a CR block and the touch count will increase.
Advertisements