Direct path read direct path reading

The traditional way to read the database is to search in memory first. If the data cannot be searched, then the data is read from disk to memory, and then PGA gets the data in SGA, so that the data is cached in memory. The next time you access it, you can get it directly from SGA without physical reading.

Direct path read (direct path reading): direct path reading violates the traditional way of reading data, which means that the server process reads the data file directly into the memory of PGA without going through buffer cache. Here are three ways for the database to read data.

Official description: When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.

If the I/O subsystem supports asynchronous I/O, then the process is able to overlap issuing read requests with processing the blocks existing in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not necessarily the same as the number of read requests (unlike db file scattered read and db file sequential read).

Check the following V$SESSION_WAIT parameter columns:

? P1: File_id for the read call

? P2: Start block_id for the read call

? P3: Number of blocks in the read call

The advantage of direct path reading: existence is reasonable, and I believe that there must be a reason for this algorithm.

The advantages are summarized as follows:

The main results are as follows: 1. After reading by direct path, the number of blocks read can always be guaranteed to be the size set by multi-block read parameters, which improves the reading efficiency.

2. Greatly reduce the use of latch, thus avoiding the possible latch competition (cbc latch, etc.)

3. Reduce the impact of full table scan on buffer cache.

4. Reduce the cost of buffer pin, and it is possible to reduce buffer busy waits and other related waits.

Of course, this method also has side effects:

1. A segment-level checkpoint occurs (described in more detail later), so this additional preparation is done before the query actually starts execution. And it may cause IO jitter because dirty data is written.

2. If your table needs to be scanned and read frequently, it is better to use the traditional reading method.

3. Search for direct path read in MOS and you will find that it may cause multiple delayed block cleanups.

+

Author: JOHN

ORACLE technology blog: ORACLE hunter's note database technology group: 367875324 (please note ORACLE management)

+