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

Oracle Study-- Oracle wait event (3)

Db file parallel read

This is a very misleading wait event, which actually has nothing to do with parallel operations (such as parallel queries, parallel DML). This event occurs during database recovery, and when there are blocks of data that need to be recovered, Oracle reads them from the data file into memory in parallel for recovery.

This wait event contains three parameters:

Files: the number of files that need to be read for the operation.

Blocks: the number of blocks that need to be read for the operation.

Requests: the number of times the operation needs to be performed.

Case study:

11:06:16 SYS@ prod > conn scott/tigerConnected.11:06:19 SCOTT@ prod > insert into emp1 select * from emp1 286720 rows created.Elapsed: 00 rows created.11:08:07 SYS@ prod 00 select event,TOTAL_WAITS 02.0411 22 SCOTT@ prod > / 573440 rows created.11:08:07 SYS@ prod > r 1 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 4449 .04db file scattered read 1650 .02db file single write 36. 39db file async Ibank O submit 133 8.69db file parallel read 37. 19Elapsed: 00 SYS@ prod 0011 8.69db file parallel read 00.0211 20 SYS@ prod > r 1 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 7561 .03db file scattered read 1785 .02db file single write 36 .39db file async I/O submit 160 10.29db file parallel read 39 .19Elapsed: 00:00:00.02

Db file parallel write

This is a background wait event, which also has nothing to do with the parallel operation of the user. It is generated by the background process DBWR, which occurs when the background process DBWR writes dirty data to disk.

DBWR writes dirty data in batches to the corresponding data files on disk in parallel, and DBWR will have this wait event before the batch job completes. If only this wait event does not have much impact on the user's operation, when the free buffer waits wait event occurs, the available space in memory is insufficient, and this will affect the user's operation, such as affecting the user to read dirty data blocks into memory.

When a db file parallel write wait event occurs, the wait can be mitigated by enabling the operating system's asynchronous Imax O. When using asynchronous iCandle O, DBWR no longer needs to wait until all data blocks are written to disk, it only needs to wait until the data is written to a percentage before it can proceed with subsequent operations.

This wait event has two parameters:

Requests: the number of times the operation needs to be performed.

Timeouts: the timeout for waiting.

Case study:

1 、 Close ASYNC I/O:11:25:47 SYS@ prod > show parameter syncNAME TYPE VALUE-- disk_asynch _ io boolean FALSEtape_asynch_io boolean TRUE11:25:53 SYS@ prod > select event TOTAL_WAITS,AVERAGE_WAIT from v$system_event11:25:58 2 where upper (event) like'DB FILE%' EVENT TOTAL_WAITS AVERAGE_WAIT -db file sequential read 2036 .02db file scattered read 844.01db file single write 27.69db file parallel write 38 1.692 、 The user performs transaction operations 11:24:02 SCOTT@ prod > conn scott/tigerConnected.11:26:19 SCOTT@ prod > begin11:26:38 2 for i in 1.. 100000 loop11:26:38 3 execute immediate 'insert into T1 values (' | | I | |')' 11:26:38 4 end loop;11:26:38 5 end;11:26:38 6 / 11:24:06 TOM@ prod > conn tom/tomConnected.11:26:47 TOM@ prod > begin11:26:57 2 for i in 1.. 100000 loop11:26:57 3 execute immediate 'insert into scott.t1 values (' | | I | |')'; 11:26:57 4 end loop;11:26:57 5 end 11:26:57 6 / 11:22:41 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 3216 .02db file scattered read 846.01db file single write 27 .69db file parallel write 67 2.78Elapsed: 00:00:00.023 、 Solution 11:22:42 SYS@ prod > show parameter syncNAME TYPE VALUE---disk_asynch_io Boolean TRUEtape_asynch_io boolean TRUE11:22:43 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 11801 .05db file scattered read 1920 .03db file single write 54. 35db file async Ibind O submit 266 7.78db file parallel read 39. 19'db file parallel write 'wait for the event to disappear!

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.