Smart
flash_cache in 11gR2 and > ( 12c ) Version supports on OEL or Solaris only 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
Performance improvements
Oracle conducted interesting test for a OLTP
database 70GB size with 8GB SGA. From below picture you can see improvements
for Transactions versus size of DB smart cache size.
Following picture shows improvement in transaction
response time versus DB smart cache size
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=sp;
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
ERROR:-
I do all steps of your manual, but
after ‘startup force’ I have an error:
SQL>
startup force
ORA-00439: feature not enabled: Server Flash
Cache
What am I doing wrong?
My configuration:
Oracle Linux Server release 6.4
2.6.39-400.24.1.el6uek.x86_64
Oracle Database 11g 11.2.0.3.0
Patch
12949806: FLASH CACHE CHECK IS AGAINST ENTERPRISE-RELEASE
Now it works!
------FOR
TESTING PURPOSE------
# fdisk -l /dev/sdc
Disk /dev/sdc: 24.5 GB, 24575868928 bytes
255 heads, 63 sectors/track, 2987 cylinders
Units = cylinders of 16065 * 512 = 8225280
bytes
Disk /dev/sdc doesn't contain a valid
partition table
# chmod 777 /dev/sdc
set
Oracle initialization parameters:
$ sqlplus / as sysdba
SQL> alter system set
db_flash_cache_file='/dev/sdc' scope=spfile;
System altered.
SQL> alter system set
db_flash_cache_size=10G scope=spfile;
System altered.
Stop/Start
database
Reference:
12c Below one more my favrt. link to configure and understand smart flash cache.
