Direct Path Read

In previous posts, “db file sequential read” and “db file scattered read” wait events, I explained two common wait events that are associated with I/O Wait. In this post I will describe another common wait event that in many cases is caused by a weak storage performance.

Direct path read 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). This event is usually caused by scanning an entire table, index, table partition, or index partition during Parallel Query execution (although 11g support “direct path read” on serial scans). The following SQL statement illustrates a parallel query scanning a table:

Sample SQL Query:Select /*+ Parallel(emp 4) */ * from Employee emp;

Execution Plan:

SELECT STATEMENT

PX COORDINATOR

PX RECEIVE

PX SEND RANGE

PX BLOCK ITERATOR

TABLE ACCESS FULL EMPLOYEE

The following diagram illustrates Oracle shadow processes that read multiple blocks in parallel from the disk and place them in the Oracle shadow processes memory bypassing the buffer cache in the SGA:

Below is an example of an AWR report illustrating where a “direct path read” is one of the top 5 wait events:

Top 5 Timed Events

Event

Waits

Time(s)

Avg Wait(ms)

% Total Call Time

Wait Class

db file scattered read

288,146

10,909

38

33.8

User I/O

direct path read

148,245

8,965

60

27.8

User I/O

CPU time

4,680

14.5

PX Deq Credit: send blkd

51,263

4,246

83

13.2

Other

db file sequential read

337,674

3,406

10

10.6

User I/O

The AWR report shows the number of waits the Oracle sessions encountered for this event. Note however, that many I/Os will be completed before the session actually needs to process the blocks (read ahead or pre-fetch operations). So, a low number of waits does not necessarily mean a low number of IOPS. This is also one of the reasons why parallel queries significantly increase the load on the storage, as I once wrote in the post https://kaminario.com/blog/storage-performance-and-parallelism/ that

“direct path read” is common for applications with a high amount of large reads (such as full or range scans) using parallel query executions. This is true for BI, DWH and DSS workload environments.

In order to improve “direct path read”, a storage system needs to provide both low latency with high IOPS, as well as high throughput. 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).

New Call-to-action