Software - Orastress! for Oracle

Oracle OLTP benchmarking and stress testing suite

Oracle DBA's and developers often find themselves in situations where they need to measure database performance against a baseline, typically to ensure that operational improvements deliver the expected benefits or to ensure that no regression in performance has occurred as a result of changes.

The changes in question may be due to a server upgrade, an Oracle upgrade, an operating system upgrade, or something more radical like a change in operating system or server hardware vendor.

This technote describes the command line tools to facilitate basic baseline performance information capture with minimal effort:

• Orabm load: loads TPC-C compliant data into a user-chosen number of Warehouses
• Orabm: stresses the CPU and memory of an Oracle DBMS server
• Orastress!: stresses the DBMS server with a multi-session, multi-instance load

Orabm load generates the test data for both Orabm and Orastress!. It's Open Source, and available as a Pro*C program.

They are designed to run out-of-the box and deliver results quickly, to provide a stake in the ground for the performance of a server running several different types of Oracle DBMS workload:

• CPU intensive workload - Orabm
• Mixed-workload Online Transaction Processing (OLTP) - Orastress!
• I/O intensive INSERT workload -Orastress!
• I/O intensive direct-path INSERT workload -Orastress!

Orabm transactions are based on the Stock-Level and Order-Status read-only transactions in TPC-C. By ensuring that the data fits totally in memory (through an appropriate Oracle configuration), Orabm can generate a CPU and memory intensive workload with almost zero physical I/O.

Orastress! runs in one of our possible modes, where the mode is provided through a command line parameter. These modes are described in the following table:

Command Line Argument	Description
READ	Runs a read-only workload designed to stress CPU and memory. All sessions runs against the same TPC-C Warehouse (W_ID=1) independent of how many Warehouses were created. This workload runs the TPC-C Stock-Level and Order-Status read-only transactions only and is identical to Orabm.
OLTP	Uses all five TPC-C transactions: New-Order, Order-Status, Payment, Stock-Level and Delivery, in order to generate a mixed-transaction (SQL INSERT/UPDATE/DELETE) OLTP workload designed to stress CPU, memory, and I/O.
INS	Runs an I/O intensive SQL INSERT workload by copying rows into the ITEM_INS table from the TPC-C ITEM table.
DIO	Runs an I/O intensive direct-path INSERT workload by copying rows into the ITEM_DIO table from the TPC-C ITEM table.
	Revision 3 workloads
READ2	Runs same read-only transactions as READ mode but randomizes sessions across all the available TPCWarehouses in order to generate a read-intensive workload. For example, if you loaded 1000 Warehouses, and ran a 10 session test, each session would be assigned to a Warehouse at random resulting in activity on different subsets of the underlying tables.
CUST1	You provide a SQL script containing SQL of your choice. If this is a SELECT statement, the fetch operation is always performed one row at a time. The script may contain a PL/SQL block, enabling randomized workloads to be generated.
CUST	You provide a SQL script containing SQL of your choice. If this is a SELECT statement, the fetch operation is performed using a configurable number of rows per fetch (batch-size) The default value is 100. The script may contain a PL/SQL block, enabling randomized workloads to be generated. An example is provided in a later section.

All tests run entirely on the database server without client-server traffic generation, and consecutive transactions run without any delay period: that's because the goal of the tests is to stress the DBMS server.

Orastress! is available as a standalone product with ready built executables for all major operating systems. Minimise your performance testing effort when considering platform moves, changes or migrations.