Oracle11Gdirectpathread is very beautiful and hurtful.

Oracle11Gdirectpathread is very beautiful and hurtful.
Direct path read

In 11g, full table scan may use direct path read to bypass the buffer cache. Such full table scan is physical read.

In 10 Gbit/s, it is read through gc buffer, so there is no direct path read problem.

High direct path read may be caused:

1. A large number of disk sorting operations, such as order by, group by, union, distinct, and rollup, cannot be sorted in PGA. The temp tablespace must be used for sorting. When reading the sorting result from the temporary tablespace, direct path read is generated.

2. A large number of Hash Join operations use temp tablespace to save the hash partition.

3. Parallel Processing of SQL statements

4. full table scan for large tables: in the middle, the full table scan algorithm has new changes. Based on the table size, cache size, and other information, determine whether to bypass the SGA and directly read data from the disk Oracle11g. 10 Gb reads all data through the cache, which is called table scan (large ). 11g considers that direct path reading is used for a large table, which may be faster than hash reading (db file scattered reads) for data files in 10g, and less latch is used.

A large amount of direct path read wait time is most likely an application issue. The direct path read event is driven by SQL statements that execute direct read operations from temporary or conventional tablespaces. When the input content is greater than the work area in PGA, the SQL statement with the function to be sorted writes the sorting result to the temporary tablespace, And the sorting sequence strings in the temporary tablespace are then merged, used to provide the final result. When reading the sorting result, 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 implicit parameter:

_ Serial_direct_read = false disable direct path read

_ Serial_direct_read = true enable direct path read

Alter sytem set "_ serial_direct_read" = never scope = both sid = '*'; can significantly reduce direct path read

It looks pretty when it brings another wait event, that is, to refresh the dirty data in the cache back to the data file. The check point event is triggered, and tasks written by DBWR are frequent.

A large number of read IO leads to slow IO, slow IO slows down DBWR writing, and the check point event will block DML. OLTP is a serious accident.

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