Oracle Exadata Smart Flash Cache is designed to accelerate database performance by caching frequently accessed data. By default, it prioritizes OLTP workloads (transaction-heavy operations) and limits caching of large I/Os with low reuse potential, such as temporary segments.

However, analytical workloads (large joins, sorts, aggregations) often rely heavily on temporary segments. To better support these workloads, Exadata introduced the main_workload_type parameter, allowing administrators to tune Smart Flash Cache behavior for either OLTP or Analytics.

Key Points

• Default Behavior:
  • Favors caching frequently accessed data.
  • Limits caching of large I/Os (temporary segments).
  • Best suited for OLTP workloads.
• Analytical Workloads:
  • Can under-utilize Smart Flash Cache due to reliance on temporary segments.
  • Setting main_workload_type = ANALYTICS relaxes restrictions, allowing temporary segments to occupy most of the cache.
• Version Note:
  • This capability applies only to Exadata System Software versions prior to 25.2.
  • From release 25.2 onwards, enhancements make workload type tuning unnecessary.

How to Configure

1. At the Database Level (CDB or non-CDB)

ALTER SYSTEM SET main_workload_type = OLTP;
ALTER SYSTEM SET main_workload_type = ANALYTICS;

2. At the Pluggable Database (PDB) Level

ALTER SESSION SET CONTAINER = <pdb_name>;
ALTER SYSTEM SET main_workload_type = OLTP;
ALTER SYSTEM SET main_workload_type = ANALYTICS;

Note:

• PDB-level settings override CDB-level settings.
• If not set, the default is OLTP.

IORM Requirements

To enable this capability, the I/O Resource Management Plan (IORMPLAN) must include the database in the interdatabase plan (DBPLAN) with a flash cache quota.

Example IORMPLAN:

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

sales database → 20G flash cache quota.

finance database → flash cache limit 10G, minimum 2G.

dev database → flash cache limit 4G, minimum 1G.

test database → minimal share.

Minimum Requirements

• Exadata System Software: 22.1.12.0.0 or 23.1.3.0.0.
• Oracle Database: 19c with patch 35017301.

🏁 Conclusion

By setting the main_workload_type parameter, administrators can optimize Exadata Smart Flash Cache for either OLTP or Analytics workloads. This ensures better utilization of cache resources, especially for analytical queries involving large temporary segments.