DB Smart Flash Cache in Oracle 11g


In case you don’t have budget to buy Exadata you can still buy huge number of flash disks and put on them part of your database. But what should be stored on flash disks(very fast) and what on magnetic disks(very slow) ?

It’s not your businesses to know let decide database :)

Introduction

DB Smart Flash Cache is new extension for buffer cache area. This extra area should be defined on solid state disks (SSD) and has following features:

  • make performance improvements at moderate cost(cheaper than DRAM)

  • low latency compared to magnetic disks

  • higher throughput compared to magnetic disks

  • easy to setup
  • easy to control
  • can be used for RAC cache fusion keeps consistency
  • direct I/O bypasses buffer cache so as well bypasses DB smart flash cache
  • can cache only clean blocks from buffer cache
  • flash cache is not auto-tuned
  • only blocks from standard buffer pool are cached in DB smart flash cache

Oracle recommends:

  • flash disks should have comparable read IOPs and IOPs write

  • this new layer should be at least 2-10 times bigger than buffer cache in DRAM

  • mainly for OLTP systems

Architecture

if a oracle server process needs to read a block from database at first it must read it from magnetic disk(physical read). Then the block is stored in buffer cache memory and added to standard “LRU chain” list.

When “in memory buffers area” is getting full Oracle must decide which blocks needs to be removed from cache. If you have DB Smart Flash Cache enabled “clean” blocks are written to “Flash cache” by DBWR process so next time they can be read into memory from Flash Cache and improve your performance.

NOTE: “Dirty” blocks are never stored in Flash Cache

List of blocks cached in DB smart flash cache are stored in buffer cache area on two dedicated flash “LRU lists” depending on object attribute FLASH_CACHE:

  • DEFAULT – standard last recently used algorithm decides how long such blocks are cached in flash cache. It’s default value assigned to each object in database.

  • KEEP – such blocks are not removed from flash cache as long as the flash cache is large enough

alter|create table|index objectname 
storage 
(
   buffer_pool { keep | recycle | default }
   flash_cache { keep | none    | default }
);

NONE value for FLASH_CACHE is blocking flash caching for a given object.

Statistics

All I/O operations from DB smart flash cache are counted as physical I/O however Oracle also collects such informations in new columns.

V$SQL - OPTIMIZED_PHY_READ_REQUESTS
V$SQLAREA - OPTIMIZED_PHY_READ_REQUESTS
V$FILESTAT - OPTIMIZED_PHYBLKRD
select name from v$statname where name like 'physical%optimized%';

NAME                                                           
----------------------------------------------------------------
physical read requests optimized                                 
physical read total bytes optimized

You can see such stats in V$SESSTAT and V$SYSSTAT

Setup

Two parameters must be set on database level to turn on DB smart flash cache:

  • DB_FLASH_CACHE_FILE – defines (OS disk path or ASM disk group) and file name to store this data
  • DB_FLASH_CACHE_SIZE – defines size of the flash cache
DB_FLASH_CACHE_FILE='/os path/flash_cache_file.dbf'
DB_FLASH_CACHE_FILE='+FLASH_DISK_GROUP/flash_cache_file.dbf'
DB_FLASH_CACHE_SIZE=200m

After setting both parameters you need to restart database.

DB_FLASH_CACHE_FILE

  • can’t be shared between many databases or instances

DB_FLASH_CACHE_SIZE

  • can’t be dynamically resized

  •  can be set to 0 to disable DB smart flash cache

  • can be set to original size to re-enable DB smart flash cache

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s