Load Testing Oracle Database: Some Insights

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Sep 7, 2017

Load Testing Oracle Database: Some Insights

The Preparation Phase is pivotal to the whole Load testing exercise. More caution needs to be exercised in analysis and conclusion especially when the load testing system is not a 1:1 replica of PROD.

Introduction:

The post broadly discusses about Oracle load testing, also referred to as performance testing. The main motive is to plan for testing the database under various representative load conditions. 

Sometimes just functional/regression testing is not sufficient for validating the new code changes. Especially when you are trying to make a bulk of code changes, or doing a DB/application upgrade or changing any hardware. It makes a lot of sense to simulate the production load on the test environment and see how it behaves under various load conditions. 

Same condition holds when a particular product is going live or a new country localization is being implemented in case of an e-business suite.

There are four main phases in a load testing exercise

1) Prepare

2) Simulate

3) Capture Metrics

4) Analyze 

Let’s discuss each phase in detail. 

  • Prepare:

First identify the tool/procedure for conducting the test. There are many tools available both as an open source and with license. 

Some of these include:

  • Mercury LoadRunner -Requires license
  • Borland SilkPerformer -Requires license
  • HammerDB -Open source
  • Swingbench -Open source
  • Oracle Application Testing Suite (OATS)-Requires license

Or load can be simulated with custom developed scripts. 

Collect the Information:

Collect the information about the production system and the load test system. You will need to know the OS version, patch level, hardware resources (CPUs, RAM and Data Storage device details etc.) 

Determine the Metrics to be Measured:

List out the metrics to be measured during the load testing exercise.

Following is the sample list for metrics

  • CPU Usage
  • Load Average
  • Memory Usage
  • SQL Response Time
  • Throughput
  • Hits per Second etc... 

OS watcher can be configured on the load testing system to get granular details of an OS and the hardware metrics.

Oracle Automatic Workload Repository (AWR) needs to be configured, if not in place already. 

Determine the Core Test Areas:

It depends on the type of Application being hosted by the Database:

  • For an E-Business Suite database the month end period is a major event, you can include this activity during simulation load.
  • For an EBS Environment with Manufacturing, ASCP plan run is a major event which needs to be tested.
  • Payroll run of the Application can be simulated if it is being used in the HR module.
  • In case of core banking database EOD (End of Day) processing to be simulated that accounts for most of the load. 

All the above cases are more like batch processing scenarios. Along with them transaction processing windows also need to be included when more concurrent users are on the system. 

Create a Test Plan:

  • Include at least two different phases for the test, a baseline and a post upgrade phase.
  • The baseline is used to measure the current environment and validate it against the actual production environment. The post-test is a test of the environment after the upgrade is made.
  • By using the baseline and the post-test, analysis can be made on the impact of the upgrade.
  • Load test procedures, including how to start the load test and how to evaluate test progress for each test.
  • List of the tests to be conducted.
  • Number of test run iterations for each test. For example run the same test three times to get a more accurate average measure of transaction response time.
  • A report needs to be created for each of the load test phases. This report should include information on results from each test run, metrics and other collected information. A report template should be created.
  • Post test actions and checklist. Actions, if any, needed to be executed after load testing. These can include steps to move the database to the pre-load testing state. 

Some considerations:

You need to consider the following issues if the load test system is not 1:1 scale of PROD

  • Behavior in load testing is different than in production.
  • Errors shown in production don't reproduce in load testing.
  • Transaction performance is different between load testing and production.
  • Other factors, not listed above, can also influence the load testing and therefore the results and analysis.
  • Simulate:

By now you have already finalized the test plan and tools to be used.

Next you need to install/setup the necessary tools and get ready to simulate. 

Load can be simulated in different ways based on the tools being used. It is as simple as running a few select DDL/DML queries on a  set of table or as complex as replicating a series of batch processing programs like ASCP run, Payroll run or EOD processing. 

First capture/replay the baseline load from Source system on to a load testing. Then move on to replaying the representative load. 

