Troubleshooting Oracle Streams Performance Issues
(Doc ID 730036.1)
Last updated on MARCH 13, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 9.2.0.8 [Release 9.2]Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.
Purpose
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.
https://www.oracle.com/technetwork/database/availability/maa-wp-10gr2-streams-performance-130059.pdf
This Notes explains what diagnostics are required by DBAs when troubleshooting the Streams processes
Troubleshooting Steps
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
| Purpose |
| Troubleshooting Steps |
| Prerequisites |
| Details to collect |
Troubleshooting detail |
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 |
| References |