Oracle databases do not run in a vacuum and it is the job of the DBA to know the thresholds where their Oracle database becomes dependent on external factors such as RAM, CPU and Network latency. 

Also see my notes on Oracle benchmarking testing.

A valid benchmark stress test  will burden the database with transactions until we see a pronounced drop-off in response time the top transactions per second (TPS). This stress test will also reveal the system bottleneck that emerges under heavy load:

A typical Oracle response-time drop-off

In this example we see a dramatic increase in response time as we pass 11 transactions per second.  Once we have "broken the systems (by exceeding the maximum transaction rate), we then correlate the increase with various internal Oracle metrics (wait events from ASH, AWR reports) and external metrics (CPU, RAM, disk and network enqueues).

A Methodology for Oracle Server Stress Testing

The goal of an Oracle server stress test is to place an increasing load on the Oracle instance until the instance becomes dependent on an external resource.  Of course, an applications may have many patterns of SQL access and the bottleneck will change over time as access patterns change, but the primary goal of server stress testing is to determine the "threshold" when the server will "break", as evidenced by hardware-related enqueues and/or internal Oracle bottlenecks.

This is an actual case study using a pair of Oracle servers with the following resources

• Servers - Two Oracle servers connected via Oracle Streams n-way replication
  • CPU's - Dual Intel 64-bit processors
  • RAM - 16 gigabytes of RAM

• Disk - Six 72 gigabyte disks using RAID 10

• Network - TCP/IP using Oracle*Net with n-way Oracle Streams replication

• Oracle Release - Oracle 10g release 2, Enterprise Edition

• Operating System - Red Hat Linux Advanced Server

• Application - Oracle Application Express (formerly HTML-DB)

• Client Software - Microsoft Internet Explorer

Figure 1 shows a model of the Oracle server architecture:

Figure 1 - The Oracle server architecture

This system architecture provides Oracle Streams cross-replication for two production servers in widely-separated locations (> 100 miles apart) with high-speed interconnects.  This approach has the dual benefit from Oracle disaster recovery, and also allows both servers to be used to process transactions.  Since all transactions are using APEX, redirecting traffic is as simple as changing the domain name in the requesting URL. 

Should one server location fail, end-user simply clicks-into the surviving servers at the other locations and continues processing, as normal.

The Oracle Stress testing Steps

To be realistic, the transaction load should closely parallel the real-world transactions, and this benchmark will use the Goal benchmark framework from the "benchmark factory".  There are several phases in preparation for the benchmark:

1. SQL extraction - We extract a representative sample of actual SQL and DML from the production database.
   

2. SQL preparation - We create PL/SQL wrappers around the SQL to pass-in valid values for the host variables, using data from the application tables.
   

3. Load Preparation - Benchmark factory allow us to submit up to 2000 transaction threads per TNS client, however the practical limit is based on network bandwidth of the client, for example 60 users tends to saturate a 100Mbit line.  We will configure 2 clients, installing BF Agent software and TNS in order to set-up a load of 200 transaction threads.
   

4. Test Execution - We then start the server data capture (vmstat, iostat) and take an AWR snapshot.  After a pre-designated interval we will step up the user load by 10 users at each step.
   

5. Post-hoc Analysis - We plot the overall response_time and correlate the drop-off with internal Oracle metrics (e.g. wait events) and external server metrics (CPU enqueues, RAM swapping).

Let's take a closer look at each Oracle stress testing step.

1. SQL Extraction

To simulate a full production load we start by extracting SQL and DML from the live production application.  You can gather the most resource intensive SQL directly from the library cache based on any of these criteria:

• Disk_reads - This will extract the SQL that typically exceeds the data buffer capacity, generally non-repeating data fetches that are unlikely to be cached.
• Executions - This is a good metric because it simulates the actual load on the database
   
• Buffer_gets - This measures logical IO and CPU and memory stress
• Number of Executions - This tests the most used transactions

We must remember that some of the most frequently-executed SQL will be against data blocks that will already be cached (high consistent gets) whereas SQL with high disk_reads will generate a load that is disk I/O intensive (high physical reads). 

If we wanted to extract the top SQL by executions we could issue the following command:

2. Preparing the SQL for Benchmark Factory

For application SQL with the PL/SQL into clause, we must replace the PL/SQL into clause syntax with its generic SQLPLUS form.  If you want your test to keep the PL/SQL into clause, you can build anonymous PL/SQL blocks for them and execute them as prepared statements.  For our purposes, we only need the data transferred back to the calling machine, so we alter the SQL to remove the PL/SQL into clause:

Providing data values for the benchmark

There are two ways to generate random testing data form the SQL that we have extracted:

1. Random Values - We can use the benchmark factory functions $BFRandList and $BFRandRange to generate a random lists of data values.  We can control all ranges of numeric values with valid ID ranges and the list substitutions with valid values.

2. Application Values - We can extract actual host variable data from the application tables. By examining tables and actual values we can provide a realistic list of values to feed into the functions. For nearly all of the SQL selected for this test there were a limited number of values with most selections being based on integer value keys easily generated using the $BFRandRange function.

Random Range Values

Here we see an example of SQL using the $BFRandRange function to generate random values for host variables:

Application Values

