Manually Populating Exadata Smart Flash Cache

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 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.

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.

Note:

When using a container database (CDB), a CDB resource plan is required to govern the resource allocation to each pluggable database (PDB), even if there is only one PDB in the CDB.

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 IROM 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 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

In addition to other approaches, you can set the CELL_FLASH_CACHE segment storage option to KEEP to elevate the segment priority and keep the segment data in Exadata Smart Flash Cache, even when the data is not being used.

For example:

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

SQL> ALTER TABLE t2 STORAGE (CELL_FLASH_CACHE KEEP);

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.

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.