Oracle Study--oracle Wait event (5)

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 (for example, checkpoint occurs).
When this wait event is obvious, it is important to consider whether the number of data files in the database is too large, causing Oracle to take a long time to do all the file header update operations (checkpoint).
This wait event has three parameters:
file#: The file number of the data file that contains the data block that needs to be updated.
block#: The number of data blocks that need to be updated.
Blocks: The number of data blocks that need to be updated (should normally be equal to 1).

< Span style= "line-height:25px;" > case study:

15:03:26 [email protected] 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                                            &Nbsp;      2093          .01db  file scattered read                                                      833          .02db file single  write                                                         27          .28db file parallel  Write                                                       5         17.4815:03:51 [email protected] prod>alter system checkpoint; System altered.15:03:35 [email protected] prod>select event,total_waits,average_ Wait from v$system_event  2* where upper (event)  like  ' db file% ' event                                                            &nbSp;  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 data blocks directly into the PGA instead of the SGA, and the data that is read is usually the private data of the session, so there is no need to put it into the SGA as a shared data because it makes no sense. This data is usually from the temporary segment of the data, such as a session of the SQL sorting data, parallel execution of the data generated in the middle of the process, and the hash Join,merge Join produced by the sorting data, because this data only for the current session of SQL operations have meaning, So there's no need to put it in the SGA.
when the direct path read wait event occurs, it means that there is a large amount of temporary data generated on the disk, such as sorting, parallel execution, and so on. Or it means that there is not enough free space in the PGA.
This wait event has three parameters:
Descriptor Address: A pointer to a direct read I/O that the current session is waiting for.
one of the oldest I/O block addresses in first Dba:descriptor address.
Block Cnt:descriptor The valid number of buffer involved in the 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 the disk file without the SGA.
This situation usually occurs when:
sort using temporal table space (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/O that the current session is waiting for.
one of the oldest I/O block addresses in first Dba:descriptor address.
Block Cnt:descriptor The effective number of buffer involved in the address context.

< Span style= "line-height:25px;" > case study:

15:37:17 [email protected] prod>  1* select * from t1  Order by 1600000 rows selected. Elapsed: 00:00:04.35execution plan----------------------------------------------------------Plan  hash value: 2148421099------------------------------------------------------------------------ -----------| 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|       |   276     (2) | 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 [email protected] 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           .03direct path read temp                                                      1746            0direct path  write temp                                                       63          .98elapsed: 00:00:00.0415:37:31  [email protected] prod>

This article is from the "Tianya blog," Please make sure to keep this source http://tiany.blog.51cto.com/513694/1536003