Benchmarking Oracle Streams

Oracle Streams is a popular free alternative to Oracle RAC for failover and disaster recovery, but it is important to note that Oracle Streams is not a panacea, and it’s not appropriate for all databases.  Oracle Streams has no licensing costs (RAC costs extra) and it is less complex to configure than a RAC database.  This disadvantage over RAC is that there is no continuous availability, update collisions may occur, replication is not instant, and application failover is usually done manually.

When using Oracle Streams for n-way replication it is critical to implode each instance with DML to determine the point where Oracle will experience contention.

The paper below describes a DML stress test on Oracle 10g Streams, using the Quest Benchmark factory software 

We also need to understand the limits of cross-replicating Oracle systems with Oracle Streams.  For more information on Streams vs. RAC for failover, see my notes here:

• Oracle failover options dataGuard RAC Streams

• 10g New Features in Oracle10g

• Oracle high availability rac data guard streams

• Oracle multi-master replication with Oracle Streams

• Oracle10g New Features RAC Architecture Large Pool Streams Pool

• Oracle features - standard edition vs. enterprise edition SE EE

• Oracle RAC Extended Distance Clusters

Oracle Streams Stress test Executive Summary

The purpose of this DML stress test was to implode an Oracle instance with insert transactions and observe the breaking-point as high-volumes of data replicate to the to a remote server using n-way Oracle Streams. 

This Oracle Streams stress test was a tremendous success and shows these exact areas where the database will experience contentions at high loads.   The test also revealed maximum rates (per server) for I/O, including disk reads, disk writes and maximum updates per second.

• 400 writes per second
• 7,000 disk reads per second
• 12,000 updates per second
• 19,000 logical buffer reads per second
• UGA memory access at 18 million per second

We now know the areas where we will have future problems as system update activity increases:

• We need to monitor the log switch interval and re-size the online redo logs if switches occur more frequently than every 15 minutes.
• We need to monitor Streams spills to disk and increase the streams pool to ensure that we never have disk enqueues.
• Hot tables may eventually have to be moved from ASSM to standard, multiple freelists.

Oracle Streams Stress Test Overview

The subject of this test is an Oracle server that is using Oracle10g 2-way Streams replication across a 90-mile high-speed interconnect.  Figure 1 shows a model of the Oracle server architecture:

Figure 1 – The Oracle server architecture

This test imploded the Oracle instance with insert statements in order to locate the system bottleneck, focusing on the ability of Oracle to replicate the data across the Oracle Streams interface to the remote server.  The default block size is 8k with a special 32k tablespace for the indexes.

This test used the Quest Benchmark Factory to generate the insert statements, not using bind variables.

Predictions

As DML transactions increase we would expect the stress to increase on those instance components associated with DML:

• Redo log contention – We expect to see very-frequent log switches and waits on redo
• Streams replication enqueues – We expect to see the Oracle Streams back-up and enqueue (to disk).
• Object contention – As inert rates increase we expect to note segment contention on the bitmap freelists (this test has tablespaces with ASSM).
• RAM exhaustion – We expect to see contention within the shared_pool and db_cache_size.

Streams Stress Experiment Results

This test was a tremendous success and shows these exact areas where the database will experience contentions at high loads and also reveals maximum rates for I/O, including disk reads, disk writes and maximum updates per second.

The AWR report from the stress test is in Appendix A.  We noted excellent rates for data block access:

• 400 writes per second
• 7,000 disk reads per second
• 12,000 updates per second
• 19,000 logical buffer reads per second
• UGA memory access at 18 million per second

During the heavy load on the system, we observed that the redo and Steams became system bottlenecks:

Redo Log contention

For this load level the size of the redo logs is insufficient, at the very start we were doing 197 switches per hour at the end 205.

For recoverability we want to keep the redo size at the default value (512k) so that we can get a few log switches per day.  If we make them too large we risk loosing multiple days work.

Object Contention

Our biggest stress point for blocks appears to be row lock waits on the indexes. This was probably because we were doing a high volume of updates along with the inserts, in normal operation we probably won't see this mix at this stress level. We used ASSM and noted issues with the initrans value.  We may want to switch to manual for some tables if we expect this level of stress.

At max stress we were also seeing sequence and object high watermark enqueues. The sequence enqueues probably indicate we need to increase the caching level for the sequences (I'll wager they are at the default of 20, we may wish to bump that up to 100 or so). The HWM enqueues are because we started expanding the table quickly...we may want to look at the tablespace settings for extent size.

We also note that the bitmap freelists (as implemented via Automatic Segment Storage Management – ASSM) become overloaded, resulting in buffer busy waits:

Streams Contention

As predicted, we noted enqueue contention within Oracle Streams as transactions backed-up to be replicated to the remote server:

At this level of activity streams is spilling, this is causing it to not keep up as well as it should (spilling means going to disk) we need a larger streams pool. At maximum (according to the report) we will need about a 400 megabyte streams pool. We may also want to look at multiple streams processes.

We also see high CPU activity associated with Streams during the high update period 

Lessons Learned
 

• Streams pool size and multiple streams processes (see Mike for advice)
• Increase sequence caching to 100
• Review extent sizes for the most active tables (fbi_donald, fbi_establishment)

We now know what will happen as the load expands and we can prepare for future high-stress conditions:

• We need to monitor the log switch interval and re-size the online redo logs if switches occur more frequently than every 15 minutes.
• We need to monitor Streams spills to disk and increase the streams pool to ensure that we never have disk enqueues.
• Hot tables (fbi_donald) may eventually have to be moved from ASSM to standard, multiple freelists.

Appendix A - AWR Report