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, themain_workload_type
setting only takes effect in thesales
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. Ifmain_workload_type
is not set in a CDB, the default setting isOLTP
. -
Minimum system requirements:
-
Exadata System Software release 22.1.12.0.0 or 23.1.3.0.0.
-
Oracle Database 19c with patch 35017301.
-
Parent topic: Administering Exadata Smart Flash Cache