How to use the CELL_FLASH_CACHE segment storage option in Oracle Exadata

Oracle Exadata Smart Flash Cache is designed to automatically manage frequently accessed data, ensuring high performance while balancing storage efficiency. Although the default caching policy works well in most cases, administrators can override it using the CELL_FLASH_CACHE segment storage option. This option allows fine-grained control over how specific database objects are cached, improving performance for critical workloads.

What is CELL_FLASH_CACHE?

• CELL_FLASH_CACHE is a segment-level storage option that determines how Exadata Smart Flash Cache handles caching for tables, indexes, partitions, and LOBs.
• It can be set during object creation (CREATE) or modified later (ALTER).
• Useful when certain objects need higher caching priority or should be excluded from caching to save space.

Available Settings

1. NONE
   • Prevents caching of the segment.
   • Ideal for peripheral or rarely accessed data.
   • Frees cache space for more important objects.

CREATE TABLE t1 (c1 number, c2 varchar2(200)) STORAGE (CELL_FLASH_CACHE NONE);

2. DEFAULT

• Uses the standard LRU (Least Recently Used) algorithm.
• This is the default setting for all objects.

ALTER TABLE tkbcsrbc MODIFY LOB (l1) (STORAGE (CELL_FLASH_CACHE DEFAULT));

3. KEEP

Starting with Exadata System Software 24.1.0 and Oracle Database 23ai, KEEP segments are automatically populated into cache. Elevates the segment’s priority in cache. Ensures frequently accessed data stays cached longer.

ALTER TABLE t2 STORAGE (CELL_FLASH_CACHE KEEP);

Advanced Usage

1. Partition-Level Control

• You can set different caching policies for individual partitions.
• Useful when certain partitions are accessed more frequently.
• Example with DEFERRED INVALIDATION (avoids immediate cursor invalidation):

ALTER TABLE ptable MODIFY PARTITION p1 
STORAGE (CELL_FLASH_CACHE KEEP) DEFERRED INVALIDATION;

2. Partitioned Table Example

CREATE TABLE ptable (c1 number, c2 clob) TABLESPACE TBS_1
PARTITION BY RANGE(c1) (
   PARTITION p1 VALUES LESS THAN (100) TABLESPACE TBS_2 
      STORAGE (CELL_FLASH_CACHE DEFAULT),
   PARTITION p2 VALUES LESS THAN (200) TABLESPACE TBS_3 
      STORAGE (CELL_FLASH_CACHE KEEP)
);

3. LOB Segment Example

CREATE TABLE tkbcsrbc (c1 number, l1 clob)
   LOB (l1) STORE AS securefile 
      (cache nologging STORAGE (CELL_FLASH_CACHE NONE))
   PCTFREE 0 TABLESPACE tbs_93 STORAGE 
      (initial 128K next 128K pctincrease 0);

Querying CELL_FLASH_CACHE Settings

You can check the caching policy applied to objects using system views:

For tables:

SELECT TABLESPACE_NAME, TABLE_NAME, CELL_FLASH_CACHE FROM user_tables;

For indexes:

SELECT INDEX_NAME, CELL_FLASH_CACHE FROM ALL_INDEXES;

Practical Uses

• Performance Optimization: Keep critical tables or indexes cached for faster query response.
• Resource Management: Exclude rarely accessed data to free cache space for high-demand workloads.
• Partition Strategy: Assign different caching policies to partitions based on usage patterns.
• LOB Handling: Control caching for large objects (LOBs) to balance performance and storage.
• Migration Planning: Use KEEP to pre-populate cache for workloads expected to benefit from flash acceleration.