Let’s touch upon HammerDB and OATS/Oracle Database Replay. 

  • HammerDB allows you to capture the load on the Source system using Oracle trace files, they can be moved to the Load test system and can be replayed on it.
  • Following document has the detailed process.

http://www.hammerdb.com/hammerdb_oracle_trace_replay.pdf

  • Application Testing Suite (OATS) is a comprehensive, integrated testing solution that ensures the quality, scalability, and availability of your Web applications, Web Services, packaged Oracle Applications and Oracle databases.
  • Oracle Database Reply is another option for achieving load testing. It can be used from DBMS_WORKLOAD_CAPTURE API’s or Enterprise Manager. However there are some licensing implications to use these tools.

Using API’s:

DBMS_WORKLOAD_CAPTURE package is part of the Oracle Real Application Testing option. DBMS_WORKLOAD_REPLAY package is part of the Oracle Real Application Testing option. Use of the function DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT() also requires a license of Oracle Diagnostics Pack.

  • The SQL Performance Analyzer feature can also be accessed from SQLPLUS interface. 

DBMS_SQLPA package is a part of Oracle Real Application Testing option

The Oracle Real Application Testing license is required for both capture and replay

Systems for Database Replay is charged by the total number of CPUs on those system.

Refer the Oracle MOS note for detailed steps for Capture, Replay using API’s: 

Capture using API’s - https://docs.oracle.com/database/121/RATUG/GUID-7AB9889A-6F05-41D3-8EA1-F4BEEC507BDE.htm#RATUG122

Replay using API’s - https://docs.oracle.com/database/121/RATUG/GUID-0F8D20F3-A4A1-4A89-852A-449C2E7C9602.htm#RATUG151 

Using Enterprise Manager:

Capture/Replay can be performed from Oracle EM interface.

From the Enterprise menu of the Enterprise Manager Cloud Control console select-> Quality Management->then Database Replay.

The Database Replay page appears.

oracle database load testing

The detailed procedure for Capture from OEM is explained in the following link

https://docs.oracle.com/database/121/RATUG/GUID-1679DB12-B633-4021-A3B9-9CC6216D6681.htm#RATUG117 

And 

The detailed procedure for Replay from OEM is explained in the following link

https://docs.oracle.com/database/121/RATUG/GUID-B97B7550-A947-45D0-A31B-636A6427DD86.htm#RATUG147 

  • Capture Metrics:

While the simulate phase is running make sure OS watcher is running and gathering stats on Load testing environment.

Make sure AWR snapshots are being run at 30 minutes interval.

If OEM infrastructure is available, make sure OEM agent is active on Load testing server and data is being captured. 

  • Analyze:

The initial step is to compare the baseline load and the representative load. Depending on the options available AWR reports can be analysed and OEM data can be compared for various load situations.

Look for the peaks in CPU/Memory usage, SQL response time and throughput and confirm whether they are in the permissible limits.

If you have used Oracle Database Replay for simulating load, following document can be used for analysing the workloads.

https://docs.oracle.com/database/121/RATUG/GUID-3E9AA9F2-B37B-4E03-9E00-FEB0A141631E.htm#RATUG159

 If you have used OATS for capture/reply, following link is the reference for analysing the testing results.

https://docs.oracle.com/cd/E25294_01/doc.920/e15484/oltchap6.htm#CEGEIEDF

For HammerDB tool, use following documentation link.

http://www.hammerdb.com/document.html 

Based on the analysis of metrics, you can decide whether to go LIVE with the Current Change or any tweaking needed. 

Conclusion:

The Preparation Phase is pivotal to the whole Load testing exercise. More caution needs to be exercised in analysis and conclusion especially when the load testing system is not a 1:1 replica of PROD.

Each of the steps and processes involved need to be carefully conducted, since any missed step or issue can invalidate the test result leading to a decision made under a false or misleading information.

A successful and well conducted load testing exercise increases the confidence and reduces the risk of a system upgrade. For any further questions, click below:

Ask Maheedhar