There are two columns in DBA_TABLES in 11gR2 : FLASH_CACHE and CELL_FLASH_CACHE.
For both the values permitted are NONE, DEFAULT and KEEP.
Can you please explain what is the purpose of these two columns, as if I understand correctly, in Exadata, CELL_FLASH_CACHE is relevant related to smart flash cache technology.
I tried to google my query, but couldn't found any satisfactory explanation for the same.
The FLASH_CACHE value is related to caching on the node where the instance runs (in a none Exadata setup).
You need to have the flash card(s) setup on your database server and set the following instance parameters.
The CELL_FLASH_CACHE is related to Exadata storage cell flesh cache.
CELL_FLASH_CACHE and FLASH_CACHE are indeed confusingly named, and refer to two different concepts.
CELL_FLASH_CACHE = Exadata Smart Flash Cache. Whitepaper URL: http://www.oracle.com/technetwork/server-storage/engineered-systems/exadata/exadata-smart-flash-cache-366203.pdf
FLASH_CACHE = Oracle Database Smart Flash Cache. Whitepaper URL: http://www.oracle.com/technetwork/articles/systems-hardware-architecture/oracle-db-smart-flash-cache-175588.pdf
The FLASH_CACHE column in the DBA_TABLES refers to the database flash cache which is an extension of the buffer cache on the database server. Exadata smart flash cache is disk cache on the Exadata storate server. Exadata will automatically store frequently queried data in the Smart Flash Cache. However, if you specifically want to store any object in the ESFC then you can use the following command. alter table <table_name> storage (cell_flash_cache keep)
The ESFC can prove handy in an OLTP environment. Smart Scans will ignore objects in the ESFC unless the object is specifically pinned into it.
You can use the following query to check the cell flash cache read hits. select name, value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%cell flash%' order by 1
Basically the Database smart flash cache resides on your database server and is nand flash either ssd or pci card. If in exadata they had added flash cache to the compute nodes that would be utilized as database smart flash cache. This is similar to using a ram disk for buffer cache etc but it is Nand flash aware and so will try to spread out reads and writes in order to preserve the life of your flash memory ( wear levelling friendly).