Analyzing & Interpreting AWR Report

Analyzing and Interpreting AWR from pasalapudi

Top 5 Timed Foreground Events - Streams miscellaneous event

Streams miscellaneous event

The Streams miscellaneous event will be renamed to "Waiting for additional work from the logfile" to better describe the activity from Oracle release 11.2.0.2.x See detail in BugDB 12341046 for more information

In AWR (Automatic Workload Repository) you may see a Streams miscellaneous event as a top resource consumers in the wait class OTHER , when running Oracle Goldengate (OGG) EXTRACT with the parameter TRANLOGOPTIONS DBLOGREADER set.

As of OGG 11.1.1.0.0 onwards the extract process can use the OCIPOGG logreader module if the TRANLOGOPTIONS DBLOGREADER parameter is set in the extract parameter file.

NOTE :  to use the logreader functionality your Oracle release must also be 10.2.0.5+ or 11.2.0.2+

CAUSE

The cause of the issue has been identified in unpublished Bug 12341046. It is caused by incorrectly associating the wait for a redo log with the "Streams miscellaneous event" rather than the IDLE wait event "Streams capture: waiting for archive log"

Solution

 To confirm that this is the case you can do the following

1. Get the operating system pocess ids of the processes which are waiting on the  'Streams miscellaneous event'

connect / as sysdba  

set markup html on

spool processes.html 

select s.sid, s.serial#, s.process, p.spid, p.pid, p.program from v$session s, v$session_wait sw, v$process p where s.sid=sw.sid and sw.event = 'Streams miscellaneous event' and s.paddr=p.addr;

spool off

2.  Attach to the processes and generate sql trace 

oradebug setospid <spid>; 

oradebug TRACEFILE_NAME 

oradebug event 10046 trace name context forever, level 12 

--Trace for 5 minutes 

oradebug event 10046 trace name context off

In the tracefile for processes associated with the GG Extract process you should see entries of the form 

WAIT #0: nam='log file sequential read' ela= 2823 log#=0 block#=1 blocks=1 obj#=-1 tim=8507422526613 <<< READING the redo logs

WAIT #0: nam='log file sequential read' ela= 221 log#=0 block#=235 blocks=2  obj#=-1 tim=8507422526918<<< READING the redo logs 

WAIT #0: nam='log file sequential read' ela= 233 log#=0 block#=235 blocks=2 obj#=-1 tim=8507422527161<<< READING the redo logs 

WAIT #0: nam='Streams miscellaneous event' ela= 488302 TYPE=16 p2=0 p3=0 obj#=-1 tim=8507423015480 

TYPE=16 confirms that we are waiting for redo and indicates that the fix for unpublished Bug 12341046 will resolve the issue

Bug 12341046 is fixed in Oracle RDBMS 12.1 onwards

Exadata Wait Event - Cell Statistics Gather

cell statistics gather

This wait event appears when a select is done on the V$CELL_STATE , V$CELL_THREAD_HISTORY , or V$CELL_REQUEST_TOTALS tables. During the select, data from the cells and any wait events are shown in this wait event.

cell statistics gather ==> appears when a select is done on the V$CELL_STATE, V$CELL_THREAD_HISTORY, or V$CELL_REQUEST_TOTALS tables. During the select, data from the cells and any wait events are shown in this wait event.

Exadata Wait Event - Cell Smart Table Scan

cell smart table scan

This wait event appears when the database is waiting for table scans to complete on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.

cell smart table scan ==> appears when the database is waiting for table scans to complete on a cell

Exadata Wait Event - Cell Smart Restore from Backup

cell smart restore from backup

This wait event appears when the database is waiting for the completion of a file initialization for restore from backup on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.
 

cell smart restore from backup ==> Appears when the database is waiting for the completion of a file initialization for restore from backup on a cell

Exadata Wait Event - Cell Smart Index Scan

cell smart index scan

This wait event appears when the database is waiting for index or index-organized table (IOT) fast full scans. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.

cell smart index scan ==> Appears when the database is waiting for index or index-organized table (IOT) fast full scans

Exadata Wait Event - Cell Smart Incremental Backup

cell smart incremental backup

This wait event appears when the database is waiting for the completion of an incremental backup on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.

Exadata Wait Event - Cell Smart File Creation

cell smart file creation

This wait event appears when the database is waiting for the completion of a file creation on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell compared to the rest of the cells.

cell smart file creation ==> Appears when the database is waiting for the completion of a file creation on a cell

Exadata Wait Event - Cell Single Block Physical Read

cell single block physical read

This wait event is equivalent to db file sequential read for a cell. The P1 , P2 , and P3 columns in the V$SESSION_WAIT view for this event identify the cell hash number, disk hash number, and the number of bytes processed during the I/O read operation.

cell single block physical read ==> equivalent to db file sequential read for a cell

Exadata Wait Event - Cell Multiblock Physical Read

cell multiblock physical read

This wait event is equivalent to db file scattered read for a cell. The P1 , P2 , and P3 columns in the V$SESSION_WAIT view for this event identify the cell hash number, disk hash number, and the total number of bytes processed during the I/O read operation.

cell multiblock physical read ==> Equivalent to db file scattered read for a cell