Will huge Consistent Reads floods BUFFER CACHE?

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.

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;

3. From session 1, run UPDATE on one of the rows from DEPT

update dept set LOC=’SRILANKA’ where deptno=10;

4. 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.

5. 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.

6. 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.

7. 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

8. Re-running steps 3 to 5 for rest of the 4 rows in the table. Keeping all the earlier transactions ACTIVE.
9. 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.

10. 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.

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?