3.2.1 Overriding the Default Caching Policy

Though typically not required, you can use the CELL_FLASH_CACHE segment storage option to override the automatic caching policy for Exadata Smart Flash Cache. The CELL_FLASH_CACHE option may be included in the STORAGE clause as part of the SQL CREATE or ALTER command for various database objects, such as regular tables and indexes, partitioned tables and indexes, and index-organized tables.

For example:

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

SQL> ALTER TABLE t2 STORAGE (CELL_FLASH_CACHE KEEP);

The CELL_FLASH_CACHE option supports the following settings:

• NONE: This value ensures that Exadata Smart Flash Cache never caches the corresponding segment. By using this setting on peripheral database segments, more cache space is available for more-important and frequently-accessed database segments.
• DEFAULT: This value specifies that database segments are cached using the default LRU (least recently used) algorithm of Exadata Smart Flash Cache. This value is the default setting for CELL_FLASH_CACHE.

• KEEP: This value elevates the segment priority in Exadata Smart Flash Cache. By using this setting, you can increase the likelihood of keeping data from the corresponding segment in the cache.

  Starting with Oracle Exadata System Software release 24.1.0 in conjunction with Oracle Database 23ai, segments with this setting are automatically populated into Exadata Smart Flash Cache. Previously, the segment data was populated when the segment was read.

You can set the CELL_FLASH_CACHE segment storage option separately on each partition in a partitioned segment, which is particularly useful if you want to influence the caching priority for different partitions based on predictable usage patterns. While setting the CELL_FLASH_CACHE segment storage option on a partition, you can add the DEFERRED INVALIDATION clause. For example:

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

By using this option, you can dynamically modify the segment storage option without immediately invalidating dependent cursors. This option requires Oracle Database software that contains the patch for bug 33456703, which is included in Oracle Database version 19.15, Oracle Database version 21.6, and later releases.

Example 3-1 Setting CELL_FLASH_CACHE on partitions

This example shows setting CELL_FLASH_CACHE individually on multiple partitions in a CREATE TABLE command.

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));

Example 3-2 Setting CELL_FLASH_CACHE on a LOB segment

This example shows setting CELL_FLASH_CACHE for a LOB segment in a CREATE TABLE command.

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);

Example 3-3 Using ALTER TABLE with CELL_FLASH_CACHE

For objects where altering the storage clause is allowed, the ALTER command can be used with CELL_FLASH_CACHE, as shown in these examples.

ALTER TABLE tkbcsrbc STORAGE(CELL_FLASH_CACHE KEEP);

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

Example 3-4 Using Views to Query CELL_FLASH_CACHE Storage Clause

The CELL_FLASH_CACHE storage clause attribute can be queried using database views based on the object involved.

SELECT TABLESPACE_NAME, TABLE_NAME, CELL_FLASH_CACHE FROM user_tables WHERE table_name='TKBCSRBC';

SELECT CELL_FLASH_CACHE FROM ALL_INDEXES WHERE index_name='TKBCIDX';