Improve Temp Reads and Writes with Exadata Storage Server releases 12.2.1.1.0

We all have worked with large temp tablespaces in our data warehouse databases. I personally have worked with 10 TB temp tablespace for 50 TB Data Warehouse running on Exadata machine, which was required for large table joints and aggregate operations. Temp writes and temp reads are used when large joints or aggregation operations don’t fit in memory and must be spilled to storage. Before Oracle Exadata Storage Server released 12.2.1.1.0, temp writes were not cached in flash cache. Both temp writes and subsequent temp reads were from hard disk only. With the release of Oracle Exadata Storage Server 12.2.1.1.0, temp writes are sent to flash cache so that subsequent temp reads can be read from flash cache as well. This can drastically improve performance for queries that spill into temp area. As per Oracle, for certain queries performance can improve up to four times faster.

Additionally, imagine an application using a lot of temp tables and now they can run entirely from flash. This feature can enhance performance for these applications many folds. This feature uses a threshold of 128KB to decide whether to send request directory to disk or write it to flash cache. Therefore, direct load writes, flashback database log writes, archived log writes, and incremental backup writes would bypass flash cache. This feature will redirect large writes into the flash cache, provided that such large writes do not disrupt the higher priority OLTP or scan workloads. Such writes are later written back to the disks when the disks are less busy.

Considerations:

  • Write-back flash cache has to be enabled for this feature to work.
  • Oracle Database 11g release 2 (11.2) or Oracle Database 12c release 1 (12.1), then you need the patches for bug 24944847.
  • This feature is supported on all Oracle Exadata hardware except for V2 and X2 storage servers.
  • Flash caching of temp writes and large writes is not supported when flash compression is enabled