Analysis of Common oracle wait events in dbfilescatteredread

When performing full table scan (FTS) or full index scan (indexfullscan), Oracle tries its best to read multiple blocks at a time to ensure performance, which becomes multiblockIO. Each time multiblockIO is executed, it waits for the completion of the physical IO, and the wait event dbfilescatteredread appears. Use dbfile

When Oracle performs full table scan (FTS) or full index scan (index full scan), to ensure performance, try to read multiple blocks at a time, this becomes multi block I/O. Each execution of multi block I/O will wait for the completion of physical I/O, and the wait event: db file scattered read appears. Use db file

When Oracle performs full table scan (FTS) or full index scan (index full scan), to ensure performance, try to read multiple blocks at a time, this becomes multi block I/O. Each execution of multi block I/O will wait for the completion of physical I/O, and the wait event: db file scattered read appears. Use db file scattered read to wait for the P1 = file #, P2 = initial block #, P3 = The number of blocks to be read. You can confirm which segments will contain multi block I/O.

Oracle defines the maximum value of multi block I/O according to the value of db_file_multiblock_read_count (MBRC). The maximum value can be confirmed through the following method.

Pay attention to this word whenever possible, because oracle also executes single block I/O when performing the FTS, and db file sequential read waits even if the FTS occurs, the sequential and scattered here do not mean whether the read table or index blocks are continuous or scattered, but whether they are continuous or scattered when they are read to the memory. On the FTS, using single block I/O may read a number smaller than the value defined by MBRC:

1. When the boundary line is reached: if there are 9 blocks in a zone, 8 blocks are read in multi block I/O at a time, the remaining block after one read by multi block I/O is read by single block I/O. If there are two remaining blocks, multi block I/O is executed and only two blocks are read.

2. When reading cached blocks during scanning: if the third block is cached when reading 8 blocks, oracle reads the first two blocks through multi block I/O, perform logical I/O once for the third block, and the remaining five blocks are read through multi block I/O. This situation often occurs because of multiple I/O operations, which may be the cause of the decline in the FTS speed.

3. Row link: If a row link is found during the execution of the FTS, the additional I/O caused by oracle to read the remaining rows, at this time, the single block I/O is executed, and the differences between the row link and the row migration (migrated row) need to be accurately understood. The row link occurs when the row size is greater than the block size. Therefore, you can use a larger block or reduce the pctfree value. There is no way to eliminate the row link. Row migration records data to a block at first, but as the row size increases, migration occurs when there is no space in the block. In this case, the row is actually moved to another block, and the rowid indicating the block to be converted and the row location is inserted in the original row. Row migration, especially when scanning tables through indexes, seriously affects the performance. This is because reading one row requires reading two or more blocks. You need to pay attention to the impact of row migration on FTS. FTS is the work of reading all the blocks under HWM from the beginning. In the process of performing the FTS, if oracle encounters row migration, it will not trigger more single block I/O, but will continue to work. This is because we know that we need to re-read it during the scanning process. Therefore, if the HWM position is the same, the performance of the FTS will remain almost unchanged regardless of whether the row migration exists or not. Of course, if a migration occurs, the allocation area will be appended. If HWM moves farther, it will affect the performance of the FTS. To eliminate row migration, pay attention to the above.

Db file scattered read events are the same as db file sequential read events, which are the most common waiting events in oracle. Because only multi block I/O or single block I/O can be executed when reading blocks from data files, the reason for concern about db file scattered read wait is that it is related to physical I/O, and it also appears together with FTS. In the end, it is not suitable to contact the FTS.

Next, we will discuss the solutions to the db file scattered read wait for the oracle I/O layer.

Application Layer

You need to filter out the SQL statements that mainly occur when db file scattered read is waiting. If you do not need to execute the FTS or index ful scan, you can modify the SQL statement or create a more reasonable index. When a large amount of data is read, The FTS performance is better in most cases. We need to consider the corresponding SQL statement to determine whether the advantage of FT is better than that of index range scan, rather than blindly creating indexes.

2. oracle memory Layer

If the buffer cache is too small, physical I/O will be required repeatedly, and the corresponding db file scattered read wait will also increase. At this time, free buffer waits has a high probability of waiting for events to appear together. The severity of db filescattered read wait caused by FTS is not only that I/O is required, but also that the efficiency of the buffer zone is reduced. This affects the work of the session. From this perspective, one of the effective methods for processing the FTS is to use multiple buffer pools. Data that is not used once is read. It is not necessary to save the data to the buffer zone, which affects the work of other users. Although multiple buffers are a powerful method for effective buffer management, they are not widely used. Multiple buffers Improve the Performance of High-Speed buffers in three aspects: First, save frequently accessed objects in the memory, and then minimize physical I/O. Second, the memory occupied by temporary data is quickly overwritten to minimize memory waste. Third, because each buffer pool uses different cache buffers lru chain latches, the effect of reducing the lock contention is achieved.

Another effective way to use FTS is to increase the value of the db_file_multiblock_read_count parameter. This parameter determines the number of blocks read at a time when multi block I/O is executed. Therefore, as this value increases, the FTS speed will also increase accordingly. The db file scattered read wait time is also reduced. However, it is not appropriate to set this value too high in the whole system. It is best to use the alter session set command to temporarily increase this value only during the execution of SQL statements, because if this value increases, the cost related to FTS is relatively low, which may lead to unexpected changes to the SQL Execution Plan.

