11gR2 : Oracle flash cache (exadata, non exadata also works), A secondary memory pool for your database buffer cache

In 11gR2, Oracle has introduced a cache called flash cache which is a secondary cache of the buffer cache. This flash cache is resides in L2 Cache of the system  (RAM) and can be very fast compare to the disk.  The SSD disks which gives you the benefit of high speed disks , the same benefit with flash cache. To read / write quicker in the memory and send it later to disk (i.e magnetic disk) as and when the dbwr is free.

So with Exadata (as its hardware consists of flash cache memory) we can leverage the same flash cache a secondary db buffer cache, where in with one patch below you can simulate the same using the cheap pen drive for testing purposes.

Before proceed further, with flash cache how does the block traverse when it has flash cache (the diagram below has been grabbed from Guy harrison’s blog to have better understanding of this process, Thank to Guy).

So the lifecycle of a block looks something like this:

1. The Oracle server process reads a file from disk and adds it to the buffer cache
2. If a session wants that block later and it's still in the buffer cache, they can read it from the buffer cache
3. Before the block leaves the buffer cache the DBWR will write it to the flash cache (if the DBWR is not too busy)
4. If a session want a block later and it's still in the flash cache, then they will read it from the flash cache (and presumably place it back in the buffer cache)
5. If the block is modified, the DBWR will eventually write it back to the disk. (Q: What happens to any unmodified copies of that block in the flash cache?)

Verify the flash cache usage in my database, Flash cache column has null values

  SELECT   owner || '.' || object_name object,
           SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 END) flash_blocks,
           SUM (CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,
           count(*) total_blocks  
    FROM       v$bh b
           JOIN
               dba_objects
           ON (objd = object_id)
GROUP BY   owner, object_name
order by 4 desc 


SQL> /

OBJECT                      FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
--------------------------- ------------ ------------ ------------
SYS.C_OBJ#                                      1330         1330
SYS.OBJ$                                         888          888
SYS.I_OBJ2                                       740          740
SYS.IDL_UB1$                                     349          349
SYS.C_FILE#_BLOCK#                               263          263
SYS.IDL_UB2$                                     177          177
SYS.TRIGGER$                                     104          104
SYS.C_OBJ#_INTCOL#                               100          100
SYS.I_OBJ1                                        73           73
SYS.HIST_HEAD$                                    50           50
SYS.I_DEPENDENCY1                                 48           48

Demo:-

1) Apply the patch, 9352237 on top of 11.2.0.1

2) Create a directory for your pendrive to mount

mkdir /mnt/usbflash

3) Add a line in fstab

/dev/sdc1 /mnt/usbflash vfat noauto,users,rw,umask=0 0 0

The device name /dev/sdc1 can be differ according to your environment

4) Set the database parameters

alter system set db_flash_cache_file=’/mnt/usbflash/oraflashcache.dat;

alter system set db_flash_cache_size=1000m;

Note: the flash cache file should be on flash drive (pendrive) only.

5) Restart the DB

6) Do some transactions or let get the db heated. (just after sometime I mean)

7) Verify that flash cache is used.

  SELECT   owner || '.' || object_name object,
           SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 END) flash_blocks,
           SUM (CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,
           count(*) total_blocks  
    FROM       v$bh b
           JOIN
               dba_objects
           ON (objd = object_id)
GROUP BY   owner, object_name
order by 4 desc 


SQL> /

OBJECT                      FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
--------------------------- ------------ ------------ ------------
SYS.C_OBJ#                        1010           320         1330
SYS.OBJ$                           777          1111         1888
SYS.I_OBJ2                         295           740         1035
SYS.IDL_UB1$                       780           349         1129
SYS.C_FILE#_BLOCK#                 540           263          803

The flash cache can  be cool feature for an OLTP systems where you have many contention on buffers and the aged out happens quickly.  But as Guy’s said the write performance can be poorer compare to reads.

-Thanks

Geek DBA