Troubleshooting Oracle Streams Performance Issues
(Doc ID 730036.1)
Last updated on APRIL 17, 2023
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 to 126.96.36.199 [Release 11.2]
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 10.1.0.5 [Release 10.1]
Oracle Database - Enterprise Edition - Version 188.8.131.52 to 184.108.40.206 [Release 11.1]
Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168 [Release 9.2]
Information in this document applies to any platform.
A performance issue will typically present itself in transactions
not appearing on the Apply sides tables as quickly as expected
thus there will be latency or lag present.
It may be unclear in the first instance whether the
issue is Capture , Propagation or Apply related - most likely it
will either be a Capture or Apply problem.
A heartbeat table is a simple way to monitor latency of Streams.
This Note explains what diagnostics are required by DBAs when
troubleshooting Streams processes. The details to be collected
should be forwarded to Oracle for further scrutiny if it is not
obvious what the issue may be.
In addition, pointers / notes are suggested to help identify where
a performance problem may lie.
Please also refer to :
The Oracle Streams Performance Tuning Best Practices: Oracle
Database 10g Release 10.2 which provides a useful guide to
performance analysis and identification.
This Notes explains what diagnostics are required by DBAs when troubleshooting the Streams processes
Last updated 13 February 2009
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document
|Details to collect|
Verify that Streams Best Practices are implemented (9.2, 10.1, 10.2, 11.1, 11.2)
Confirm that Recommended Patches are installed
Use STRMMON at both source and target to analyze processing
rates(default output) and top events
Run Healthcheck at source andtarget
High process CPU usage at the OS level
Generate AWR output for the time period
Check SQL connected with high level of buffer gets
Generate ASH output for the time period
Oracle Streams Performance Advisor (>= 11.1)
|Generate process stack information|
Investigate memory usage by Streams Processes
Optional 10046 / level 12 (sql trace)
Gather and upload details collected