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

Oracle Study-- Oracle wait event (4)

Db file scattered read

This wait event, which is often seen in the actual production database, is a wait event caused by a user action. This wait event occurs when a user issues a SQL operation such as a SQL operation that needs to read multiple data blocks each time. The two most common cases are full table scan (FTS: Full Table Scan) and index fast scan (IFFS: index fast full scan).

The scattered in this name may lead many people to think that it reads data blocks in the way of scattered. In fact, on the contrary, when such wait events occur, SQL operations read data blocks sequentially, such as FTS or IFFS mode (if you ignore the situation where the data blocks that need to be read already exist in memory).

Scattered here refers to the way in which read blocks of data are stored in memory. After they are read into memory, they exist in memory in a decentralized manner rather than contiguously.

This wait event has three parameters:

The file number of the data file in which the data block is to be read by File#:.

The starting block number to be read by Block#:.

Blocks: the number of blocks to read.

Case study:

12:04:54 SYS@ prod > select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event12:04:59 2 where upper (event) like'DB FILE%' EVENT TOTAL_WAITS AVERAGE_WAIT -db file sequential read 5069 .02db file scattered read 930.03db file single write 27.36db file parallel write 15 14.24db file parallel read 34 .64 Elapsed: 00 select 00 from 00.1212 5 5 SCOTT@ prod > select * Elapsed T1 12:05:04 SYS@ prod > select event,TOTAL_WAITS AVERAGE_WAIT from v$system_event 2 * where upper (event) like'DB FILE%'EVENT TOTAL_WAITS AVERAGE_WAIT -- db file sequential read 5166 .02db file scattered read 966.03db file single write 27 .36db file parallel write 16 13.69db file parallel read 34 .64Elapsed: 00:00:00.02

Oracle also carries out Single Block I mango O when performing FTS. At this point, db file sequential read waiting occurs even for FTS. The use of Single Block Ibind O on FTS or the read number of blocks less than the MBRC value are as follows:

(1) when the boundary of the zone is reached: if there are nine blocks in an area, and eight blocks are read at one time by Multi Block Imax O, then the remaining block after one read with Multi Block Imax O is read by Single Block Imax O, and if there are two remaining blocks, Multi Block Imax O will be executed and only two blocks will be read.

(2) when reading cached blocks during scanning: if 8 blocks are read, and the third block is cached, oracle reads the first two blocks through Multi Block Imax O, for the third block, Logical I _ pico is performed once, and the remaining 5 blocks are read by Multi Block I _ Unio. When this happens frequently, it may be the cause of the decrease in the speed of FTS due to the triggering of many times of Imax O.

(3) when there is a row link: if a row link is found during the execution of the FTS, the oracle executes the Single Block I FTS O in order to read the additional Imax O caused by the remaining rows.

14:16:34 SYS@ prod > show parameter multNAME TYPE VALUE---db_file_multiblock_read_count Integer 19parallel_adaptive_multi_user boolean TRUE14:17:28 SYS@ prod > col segment_name for a2014SYS@ prod 1808 SYS@ prod > select OWNER SEGMENT_NAME, SEGMENT_TYPE,EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents14:18:47 2 where segment_name='T1' AND owner='SCOTT' OWNER SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS -- SCOTT T1 TABLE 0168 8SCOTT T1 TABLE 1184 8SCOTT T1 TABLE 2 192 8SCOTT T1 TABLE 3 200 8SCOTT T1 TABLE 4 208 8SCOTT T1 TABLE 5 216 8SCOTT T1 TABLE 6 224 8SCOTT T1 TABLE 7 232 8SCOTT T1 TABLE 8 240 8SCOTT T1 TABLE 9 248 8SCOTT T1 TABLE 10 256 8SCOTT T1 TABLE 11 264 8SCOTT T1 TABLE 12 272 8SCOTT T1 TABLE 13 280 8SCOTT T1 TABLE 14 288 8SCOTT T1 TABLE 15 296 8SCOTT T1 TABLE 16 384 128OWNER SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BLOCK_ID BLOCKS -- SCOTT T1 TABLE 17 512 128SCOTT T1 TABLE 18 640 128SCOTT T1 TABLE 19 768 128SCOTT T1 TABLE 20 896 128SCOTT T1 TABLE 21 1024 12822 rows selected.Elapsed: 00:00:00.78

Db file sequential read

This wait event, which is also common in the actual production library, occurs when Oracle needs to read only a single block of data at a time. The most common cases are index access (in addition to IFFS), rollback operations, ROWID access to table data, reconstruction of control files, DUMP of file headers, and so on.

