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

Oracle Study-- Oracle wait event (5)

Db file single write

This wait event usually occurs only in one case, when Oracle updates the header information of the data file (such as when a Checkpoint occurs).

When this wait event is obvious, you need to consider whether the number of data files in the database is so large that it takes a long time for Oracle to checkpoint all the headers.

This wait event has three parameters:

File#: requires the file number of the data file where the updated data block is located.

Block#: requires an updated block number.

Blocks: the number of blocks that need to be updated (usually equal to 1).

Case study:

15:03:26 SYS@ prod > select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event15:03:31 2 where upper (event) like'DB FILE%' EVENT TOTAL_WAITS AVERAGE_WAIT -db file sequential read 2093 .01db file scattered read 833.02db file single write 27.28db file parallel write 5 17.4815 0315 SYS@ prod > alter system checkpoint System altered.15:03:35 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 2673 .01db file scattered read 833.02db file single write 36 .55db file parallel write 7 14.73Elapsed: 00:00:00.01

Direct path read

This wait event occurs when the session reads the data block directly into the PGA rather than the SGA, which is usually private to the session, so it doesn't need to be put into the SGA as shared data, because it doesn't make sense. This data usually comes from temporary data, such as the sorted data of SQL in a session, the data generated during parallel execution, and the sorted data generated by Hash Join,merge join, because these data are only meaningful to the SQL operation of the current session, so they do not need to be put in the SGA.

When the direct path read wait event occurs, it means that a large amount of temporary data is generated on the disk, such as sorting, parallel execution, and so on. Or it means that there is not enough free space in PGA.

This wait event has three parameters:

Descriptor address: a pointer to a direct read I hand O that the current session is waiting for.

First dba: the oldest I Zero block address in descriptor address.

Block cnt: the number of valid buffer involved in the descriptor address context.

Direct path write

This wait event is the opposite of direct path read, where the session writes some data directly from the PGA to a disk file without going through the SGA.

This usually occurs when:

Sort using temporary tablespaces (out of memory)

Direct loading of data (loading data using append)

Parallel DML operations.

This wait event has three parameters:

Descriptor address: a pointer to a direct I Descriptor address O.

First dba: the oldest I Zero block address in descriptor address.

Block cnt: the number of valid buffer involved in the descriptor address context.

Case study:

15:37:17 SCOTT@ prod > 1 * select * from T1 order by 1600000 rows selected.Elapsed: 00:00:04.35Execution Plan---Plan hash value: 2148421099 Murray- -| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |-- -- | 0 | SELECT STATEMENT | | 838K | 10m | | 4260 (1) | 00:00:52 | 1 | SORT ORDER BY | | 838K | 10m | 16m | 4260 (1) | 00:00:52 | | 2 | TABLE ACCESS FULL | T1 | 838K | 10m | | | | 00:00:04 | 00:00:04 |-Note--dynamic sampling used for this statement (level=2) Statistics-- | -7 recursive calls 3 db block gets 1355 consistent gets 1823 physical reads 0 redo size 10809270 bytes sent via SQL*Net to client 440512 bytes received via SQL*Net from client 40001 SQL*Net roundtrips to / from client 0 sorts (memory) 1 sorts (disk) 600000 rows processed 15:36:39 SYS@ prod > select event TOTAL_WAITS AVERAGE_WAIT from v$system_event 2 * where upper (event) like 'DIRECT%'EVENT TOTAL_WAITS AVERAGE_WAIT -- direct path read 154.03 direct path read temp 1746 0direct path write temp 63 .98 Elapsed: 00VOR 00VOR 00.0415UR 37R 31 SYS@ prod >

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.