By default, Exadata Smart Flash Cache is automatically populated when reading data from disk, which is optimal for most application scenarios. However, if your application is sensitive to the increased I/O latency of the initial disk reads, you can manually populate Exadata Smart Flash Cache. This topic describes approaches and considerations for manually populating Exadata Smart Flash Cache.

Scanning Data

If Exadata Smart Flash Cache is not full, you can read data into the cache by simply accessing the desired data, usually through a full table scan.

To determine whether Exadata Smart Flash Cache has available space, compare the FC_BY_ALLOCATED storage server metric with the effectiveFlashCacheSize attribute. For example, the following output shows that each cell contains approximately 4 TB of available space.

# FC_BY_ALLOCATED shows that each cell contains approximately 19 TB of data allocated
$ dcli -g cell_group cellcli -e list metriccurrent where name\=\"FC_BY_ALLOCATED\"
db01celadm01: FC_BY_ALLOCATED        FLASHCACHE      19,313,940 MB
db01celadm02: FC_BY_ALLOCATED        FLASHCACHE      19,311,784 MB
db01celadm03: FC_BY_ALLOCATED        FLASHCACHE      19,311,688 MB

# effectiveCacheSize shows that each cell contains approximately 23 TB of flash cache space
$ dcli -g cell_group cellcli -e list flashcache attributes effectiveCacheSize detail
db01celadm01: effectiveCacheSize:     23.28692626953125T
db01celadm02: effectiveCacheSize:     23.28692626953125T
db01celadm03: effectiveCacheSize:     23.28692626953125T

You can use the following query to confirm whether the desired data is populated in Exadata Smart Flash Cache.

select name,value
  from v$statname n,
       v$mystat s
  where s.statistic# = n.statistic#
    and name in ('physical read IO requests','physical read requests optimized')
  order by name;

For example:

-- get session statistics
SQL> select name,value
  2    from v$statname n,
  3         v$mystat s
  4    where s.statistic# = n.statistic#
  5      and name in ('physical read IO requests','physical read requests optimized')
  6    order by name;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read IO requests                                                 5
physical read requests optimized                                         11

// run the desired workload...

-- get session statistics again
SQL> select name,value
  2    from v$statname n,
  3         v$mystat s
  4    where s.statistic# = n.statistic#
  5      and name in ('physical read IO requests','physical read requests optimized')
  6    order by name;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read IO requests                                            45,193
physical read requests optimized                                     23,140

In the previous example, the workload performed 45188 physical read requests (45193 - 5), of which 23129 (23140 - 11) are optimized. In this case, 22059 (45188 - 23129) unoptimized (disk) reads are performed.

When you repeat the workload, all of the reads should be optimized (physical read IO requests = physical read requests optimized), which indicates that all of the desired data is populated in Exadata Smart Flash Cache.

You can also use CellCLI with the LIST FLASHCACHECONTENT command to see the amount of cache space occupied by each database object on each storage server.

Using flashCacheMin

If Exadata Smart Flash Cache is fully populated in a consolidated environment, you can use the I/O Resource Management (IORM) flashCacheMin setting to free up space so that the desired data can be read into the cache. In this case, the flashCacheMin setting must be larger than the current space occupied by the database with sufficient space to add the desired data.

To determine the current Exadata Smart Flash Cache space allocation for each database, examine the DB_FC_BY_ALLOCATED metric. For example:

$ dcli -g cell_group cellcli -e list metriccurrent where name\=\"DB_FC_BY_ALLOCATED\"
db01celadm01: DB_FC_BY_ALLOCATED     ASM                     0.000 MB
db01celadm01: DB_FC_BY_ALLOCATED     DBCDB1              1,694,241 MB
db01celadm01: DB_FC_BY_ALLOCATED     DBCDB2              4,851,611 MB
db01celadm01: DB_FC_BY_ALLOCATED     DBCDB3              4,638,129 MB
db01celadm01: DB_FC_BY_ALLOCATED     DBCDB4              2,157,755 MB
db01celadm01: DB_FC_BY_ALLOCATED     DBCDB5              9,509,356 MB
db01celadm01: DB_FC_BY_ALLOCATED     _OTHER_DATABASE_      365,790 MB
db01celadm02: DB_FC_BY_ALLOCATED     ASM                     0.000 MB
db01celadm02: DB_FC_BY_ALLOCATED     DBCDB1              1,629,001 MB
db01celadm02: DB_FC_BY_ALLOCATED     DBCDB2              4,761,316 MB
db01celadm02: DB_FC_BY_ALLOCATED     DBCDB3              4,495,902 MB
db01celadm02: DB_FC_BY_ALLOCATED     DBCDB4              2,106,805 MB
db01celadm02: DB_FC_BY_ALLOCATED     DBCDB5              9,848,567 MB
db01celadm02: DB_FC_BY_ALLOCATED     _OTHER_DATABASE_      377,023 MB
db01celadm03: DB_FC_BY_ALLOCATED     ASM                     0.000 MB
db01celadm03: DB_FC_BY_ALLOCATED     DBCDB1              1,664,919 MB
db01celadm03: DB_FC_BY_ALLOCATED     DBCDB2              4,872,123 MB
db01celadm03: DB_FC_BY_ALLOCATED     DBCDB3              4,459,631 MB
db01celadm03: DB_FC_BY_ALLOCATED     DBCDB4              2,096,412 MB
db01celadm03: DB_FC_BY_ALLOCATED     DBCDB5              9,750,586 MB
db01celadm03: DB_FC_BY_ALLOCATED     _OTHER_DATABASE_      315,181 MB

In the previous example, DBCDB1 consumes approximately 1.6 TB of cache space on each cell. To create a minimal IORM plan that increases the DBCDB1 allocation to 2 TB on each cell you could use the following command:

$ dcli -g cell_group cellcli -e alter iormplan dbplan=\(\(name=DBCDB1, flashCacheMin=2T\)\)

If Exadata Smart Flash Cache is fully populated in a consolidated environment, then using the IORM flashCacheMin to increase the allocation for one database effectively steals space away from all of the others. In such cases, the cache space is not transferred immediately, and it may take more than one scan of the desired data to bring it into the cache.

Using CELL_FLASH_CACHE KEEP

Starting with Oracle Exadata System Software release 24.1.0 and Oracle Database 23ai, you can automatically load the segment data into Exadata Smart Flash Cache by setting the CELL_FLASH_CACHE segment storage option to KEEP.

Note that the KEEP option elevates the caching priority of the segment, ensuring that its data is cached ahead of any data belonging to non-keep objects. So, consider other approaches before using the KEEP option to load the cache. Also, after loading the desired segment data into Exadata Smart Flash Cache, restore the default segment priority by resetting the CELL_FLASH_CACHE storage option to DEFAULT. Failure to restore the default segment caching priority may impact the performance of non-keep objects.

For example, the following commands show how to enable the KEEP option on a newly created table (t1) and an existing table (t2).

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

You can use the CellCLI LIST FLASHCACHECONTENT command to monitor the amount of cache space occupied by each database object on each storage server. After the desired segment data is in the cache, you can use the SQL ALTER TABLE command to reset the CELL_FLASH_CACHE storage option to DEFAULT.

For example:

SQL> ALTER TABLE t1 STORAGE (CELL_FLASH_CACHE DEFAULT);
SQL> ALTER TABLE t2 STORAGE (CELL_FLASH_CACHE DEFAULT);

Note also that with software releases before Oracle Exadata System Software release 24.1.0 and Oracle Database 23ai, setting the CELL_FLASH_CACHE segment storage option to KEEP does not immediately load data into Exadata Smart Flash Cache, and segment data is populated into Exadata Smart Flash Cache when the segment is accessed.

Identifying Objects to Populate Manually

You can use the Segments by UnOptimized Reads section of the AWR report to identify objects that are not being cached.

This information is also available by comparing physical reads with optimized physical reads in V$SEGMENT_STATISTICS and V$SEGSTAT.

Other Considerations

• Small segments may not qualify for direct path reads and end up populating the database buffer cache when manual population is performed. This can be avoided by using a parallel query to perform the scan.

• You need to perform multiple scans to populate the cache with data from tables and their associated indexes. To populate the cache with index blocks, use an INDEX FAST FULL SCAN.