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