My Oracle Support Banner

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.