The use of larger blocks is also a way to improve the performance of the FTS. Larger blocks improve the performance of the FTS in the following two aspects: first, add the number of rows contained in a block, in this way, a table of the same size uses fewer blocks, and the number of multi block I/O operations is reduced accordingly. Second, if the data block is large, the probability of row link or row migration will decrease, and the additional I/O will also decrease. In most OLTP systems, only the standard block size (8 K) is used, but larger blocks can be used on DSS that often scan a large amount of data to improve performance.

Three oracle segments

Setting a proper table partition can effectively reduce the FTS range. For example, when performing the FTS to obtain 1 million of the 0.1 million data records, the range of the 0.1 million data records is separated by partitioning, then, the range of the FTS can be reduced to a tenth.

Four OS/bare device layer

If SQL optimization or buffer cache optimization cannot solve the problem, you should doubt the performance of the I/O system. After comparing the waiting times of the db file scattered read event with the waiting time, if the average waiting time is long, the slow I/O system may be the main reason.

The v $ filestat view can be used to obtain activity information about multi block I/O and single block I/O of each data file.

Select f. file #, f. name,

S. phyrds, s. phyblkrd, s. readtim,-all read work information

S. singleblkrds, s. singleblkrdtim,-single block I/O

(S. phyblkrd-s. singleblkrds) as multiblkrd,-multi block I/O times

(S. readtim-s. singleblkrdtim) as multiblkrdtim,-multi block I/O time

Round (s. singleblkrdtim/decode (s. singleblkrds, s. singleblkrds), 3) as singleblk_avgtim,-single block I/O average wait time (MS)

Round (s. readtim-s. singleblkrdtim)/(s. phyblkrd-s. singleblkrds), 3) as multiblk_avgtim-multi block I/O average wait time (MS)

From v $ filestat s, v $ datafile f

Where s. file # = f. file #

And s. phyblkrd-s. singleblkrds! = 0;

If the average execution time of a specific file is too high, the performance of the I/O system where the file is located should be used to improve the performance. There is no reasonable average wait time value for multi block I/O, but the average wait time should be maintained at about 10 microseconds.

Metalink notes:

WAITEVENT: "db file scattered read" Reference Note [ID 34558.1]

Systemwide Waits:

If the TIME spent waiting for multiblock reads is significant then it can be helpful to determine which segment/s Oracle is refreshing the reads against. the files where the reads are occuring can be found by looking at <> whereBLKS_READ/READS> 1. (A ratio greater than 1 indicates there are some multiblock reads occuring ).

It can also be useful to see which sessions are between Ming scans and trace them to see if the scans are expected or not. This statement can be used to see which sessions may be worth tracing:

SELECT sid, total_waits, time_waited

FROM v $ session_event

WHERE event = 'db file scattered read'

And total_waits> 0

Order by 3, 2

Delete or Update running slow-db file scattered read waits on index range scan [ID 296727.1]

Applicability: 9.2.0.6 and later.

Problem description:

SQL runs slowly. By setting the 10046 event, you can find the db file scattered read wait event, and db file scattered read occurs on an index.

However, the execution plan indicates that the table is using index range scan, and there is no indication that a full table scan or full index scan occurs. However, index range scan of the index should cause db file sequential reads wait events.

At the same time, the number of data blocks obtained by a single db file scattered read is 11.

Db_file_multiblock_read_count is different.

Cause:

This is caused by the Pre-extraction of the index leaf node during the index range scan operation. Oracle can delete and update indexes for maintenance while pre-extracting indexes. In addition, the pre-extraction function is enabled for all indexes by default. The number of pre-extracted data blocks depends on the implicit parameter _ db_file_noncontig_mblock_read_count. The default value of this parameter is 11.

The _ index_prefetch_factor parameter affects the optimization estimation of the number of I/O operations to be performed during index extraction.

Solution:

The index pre-extraction operation has a low probability of affecting the query. you can disable the pre-extraction function by setting _ db_file_noncontig_mblock_read_count to 0 or 1. However, this is generally not recommended.

The following options can be used to improve the query performance:

1. Rebuild the current index

2. Create a more optimized index based on the column corresponding to the where clause.

How to Tell if the IO of the Database is Slow [ID 1275596.1] (not all read)

Determine the corresponding time of I/O:

The following are common waiting times and generally acceptable waiting times.

Troubleshooting I/O-related waits [ID 223117.1] (not read)

Full Table Scans On A Table Is Reading 1 Block At A Time. (Due To Chained/Migrated Rows) [ID 554366.1]

Probably: The pctfree of a table is too low (only set to 5), and the update operation is very frequent, resulting in a large number of row migration. 90% of the rows in the table are migration rows, this results in a full table scan to read only one data block at a time.

When pctfree is used to recreate the table for 20, the row migration disappears and the problem is solved.

Applicability:

Oracle Server-Enterprise Edition-Version: 8.1.7.4 to 11.1.0.6-Release: 8.1.7 to 11.1

References

Notes: 102989.1-How to Find and Eliminate Migrated and Chained RowsNOTE: 122020.1-Row Chaining and Row Migration

Question: it is different from the introduction of db file scattered read in OWI for row migration. It must be verified through experiments.

TROUBLESHOOTING: Advanced Query Tuning [ID 163563.1] (not read)