9.1.6 Faster Performance for Large Analytic Queries and Large Loads

Temp writes and temp reads are used when large joins or aggregation operations don't fit in memory and must be spilled to storage. In Oracle Exadata System Software releases earlier than 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. In Oracle Exadata System Software release 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 significantly speeds up queries that spill to temp if they are temp I/O bound. For certain queries, performance can improve up to four times faster. This is comparable to putting the temporary tablespace entirely in flash. Write-back flash cache has to be enabled for this feature to work.

Prior to Oracle Exadata System Software release 12.2.1.1.0, there was a size threshold for writes to the flash cache. Most writes over 128 KB are routed straight to disk because these writes are not expected to be read any time soon. For example, direct load writes, flashback database log writes, archived log writes, and incremental backup writes would bypass flash cache. Starting with Oracle Exadata System Software release 12.2.1.1.0, the flash cache algorithms have been enhanced to redirect such 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. This feature allows Oracle Exadata Storage Server to utilize additional spare flash capacity and I/O bandwidth to provide better overall performance.

Note that this feature is supported on all Oracle Exadata Database Machine hardware except for V2 and X2 storage servers. On X3 and X4 storage servers, flash caching of temp writes and large writes is not supported when flash compression is enabled.

New statistics and report sections related to this feature were added to Automatic Workload Repository (AWR) reports in Oracle Database 18c (18.1.0), also available in the 12.1.0.2.0 July 2017 DBBP and the 12.2.0.1.0 Oct 2017 RUR.

Minimum requirements:

• Oracle Exadata Database Machine X3-2 or later

• Oracle Exadata System Software 12.2.1.1.0

• Oracle Database (one of the following):

  • Oracle Database 11g release 2 (11.2) with the fix for bug 24944847 applied

  • Oracle Database 12c release 1 (12.1) — 12.1.0.2.0 July 2017 DBBP

  • Oracle Database 12c release 2 (12.2.0.1) — 12.2.0.1.0 Oct 2017 RUR

  • Oracle Database 18c (18.1.0)