3.2.3 Optimizing Exadata Smart Flash Cache for Analytical Workloads

By default, Exadata Smart Flash Cache favors caching of frequently accessed data and limits caching of data with low potential for reuse. Specifically, Exadata Smart Flash Cache limits the amount of cache space occupied by large I/Os with low potential for reuse, which includes I/Os associated with temporary segments. Furthermore, if thrashing (caching of data recently removed from the cache) is detected, additional cache space is created by removing data associated with large I/Os (including temporary segment data).

This approach naturally suits online transaction processing (OLTP) workloads. However, analytical workloads typically under-utilize the resources of Exadata Smart Flash Cache when processing large join and sort operations with large temporary segments.

To better utilize Exadata Smart Flash Cache for analytical workloads, Exadata allows administrators to specify the main workload type for each database and pluggable database (PDB) by setting the main_workload_type database instance parameter.

For example:

• In a container database (CDB) or non-CDB:

  SQL> alter system set main_workload_type = [ OLTP | ANALYTICS ];

  In a PDB:

  SQL> alter session container = <pdb_name>;
  SQL> alter system set main_workload_type = [ OLTP | ANALYTICS ];

The available options are:

• OLTP: Exadata Smart Flash Cache favors caching of frequently accessed data and limits caching of data with low potential for reuse.

• ANALYTICS: Exadata Smart Flash Cache relaxes the limits on caching data with low potential for reuse, enabling temporary segments to occupy most of the cache.

Note the following details regarding this capability:

• To enable this capability, the Exadata I/O resource management plan (IORMPLAN) must contain the associated database as part of the interdatabase plan (DBPLAN), and the plan directive must include a flash cache quota specified using the flashCacheSize attribute. For example, on a system using the following IORMPLAN, the main_workload_type setting only takes effect in the sales database and any PDB it contains:

  CellCLI> ALTER IORMPLAN dbplan=((name=sales, share=8, flashCacheSize=20G), -
                                  (name=finance, share=8, flashCacheLimit=10G, flashCacheMin=2G), -     
                                  (name=dev, share=2, flashCacheLimit=4G, flashCacheMin=1G), -
                                  (name=test, share=1))

• The main_workload_type setting can differ across PDBs or between the CDB and any PDB. Any PDB-level setting overrides the CDB setting.

• If main_workload_type is not set in a PDB, the corresponding CDB setting is used. If main_workload_type is not set in a CDB, the default setting is OLTP.

• Minimum system requirements:

  • Exadata System Software release 22.1.12.0.0 or 23.1.3.0.0.

  • Oracle Database 19c with patch 35017301.