Misleading wait event names clarified in V$EVENT_NAME

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.