loading
hello dummmy text

Oracle Database BUFFER CACHE is one of the core important architectural memory component which holds the copies of data blocks read from datafiles. In my journey of Oracle DBA this memory component played major role in handling Performance Tuning issues. In this Blog, I will demonstrate a case study and analyze the behavior of BUFFER CACHE which would probably help you in understanding the architecture better. Similar to “Can a data BLOCK accommodate rows of distinct tables?” discussion, we will pick up a way to execute the concepts and verify them in reality.

To begin with, there are two types of list that BUFFER CACHE will have at any given point of time. One: LRU list holds free buffers and pinned buffers. Two: Write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk.

We will be picking up a simple two step process to see if BUFFER CACHE floods when there are active transactions on a BLOCK and active reads on the same BLOCK.

Step1: Update a row from data block from session 1

Step2: Read the same block from the session 2

The same set of two steps will be recursive on different rows from the same data BLOCK. Between Step1 and Step2 we will keep monitoring the BUFFER CACHE statistics to answer this articles title.

How does BUFFER CACHE handles UPDATE and SELECT on a BLOCK in #Oracle #Database? Click To Tweet

Exercise:

  1. We will use DEPT table from SCOTT schema to run the test case.
  2. Making sure that BUFFER CACHE has no BLOCKS from DEPT
— Block# 135 is the block no of DEPT table data.
select BLOCK#, status, dirty from v$bh where BLOCK#=135;
  1. From session 1, run UPDATE on one of the rows from DEPT
update dept set LOC=’SRILANKA’ where deptno=10;
  1. Check the block status in BUFFER CACHE.
select BLOCK#, status, dirty from v$bh where BLOCK#=135;
BLOCK#      status      dirty
135         xcur        N

Observation: Status of this BLOCK in the BUFFER CACHE is XCUR – Exclusive Current. It is not DIRTY.

  1. From session 2, run SELECT on the complete DEPT
set autotrace on

select * from scott.dept;

Statistics

———————————————————-

2  recursive calls

0  db block gets

11  consistent gets

   6  physical reads

108  redo size

688  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)

5  rows processed

Observation: There are physical reads even after the block is in BUFFER CACHE for update.

  1. Now re-check the block status in BUFFER CACHE.
select BLOCK#, status, dirty from v$bh where BLOCK#=135;

BLOCK#      status      dirty

135         xcur        Y

135         cr          N

Observation: There are two copies of same BLOCK in the memory, One with XCUR status and marked as DIRTY and two with CR for Consistent Read operation requested by session 2.

  1. To verify if the BUFFER CACHE floods when you keep running the steps 3 to 6, let us make a note of the current number of memory used by DEPT Note that still earlier transaction is ACTIVE.
SELECT object_name, object_type type, COUNT(*) GETS_COUNT

FROM v$bh

JOIN dba_objects ON object_id = objd and object_name=’DEPT’

GROUP BY object_name, object_type

ORDER BY GETS_COUNT DESC;

OBJECT_NAME       TYPE        GETS_COUNT

DEPT              TABLE       7
  1. Re-running steps 3 to 5 for rest of the 4 rows in the table. Keeping all the earlier transactions ACTIVE.
  2. Now re-check the block status in BUFFER CACHE and the size DEPT table occupied.
select BLOCK#, status, dirty from v$bh where BLOCK#=135;

BLOCK#      status      dirty

135         xcur        Y

135         cr          N

135         cr          N

135         cr          N

135         cr          N

135         cr          N

SELECT object_name, object_type type, COUNT(*) GETS_COUNT

FROM v$bh

JOIN dba_objects ON object_id = objd and object_name=’DEPT’

GROUP BY object_name, object_type

ORDER BY GETS_COUNT DESC;

OBJECT_NAME       TYPE        GETS_COUNT

DEPT              TABLE       14

Observation: For every READ operation after every new UPDATE operation, there is a new BLOCK in the BUFFER CACHE for Consistent Read.

  1. Now let us commit the transactions and monitor the BUFFER CACHE.
SQL> commit;

Commit complete.

— From session 2:

set autotrace on

select * from scott.dept;

Statistics

———————————————————-

0  recursive calls

0  db block gets

7  consistent gets

0  physical reads

0  redo size

696  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)

5  rows processed

Observation: Consistent gets went down to 7 from 14.

Will huge Consistent Reads floods #BUFFER #CACHE in #Oracle #Database? Click To Tweet

Conclusion:

  • When every new transaction on the data BLOCK is followed by SELECT, BUFFER CACHE creates a Consistent Read copy.
  • These copies are created by Oracle Database as the SCN number has been changed on the BLOCK at the time of issuing SELECT statement.
  • Such operations definitely floods the BUFFER CACHE.
  • Oracle by some means hopefully maintains to clean up these buffers by LRU, MRU algorithms.
  • But, when system is very busy with resource issues then this BUFFER CHAINS are really a problem.

Few TITBITs:

  • What happens when you flush BUFFER CACHE, when a transaction is ACTIVE?
  • Will your FLUSH statement hangs?
  • Will your blocks in BUFFER CACHE wipes off?

I love to see your comments!

Feel free to give additional information in the comments, that I can validate and include in blog posts or update this as well.

10 Comment

  1. Shamim

    After some time block became dirty, does it mean that now roll back will not work for user who is updating?

    1. Pawan Kumar Yaddanapudi

      Hi Shamim,

      Rollback operation is still applicable on the data block. And the interesting fact is: buffer is marked dirty even before transaction is completed. This will be answered in my next blog soon.

      1. Amaan

        why does buffer is marked dirty even before the transaction has yet not been completed….

        1. Pawan Kumar Yaddanapudi

          It is marked dirty, because there is another copy of the same block in the buffer now because of the read operation performed by second session. But still, transaction will not be updated to datafile until it is committed !!!

          1. Rajnikant Yadav

            Dear Pawan,
            when you updated the data block with update statement, the block should become dirty as per oracle docs, and the when second session is selecting previous data it should get it from undo block i.e previous image. my question is why the block is not marked dirty , after update statement?

          2. Pawan Kumar Yaddanapudi

            Block will have to become dirty but its asynchronous, it will not perform as soon as the transaction is completed. Yet, the transaction has to be committed or rollback ed to move the block from one state to other.

  2. Pavan Kumar N

    What happens when you flush BUFFER CACHE, when a transaction is ACTIVE?
    We don’t loose the buffers which are XCUR – need for transaction. Since its doesn’ t make sense at all to loose the buffer when transaction is active. As client session would need to re-enter the details til the last save point.

    Will your FLUSH statement hangs?
    It wont hang, it will clean-up buffers – based on LRU algorithm, which aren’t not required. Probably if you fetch and compare v$bh you will find the difference in reduction in CR copies which aren’t not under active transactions ( try running select query long running query) and in parallel flush the buffer cache you will have your answer for this query which blocks will survive (CR copies).

    Will your blocks in BUFFER CACHE wipes off?
    Test above case , it answers your query.

    1. Pawan Kumar Yaddanapudi

      Thanks Pavan !!! Appreciate your answers.
      Those questions were for the readers like you to try.
      Good job.

  3. Adarsh

    Why can’t oracle create a consistent read copy from UNDO. Why does it have to read the block from the disk ?

    1. Pawan Kumar Yaddanapudi

      Read copy from UNDO is to perform I/O operation and yes by accessing disk.

Write a Reply or Comment

Your email address will not be published. Required fields are marked *

Knowledge That Can Always Keep Your Inbox Informed