Shulou(Shulou.com)06/01 Report--

db file sequential read: direct path read;

The official statement reads as follows:

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.

Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call because of extent boundaries, or buffers present in the buffer cache. These waits would also show up as db file sequential read.

Check the following V$SESSION_WAIT parameter columns:

? P1: The absolute file number

? P2: The block being read

? P3: The number of blocks (should be 1)

Explanation: Oracle processes need to access blocks that cannot be retrieved from SGA, so oracle processes wait for blocks to be read from I/O to SGA.

A sequential read is a single-block read, and single-block I/O is generally the result of an indexed read;

db file sequential read The wait event has three parameters:

? P1: The absolute file number

? P2: The block being read first block#

? P3: The number of blocks (should be 1) numo de blocks

db file sequential read latency is caused by executing SQL statements (user and recursive) that read single blocks of index, undo segments, and tables (when accessed via rowid), control files, and data file headers. Physical I/O requests for these objects are quite normal, so the existence of db file sequential read waits does not necessarily mean that the library or application has gone wrong. If the conversation takes a long time on this event, it may not be a bad thing either. Conversely, if the session spends a lot of time on equeue or latch free, then something is wrong.

Question: Is the db file sequential read in the system wait event in the AWR report reasonable?

The following important parameters in the awr report are interpreted, taking the awr report of 11G as an example:

Description: db file sequential read means that the corresponding data cannot be found in the sga, so it has a lot to do with buffer hit. When the buffer hit rate is too low, the corresponding db file sequential read will be high, and the buffer hit will generally remain above 95%.

View the total and average time of db file sequential reads for this report;

Foreground Wait Events also counts the time taken and the average time taken for db file sequential reads.

Check whether there is room for tuning based on SQL User I/O latency;

DB file sequential read optimization method:

From the beginning of reading, increase the size of buffer cache in SGA to avoid reading from hard disk every time; optimize sql statements to reduce unnecessary block reads;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Author: John

ORACLE Technology Blog: ORACLE Hunter Notes Database Technology Group: 367875324 (Please note ORACLE Management)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.