Sequential here does not mean that Oracle accesses data sequentially. Like db file scattered read, it means that read blocks of data are stored in memory in a continuous manner.

This wait event has three parameters:

The data block to be read by File#: is locked in the file number of the data file.

The starting block number to be read by Block#:.

Blocks: the number of blocks to read (this should be equal to 1).

Case study:

14:28:55 SYS@ prod > alter system flush buffer_cache;System altered.Elapsed: 00 SYS@ prod > select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event14:29:41 2 where upper (event) like'DB FILE%' EVENT TOTAL_WAITS AVERAGE_WAIT -db file sequential read 13991 .04db file scattered read 1637 .03db file single write 36.35db file parallel write 946 2.98db file parallel read 46 .48 Elapsed: 00 2.98db file parallel read 0015 00.0314 14 create index t1_ind on 26 46 SCOTT@ prod > create index t1_ind on T1 (id) Index created.14:28:30 SCOTT@ prod > select * from T1 where id=100014:28:48 2 ID- 1000 1000 1000Elapsed: 0000 SYS@ prod 00.0514 SYS@ prod > select event,TOTAL_WAITS AVERAGE_WAIT from v$system_event 2 * where upper (event) like'DB FILE%'EVENT TOTAL_WAITS AVERAGE_WAIT -- db file sequential read 13994 .04db file scattered read 1637 .03db file single write 36 .35db file parallel write 946 2.98db file parallel read 46 .48Elapsed: 00:00:00.03

14:29:58 SYS@ prod >

Data file information about the activity of Multi Block Imax O and Single Block Imax O:

14:38:22 SYS@ prod > select f. Fileholders, 2 f.name, 3 s.phyrds, 4 s.phyblkrd, 5 s.readtim, 6 s.singleblkrds, 7 s.singleblkrdtim, 8 (s.phyblkrd-s.singleblkrds) as multiblkrd, 9 (s.readtim-s.singleblkrdtim) as multiblkrdtim 10 round (s.singleblkrdtim / 11 decode (s.singleblkrds, 0,1, s.singleblkrds), 123) as singleblk_avgtim, 13 round ((s.readtim-s.singleblkrdtim) / 14 nullif ((s.phyblkrd-s.singleblkrds), 0), 15 3) as multiblk_avgtim 16 from v$filestat s V$datafile f 17 * where s.file# = f.file# FILE# NAME PHYRDS PHYBLKRD READTIM SINGLEBLKRDS SINGLEBLKRDTIM MULTIBLKRD MULTIBLKRDTIM SINGLEBLK_AVGTIM MULTIBLK_AVGTIM -- 1/ u01/app/oracle/oradata/prod/system01.dbf 16977 68027 419 12896 373 55131 46. 029. 001 2 / u01/app/oracle/oradata/prod/sysaux01.dbf 2041 3089 142 1894 134 1195 8. 071. 007 3 / u01/app/oracle/oradata/prod/undotbs1.dbf 11 11 4 11 4 00. 364 4 / u01/app/oracle/oradata/prod/users01.dbf 591 3355 8 359 7 2996 1. 019 0 5 / u01/app/oracle/oradata/prod/example01.dbf 10 14 0 9 0 5 0 0 0 6 / u01/app/oracle/oradata/prod/tbs1.dbf 4 4 0 4 0 0 0 7 / u01/app/oracle/oradata/prod/undotbs2.dbf 1815 1818 50 1812 48 6 2. 026. 333 8 / u01/app/oracle/oradata/prod/perftbs01.dbf 4 4 0 4 0 0 0 9 / u01/app/oracle/oradata/prod/tbs2.dbf 4 4 0 4 0 0 0 09 rows selected.select f.file# F.name, s.phyrds, s.phyblkrd, s.readtim,-- all read work information s.singleblkrds, s.singleblkrdtim,-- Single Block s.phyblkrd O (s.phyblkrd-s.singleblkrds) as multiblkrd,-- Multi Block I s.singleblkrdtim O (s.readtim-s.singleblkrdtim) as multiblkrdtim -- Multi Block s.singleblkrdtim O time round (s.singleblkrdtim / decode (s.singleblkrds, 0,1, s.singleblkrds), 3) as singleblk_avgtim,-- Single Block I hand O average waiting time (cs) round ((s.readtim-s.singleblkrdtim) / nullif ((s.phyblkrd-s.singleblkrds), 0)) 3) as multiblk_avgtim-Multi Block Ibank O average waiting time (cs) from v$filestat s, v$datafile f where s.file# = f.file#

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.