3.2.1 Overriding the Default Caching Policy

Exadata Smart Flash Cache stores frequently accessed data in high-performance flash storage. It automatically works in conjunction with Oracle Database to intelligently optimize cache efficiency by favoring frequently accessed and high-value data. Just as importantly, it avoids caching data that will never be reused or will not fit into the cache.

By default, caching occurs automatically and requires no user or administrator effort.

Although it is generally not required or recommended, Oracle Exadata System Software also enables administrators to override the default caching policy and keep specific database segments in or out of the cache by using the CELL_FLASH_CACHE segment storage option. 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 the database segment is automatically cached using the intelligent algorithms that support Exadata Smart Flash Cache. This value is the default setting for CELL_FLASH_CACHE.

• KEEP: This value prioritizes the segment in Exadata Smart Flash Cache, ensuring that its data is cached ahead of any data belonging to non-keep objects. While not guaranteeing that the segment is always cached, this setting greatly increases the likelihood of keeping the segment in the cache.

  Note:

  • This setting is generally not recommended. Excessive use may adversely impact performance by limiting the space available for frequently accessed non-keep objects.

  • This setting elevates the cache priority for all mirrored copies of data. Using this option with high redundancy storage means that all three data copies get elevated cache priority in Exadata Smart Flash Cache on three separate storage servers. In contrast, the default caching algorithms allow the tertiary mirror to age out of the cache sooner than the primary data copy.

  • Starting with Oracle Exadata System Software release 24.1.0 and 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 the DEFERRED INVALIDATION clause, 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';