DB Smart Flash Cache in Oracle 11g

59519751

http://dbaora.com/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
　　jpg1
　　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>
  

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>
  

　　Performance improvements

　　Oracle conducted interesting test for a OLTP database 70GB>　　jpg2

　　Following picture shows improvement in transaction response time versus DB smart cache>　　jpg3
　　Example
　　I simulate SSD disk by creation ramdisk – disk based in memory using following steps:
　　1. create directory to mount ramdisk and change owner to oracle and group dba
[root@oel5 /]mkdir /ramdisk　　
[root@oel5 /]chown oracle:dba -R /ramdisk
　　2. mount ramdisk and check it
[root@oel5 /]# mount -t tmpfs none /ramdisk -o size=256m　　
[root@oel5 /]# mount | grep ramdisk
　　
none on /ramdisk type tmpfs (rw,size=256m)
　　3. set parameters for database and restart it as user oracle
SQL> alter system set db_flash_cache_file='/ramdisk/ram.dbf'　　
SQL> scope=spfile;
　　

　　
System altered.
　　

　　
SQL> alter system set db_flash_cache_size=200M scope=spfile;
　　

　　
System altered.
　　

　　
SQL> startup force
　　
ORACLE instance started.
　　

　　
Total System Global Area  835104768 bytes
　　
Fixed Size                  2232960 bytes
　　
Variable Size             507514240 bytes
　　
Database Buffers          322961408 bytes
　　
Redo Buffers                2396160 bytes
　　
Database mounted.
　　
Database opened.
　　
SQL> show parameter flash_cache
　　

　　
NAME                    TYPE        VALUE
　　
----------------------- ----------- ------------------------------
　　
db_flash_cache_file     string      /ramdisk/ram.dbf
　　
db_flash_cache_size     big integer 200M
　　4. Check new file exists in /ramdisk
[root@oel5 ramdisk]# ll　　
total 8
　　
-rw-r----- 1 oracle asmadmin 209715200 Feb 24 22:54 ram.dbf
　　5. Let’s create tables with flash_cache keep reference in storage clause so Oracle will try to keep the blocks in DB smart cache as long as possible.
create table test_tbl1　　
(id number,
　　
id1 number,
　　
id2 number)
　　
storage(flash_cache keep);
　　

　　
begin
　　
  for i in 1..1000000
　　
  loop
　　
    insert into test_tbl1 values(i, i, i);
　　
  end loop;
　　
  commit;
　　
end;
　　
/
　　6. Eventually after some time you can see some data in flash cache – v$bh view.
select status, count(*) from v$bh　　
group by status;
　　

　　
STATUS       COUNT(*)
　　
---------- ----------
　　
xcur            36915
　　
flashcur        25583
　　
cr                 13
　　7. If you clean buffer cache as well db smart flash cache is purged
alter system flush buffer_cache;　　
system FLUSH altered.
　　

　　
STATUS       COUNT(*)
　　
---------- ----------
　　
xcur              520
　　
free            36411
　　Have a fun
　　Tomasz