Oracle 11gR2 Database Flash Cache

Posted to setup the Database Flash Cache, then start to Test.

SQL> show parameter db_flash_cache_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string /dev/sdc

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 10G

Good idea to use the flash cache, when "db file sequential read" is a top wait event.

The FLASH_CACHE clause lets you override the automatic buffer cache policy and specify how specific schema objects are cached in flash memory. To use this clause, Database Smart Flash Cache (flash cache) must be configured on your system. The flash cache is an extension of the database buffer cache that is stored on a flash disk, a storage device that uses flash memory. Because flash memory is faster than magnetic disks, the database can improve performance by caching buffers in the flash cache instead of reading from magnetic disk.

Example:

SQL> create table tb_test01 as select * from all_objects where rownum <=10;
Table created.

SQL> select table_name, flash_cache from user_tables where table_name='TB_TEST01';

TABLE_NAME FLASH_C
------------------------------ -------
TB_TEST01 DEFAULT

KEEP Specify KEEP if you want the schema object buffers to remain cached in the flash cache as long as the flash cache is large enough.

NONE Specify NONE to ensure that the schema object buffers are never cached in the flash cache. This allows you to reserve the flash cache space for more frequently accessed objects.

DEFAULT Specify DEFAULT if you want the schema object buffers to be written to the flash cache when they are aged out of main memory, and then be aged out of the flash cache with the standard buffer cache replacement algorithm. This is the default if flash cache is configured and you do not specify KEEP or NONE.

SQL> create table TB_TEST01 storage( flash_cache keep) as select * from all_objects;

Table created.

SQL> select table_name,flash_cache from user_tables;

TABLE_NAME FLASH_C
------------------------------ -------
TB_TEST01 KEEP

Check V$SYSSTAT

SQL> select * from v$sysstat where name like 'flash cache%';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------- ---------- ---------- ----------
87 flash cache inserts 8 27933 2941771786
91 flash cache eviction: invalidated 8 22256 3647825193
92 flash cache eviction: buffer pinned 8 0 3134415242
93 flash cache eviction: aged out 8 0 3307935113
94 flash cache insert skip: not current 8 12 1693383402
95 flash cache insert skip: DBWR overloaded 8 0 3504558414
96 flash cache insert skip: exists 8 8150 3291155557
97 flash cache insert skip: not useful 8 3738 3620030478
98 flash cache insert skip: modification 8 195 4128442906
99 flash cache insert skip: corrupt 8 0 2560222967

Check number of blocks(segment) in the flash cache.

SQL> 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)
where owner='SURACHART' and object_name='TB_TEST01'
group by owner,object_name
/

OBJECT FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------------------------ ------------ ------------ ------------
SURACHART.TB_TEST01 67 1607 1674

Or number of blocks(all segments) in the flash cache

SQL> SELECT
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
/

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
5535 14281 19816

However, We can disable the flash cache by set db_flash_cache_size to zero.

SQL> show parameter db_flash_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 10G

SQL> alter system set db_flash_cache_size=0;

System altered.

SQL> show parameter db_flash_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 0

Reference:
Oracle Document 11gR2
Using the Oracle 11GR2 database flash cache
Pardon Me, Where Is That Flash Cache? Part I.
Pardon Me, Where Is That Flash Cache? Part II.