Pinning Table in the Flash Cache

Objects are automatically cached in the Exadata Smart Flash Cache (ESFC), however the DBA can enforce that an object is kept in flash cache. The CELL_FLASH_CACHE storage clause attribute controls prioritization of blocks within the ESFC and also the treatment of Smart Scan blocks. There are three possible settings

DEFAULT: The automatic caching mechanism is in effect. This is the default value.
NONE: Never cache this object.
KEEP: The object should be given preferential status.

Note that this designation also changes the default behavior of Smart Scans allowing them to read from both the cache and disk.

Here is an example of changing the CELL_FLASH_CACHE storage clause using the “ALTER TABLE” command:

To pin a table in ESFC:


SQL> ALTER TABLE llamadas STORAGE (CELL_FLASH_CACHE KEEP);

Table altered.

This storage attribute can also be specified when the table is created:


SQL> create table region
2 (
3 name varchar2(30 byte) not null enable,
4 num number not null enable,
5 buddy_region number default null,
6 change_state char(1 byte) default null,
7 weights varchar2(500 byte) default null,
8 primary key (name)
9 using index pctfree 10 initrans 2 maxtrans 255 compute statistics
10 tablespace tbs_idx enable
11 )
12 (cell_flash_cache keep)
13 tablespace tbs_data;

Table created.

Setting the storage clause CELL_FLASH_CACHE to KEEP, smart scan will attempt to read the data directly from memory. This situation can be analyzed by checking the “cell flash cache read hits“:


SQL> select * from llamadas;

SQL> select name, value from v$sysstat where name in ('physical read total IO requests','cell flash cache read hits');

NAME    VALUE 
------ ------------ 
physical read total IO requests 1274902412
cell flash cache read hits       984578330

Scan operations on a large table are unaffected by the Flash Cache unless the table is associated with the CELL_FLASH_CACHE KEEP clause as shown in figure 1.

Figure 1: Result of CELL_FLASH_CACHE storage setting on ESFC

To un-pin a table in ESFC:


SQL> ALTER TABLE llamadas STORAGE (CELL_FLASH_CACHE DEFAULT);

Can examine the current settings for the CELL_FLASH_CACHE clause by querying DBA_SEGMENTS, DBA_TABLES or DBA_INDEXES:


SQL> SELECT segment_name,segment_type,cell_flash_cache FROM dba_segments where segment_name = 'LLAMADAS';

SEGMENT_NAME SEGMENT_TYPE CELL_FLASH_CACHE
------------- ------------- ---------------
LLAMADAS TABLE KEEP