In a previous posts, “db file sequential read,” “db file scattered read” wait events, and “Direct path Read,” I explained three common wait events that are associated with I/O Wait. In this post, I will describe two other common wait events that in many cases are caused by weak storage performance.
This important information is taken from the Oracle performance white paper written by Bartal Vindzberg and myself (which will be posted in a future blog article).
Similar to Direct path read 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 event is usually caused by a sort operation that cannot be complete in memory and requires storage access. It is common to encounter the Direct path read temp event during parallel query execution.
Similar, 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 where a “direct path read temp” is one of the top 5 wait events:
Since I got the above AWR in a POC I recently worked on, The AWR below is taken from the same workload but with K2. The CPU time was doubled and the latency of direct path read temp was significantly improved (from 13 ms to 1 ms):
“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 “Order By” clause or merge join operation. This is true for BI, DWH and DSS workload environments.
In order to improve “direct path read temp”, a storage system needs to provide both low latency on higher IOPS and high throughput. The Kaminario K2 is an ideal storage system for Oracle because it can speed up both single (db file random read) and multiple block access (db file scattered read, direct path read ).