Oracle 11G Direct Path read it's beautiful and it hurts.

Direct Path Read

In 11g, a full table scan may use the direct path read method, bypassing buffer cache, so that the full table scan is physical read.

In 10g, all are read by GC buffer, so there is no problem with direct path read.

The possible reasons for higher direct path read are:

1. A large number of disk sequencing operations, order by, Group by, Union, DISTINCT, rollup, cannot be sorted in the PGA and need to be sorted using the temp table space. Direct path read is generated when the sort results are read from the temporary table space.

2. A large number of hash join operations, using the temp table space to save the hash area.

3. Parallel processing of SQL statements

4. Large table full table scan, in, the whole table scanning algorithm has the new change, according to the table size, the cache size and so on information, decides whether bypasses the SGA directly from the disk reads oracle11g to fetch the data. 10g, however, reads all data through the cache, called Table scan (Large). 11G considers the full table of large tables to be read using direct paths, which may be more readable than data files in 10g (db file scattered reads) faster and use less latch.

A large number of direct path read wait times are most likely an application issue. The direct path read event is driven by SQL statements that perform direct read operations from a temporary or regular table space. When the input content is larger than the work area in the PGA, the SQL statement with the function that needs to be sorted writes the result to the staging table space, and the sort order string in the temporary tablespace is subsequently merged to provide the final result. When the sorting result is read, the Oracle session waits on the direct path read wait event. Db_file_direct_io_count initialization parameters may affect the performance of DIRECT path read.

An implied parameter:

_serial_direct_read = False Disables direct path read

_serial_direct_read = True Enable Direct path read

Alter Sytem set "_serial_direct_read" =never scope=both sid= ' * '; Can significantly reduce the direct path read

It looks beautiful. When it comes to another wait event, it is to brush the dirty data in the cache back into the data file. The check point event is fired, and DBWR writes a very frequent task.

A large number of read IO results in slow io, and slow IO makes the DBWR write slower, while the check point event blocks DML. It is a very serious accident in the area of OLTP.

Because the application has a large number of statements with full table queries.

Oracle 11G Direct Path read it's beautiful and it hurts.