A few years ago, we set out to define everything you need to know about Oracle I/O waits events. This was part of a series that dealt with Oracle I/O Wait events – “ ‘db file sequential read’ wait event,” “db file scattered read,” “Direct Path Read,” and “Direct path read temp and direct path write temp.” A large percentage of these waits events were caused by weak storage performance.
We’ve seen some major strides in the storage world since we published these blog posts in 2011. Solid-State is becoming a must-have for enterprises looking for effective storage solutions. And, SSD performance is constantly improving providing the framework for Oracle to excel its performance.
With that in mind, we’d like to put together a refresher on the major Oracle I/O wait events. Let’s start with direct path read temp and direct path write temp.
The Direct Path Read Temp wait events are 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). in Direct Path Read Temp the data is read from temporary tablespaces. This 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 wait events are 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:
So, what can you expect from running Oracle on Kaminario’s K2 all-flash array, with regards to these events?
Depending on the block size, we can expect around 1-4 ms latency where without all Flash array it is common to see 10-100 ms latencies. See below an example from running the above Oracle workload on K2. Notice the improvements in :direct path read temp” latencies and as a result the DB CPU is increased.
“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.. It is common to see such workloads in large BI, DWH and DSS applications.
In order to improve “Direct Path Read Temp”, a storage system needs to provide both low latency on higher IOPS and high throughput. It is common to see in a non SSD environments that latency increased significantly when Oracle execute queries that use direct path read/write temp. The Kaminario all-flash 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 ), as you can see from the POC results above.