This is the fourth blog out of a 6-blog series that discusses Oracle and storage performance. If you’re new to this series, please visit the first blog in this series Moving up the stack: Understanding Oracle and storage performance.
Similar to direct path read, which was covered in the previous blog, the direct path read temp is an access path in which multiple Oracle blocks are read directly to the Oracle process memory without being read into the buffer cache in the Shared Global Area (SGA). The main difference between the two access paths is the source of data: in direct path read temp the data is read from temporary tablespaces. This wait event is usually caused by a sort operation that cannot be completed in memory and requires storage access. It is common to encounter the direct path read temp event during parallel query execution.
Similarly, direct path write temp is an access path in which multiple Oracle blocks are written directly to the temporary files by the shadow Oracle process.
The following SQL statement illustrates a parallel query scanning a table and performing a sort on disk:
The following diagram illustrates an Oracle shadow processes that reads in parallel blocks from the data tablespace, writes the temporary data to the temp tablespace and reads the temporary data back without placing it in the buffer cache. The read and write operations are part of a sort performed by the processes:
Below is an example of an AWR report illustrating direct path read temp as one of the top 5 wait events:
The AWR below is taken from the same workload but this time running it against the K2 all-flash array. The CPU time was doubled and the latency of direct path read temp was significantly improved (from 13ms to 1ms):
direct path read temp is common for applications with a high amount of large reads (such as full scans or range scans) using parallel query executions, which require sort operations. Sort might be a result of an Order By clause or a merge join operation. This is true for BI, DWH and DSS workload environments.
In order to improve direct path read temp, the underlying storage system must support consistent low latency, high IOPS, and high throughput – simultaneously! Take a look at how the K2 handles Oracle workloads here.