By Franck Pachot
The oracle wait event names were originally implemented for the oracle rdbms developers and are now use by the database users to troubleshoot performance issues. The consequence is that the name may be misleading because they have a meaning from the internal point of view. Here is some clarification about them.
In 12c the clarification is easy because we have a new DISPLAY_NAME column in the V$EVENT_NAME view:
SQL> select wait_class,name, display_name from v$event_name where display_name != name order by 1,2; WAIT_CLASS NAME DISPLAY_NAME -------------- ----------------------------------- ---------------------------------------------- Administrative concurrent I/O completion online move datafile IO completion Administrative datafile copy range completion online move datafile copy range completion Administrative wait for possible quiesce finish quiesce database completion Commit log file sync commit: log file sync Configuration log buffer space log buffer full - LGWR bottleneck Idle LGWR real time apply sync standby apply advance notification Other DFS db file lock quiesce for datafile offline Other Image redo gen delay redo resource management Other datafile move cleanup during resize online move datafile resize cleanup System I/O control file sequential read control file read System I/O control file single write control file write System I/O db file parallel write db list of blocks write System I/O log file parallel write log file redo write System I/O log file sequential read log file multiblock read System I/O log file single write log file header write User I/O db file parallel read db list of blocks read User I/O db file scattered read db multiblock read User I/O db file sequential read db single block read User I/O db file single write db single block write
For long we know the misleading ‘db file sequential read’ which is what we call ‘random reads’ from storage point of view and ‘db file scattered read’ that is what we call ‘sequential reads’ from storage point of view. The DISPLAY_NAME clarifies everything: single block reads vs. multiblock reads.
‘db file parallel read’ is a batch of random reads, used by prefetching for example, which reads multiple blocks but non contiguous.
‘db file parallel write’ is similar, for DBWR to write a batch of blocks. The DISPLAY_NAME clarifies everything: ‘db list of blocks’.
‘log file parallel write’ is ‘parallel’ only because you can have multiplexed files. DISPLAY_NAME is less misleading with ‘log file redo write’.
The ‘log buffer space’ has a DISPLAY_NAME that is more focused on the cause: ‘log buffer full – LGWR bottleneck’
You can look at the others where DISPLAY_NAME is very clear about the operation: ‘online move’ for some operations on files, ‘commit’ for the well know log file sync…
Of course they are also described in the Database Reference documentation.