By carefully selecting our SQL we can utilize selection values easily loaded into the $BFRandList function.  To use actual application data you would need to create a function that would return random values to the SQL statement, however, this would generate additional load on the database possibly causing skewed or invalid results.

We are now ready to configure the client machines, placing the TNS layer and the Benchmark Factory software that will load-down the database.  Now that we have the SQL ready we can pursue the set-up on the BF clients.

3. Load Preparation

Stressing the database server involves a set-up of multiple Oracle*Net clients.  The benchmark factory allows you to install an "agent" on an Oracle*net client machine and launch the SQL from many client PC's.

How many users do we want to simulate? Up to 2000 or so (up to CPU or network bandwidth limits) can be done from a single agent process, more than that we will need to install a second or more agents to submit requests through. Any windows based system can be used as an agent, but it must have Oracles network stack installed.

4. Test Execution & Capturing Results

Here is an example of the results from a "Goal" test where the goal was to push the system to where the TPS dropped below the previous two runs.

The Benchmark Factory collects all transaction data and places it inside a spreadsheet for easy access and summarization:

Experiment Predictions

In our typical 64-bit Oracle system with 32 gigabytes of RAM we would predict results as follows:

• The RAM data buffer cache (14 gigs in each of the two servers) will be sufficient to cache the working-set of frequently-referenced data blocks and disk enqueues will not be the bottleneck.

• The low-DML of this system and the fact that OAE does whole-screen data map-outs in one Oracle*Net trip, we do not expect to see network latency being responsible for the response-time drop-off.

Because of the architecture and the nature of transactions against this system we expect that the main source of contention in this system should be CPU, and Oracle latch-elated wait events.  We should start seeing CPU enqueues (in our case, the run queue exceeding "2" on each server) at about the same transaction rate when total response time declines precipitously.  We predict that this configuration will bottleneck on CPU at approximately 30 transactions per second and that RAM, network and disk enqueues will not be on the bottleneck.

Our Results

For the initial test run we utilized only a single Node as the full scale replication was not yet implemented. In the test we set up a run from 1-100 users executing the SQL transactions from Appendix A, in this phase of testing we will not test the replication.

In this goal test the criteria for ending the test was for the transactions per second at the current user load to fall behind that of the two previous runs. The graph in Figure 4 shows the TPS results.

 Figure 4 - The Preliminary Test TPS Results

The results show that at 60 users the system TPS seems to peak in this configuration, but what about other signs of strain in the system? Is it CPU, Disk, or Network related? Look at Figure 5 for CPU usage results.

Figure 4 - The Preliminary Test TPS Results

Note that the user (us) CPU usage peaks at around 65 percent; with System (sy) peaking at only 5 percent this still indicates that we have 30 percent CPU idle time. Clearly based on Figure 5 we do not have CPU stress. A secondary indicator of CPU stress is runqueue length. Figure 5 shows the plot of runqueue for the same time period.

Figure 5 - The Preliminary Test Runqueue Results

As you can see the runqueue has rarely rises above 4, this indicates that CPU stress was not a factor. Next let's examine disk stress results. Look at Figure 6 showing Disk Writes.

Figure 6 - The Preliminary Test Disk Write  Results

As can be seen from Figure 6, other than a single large burst of greater than 30,000 IO in a 10 second interval near the mid-range of the test, disk IO was well within the disks operational limits. Figure 7 shows that Disk Reads were also well within specifications.

Figure 7 - The Preliminary Test Disk Read Results

The system reads are clearly below any stress levels that would indicate the disks were the cause of the low TPS values. This is also supported when we examine the system wait statistics in Figure 8.

Figure 8 - The Preliminary Test Wait Results

As can be seen from examining Figure 8, other than a peak that corresponds to the reads on disk hbc, system waits are not causing the TPS drop off. The final possible bottle necks are memory and network. Examination of the AWR report for the test period shows some interesting network statistics. Look at Figure 9 for an excerpt from the applicable AWR report from the test run.

Figure 9 - The Preliminary Test AWR Results

What the AWR report is showing is that we are seeing some shared pool stress and network stress. Review of alert log entries shows the database was also getting ORA-04031 errors, indicating the shared pool was becoming fragmented. It was decided that the CURSOR_SHARING parameter would be left at EXACT; as a result this caused numerous not sharable SQL statements to be loaded into the shared pool causing the ORA-04031 errors.

When we retest using the dual nodes with replication, we will turn on the CURSOR_SHARING to reduce memory thrashing and parsing. In addition we will make sure the clients used are on separate network strands.

Conclusions for Phase 1 Testing

Phase one showed that even with limited network bandwidth the system easily supported 50-60 users on a single node, more than is anticipated than will be needed during normal operations. With better memory parameters and better network bandwidth it is anticipated that the nodes will easily support 80-100 users.

Disk Load - other than a single large burst of greater than 30,000 IO in a 10 second interval near the mid-range of the test, disk IO was well within the disks operational limits.

CPU - The user (us) CPU usage peaks at around 65 percent; with System (sy) peaking at only 5 percent this still indicates that we have 30 percent CPU idle time.

Memory and Network - Examination of the AWR report for the test period shows some interesting network statistics.  As predicted, the server became CPU-bound as active users rose above 60:

Other Oracle benchmark notes: