All DBAs should have at least some familiarity with the TPC-C benchmark, as Orabm and Orastress! are based on some of its facilities. The full TPC-C specification - including examples of the 5 TPC transactions and an input data generator (written in embedded SQL) - can be found at www.tpc.org.

A TPC-C compliant data-loader (Orabmload) is included, that implements all the functions in the specification. This data is the basis for both Orabm and Orastress!.

2 Introduction

Oracle DBAs 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 paper describes three command line tools to facilitate basic baseline performance information capture with minimal effort:

· Orabmload: 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

Orabmload generates the test data for both Orabm and Orastress!

It’s Open Source, and available as a Pro*C program.

Appendix A includes instructions on how to build the executable from source.

These tools are not fully-fledged, all encompassing, or totally configurable. Instead 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!

Ready built executables for Windows are provided in all cases: all you need is a low end PC with Oracle9i client installed, and one or more Oracle databases (any 8i or 9i server platform) accessible by Oracle Net to get started.

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 four possible modes, where the mode is provided through a command line parameter. These modes are described in the following table:

Command Line Arg	Description
READ	Runs the identical read-only workload as Orabm to stress CPU and memory.
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 which stresses 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.

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. In these respects the tests differ fundamentally from TPC-C, which simulates the behavior of a real-world application with real end users who introduce thinking and data-entry time delays between transactions.

3 Orabm

Orabm is a set of SQL scripts and command line program (Orabm) designed to help answer the question:

"how does my server perform under a CPU/memory intensive Oracle database workload"

The code is downloadable from:

http://www.linxcel.co.uk/software_orastress.html

The Orabm data loader program (Orabmload) generates a TPC-C compliant set of data against which the test runs, and orabm runs the stress test itself.

Source code for both Orabm (orabm.c) and Orabmload (orabmload.pc) is available.

Note: Ready-built executables for Oracle9i on Sun Solaris, Linux, and Windows are provided – the Windows version means you can run the entire suite from any PC, even a low-end one, where Oracle client software is installed. The database can be any Oracle database accessible by Oracle Net.

If you don't run Oracle on those platforms you can easily build your own versions from source, using instructions in Appendix A.

POSIX threads support is required to build orabm, which runs multiple concurrent database sessions at once from separate threads in the same program.

The chief characteristics of a workload to meet the goal of stressing the CPUs are:

1. it runs entirely within the DBMS server thereby avoiding delays from client/server roundtrips.

2. there is no wait time between transactions

3. all transactions are read-only to avoid file-write operations

4. the set of tables which the stress test runs against fit entirely within a 200MB buffer cache

Provided that you allocate an Oracle System Global Area (SGA) with a 200MB buffer cache, the Oracle workload imposed by orabm should result in no physical I/O at all, once the data is cached - just logical I/O from the Oracle buffer cache, resulting in intensive use of CPU and memory.

3.1 Orabm Installation

All objects used by the stress test are owned by ORABM. Follow the steps below to install the objects and load the test data, making sure that ORACLE_SID is set to the database that you want to run the test against:

#	Operation	Command
1	create the ORABM user (assumes TOOLS tablespace, TEMP temporary tablespace)	sqlplus system/pwd @orabm_user
2	create the tables	sqlplus system/pwd @orabm_tab
3	Load the data	$ orabmload Warehouses 1
4	create the indexes	sqlplus system/pwd @orabm_ind
5	analyze the tables and indexes	sqlplus system/pwd @orabm_analyze
6	create the stress-test PL/SQL procedures	sqlplus system/pwd @orabm_serverside_stress
7	cache the table and index data in the SGA	sqlplus system/pwd @orabm_cache

Note: you can optionally run Orabmload against a remote database by first setting the environment symbol LOCAL (Windows) or TWO_TASK (UNIX/Linux) to contain an Oracle Net alias where you installed the objects. Keep in mind this will be a lot slower than running the load from the server where the database is located.

Atfer completing the list, you can use the orabm_query_cache.sql script to display the approximate percent of each table's data and index blocks present in the block buffer cache. This should be close to 100%.

3.2 How Orabm Works

Orabm works by running a user-specified number of database transactions in each of a user-specified number of concurrent database sessions. The transactions are executed by the ORABM_SERVERSIDE_STRESS stored procedure, under the schema ORABM.

For each concurrent session, ORABM_SERVERSIDE_STRESS runs the number of transactions specified on the orabm command line, and returns the transactions per second (TPS) value for that session during the sampling interval on completion. To ensure that all concurrent sessions are processing transactions during the sampling interval, the TPS value only includes results from the middle 80% of transactions: the first 10% and last 10% are ignored.

The transactions are loosely based on the TPC-C Order-Status and Stock-Level transactions, using a predefined distribution of transactions. The transaction split, which is based on data returned by the DBMS_RANDOM package, should be:

Stock-Level:Order-by-Customer-Name:Order-by-Customer-Id

50%:30%:20%

The string returned by ORABM_SERVERSIDE_STRESS includes the transaction split during the test, to ensure that the transaction distribution is correct, subject to random fluctuations e.g.:

...sl=4042(50.5%) on=2384(29.8%) oi=1573(19.7%)...

3.3 Orabm Command Line

Once you have set up the test tables, data, and indexes, you're ready to run orabm. The following command shows orabm running 20000 transactions in a single session against the Oracle database identified by ORACLE_SID in the UNIX environment:

$ orabm 1 20000

This command line runs the same workload against a remote database identified by the Oracle Net alias linxceld1.co.uk from a Windows command box:

C:\> orabm 1 20000 linxceld1.co.uk

Note: running against a remote database has little (if any), affect on the transaction throughput, because all processing takes place on the DBMS server.

Execution of a single Orabm session should show a single CPU at close to 100% utilization, provided that all table and index data is present in the Oracle block buffer cache and no other workload is running on the database server. On UNIX or Linux, you can use the “top” command to confirm this, or check that no "db file sequential read" event waits are taking place for the Oracle session using info in the V$SESSION_EVENT view - these indicate waits for physical I/O.

Alternatively, if your Oracle DBMS is running on Linux, you can use the gkrellm performance monitor to show that CPU utilization of a single CPU is at ~100% and no physical I/O is taking place. Gkrellm can be downloaded from:

http://web.wt.net/~billw/gkrellm/gkrellm.html

Here's an example of the command line you would use to run 10000 transactions against a local Oracle database for three iterations. In the first iteration, one session runs, in the second iteration two concurrent sessions run, and in the third iteration, six concurrent sessions run:

$ orabm 1,2,6 10000

Keep in mind that the specified number of transactions is run in each concurrent session.

Note: you should specify sufficient transactions such that the TPS results produced don't fluctuate significantly between runs for a given number of sessions; 100000 is a good value to choose.

3.4 Orabm Results Output

Output is appended to a log file orabm.database.log, where database is either the ORACLE_SID or TNS alias that identifies the database where the test was run e.g. orabm.t92.log. For each iteration, the TPS value for each concurrent session appears between begin and end markers. For example, the following shows the contents of the log for two concurrent sessions - in this case the second iteration for the previous command line example - where txn(all) displays the total transaction count, and xn(sam) and t(sam) show the total transactions and time for the middle 80% of transactions for which sampling took place:

---begin sess=2 txn=10000 ORACLE_SID=t92 Fri Nov 8 20:31:48 2002

T92.WORLD txn(all)=10000 xn(sam)=7999 t(sam)=44 tps=182 ...

T92.WORLD txn(all)=10000 xn(sam)=7999 t(sam)=45 tps=178 ...

---end - Fri Nov 8 20:32:46 2002

The total TPS for this iteration is the sum of the TPS for the two concurrent sessions (182+178=360). A shell script (orabm_tps.sh) can be used to process output from the log on UNIX and Linux. The script aggregates the TPS values for concurrent sessions in a single iteration into a total TPS value for that iteration. The output based on the log info from the previous command line (3 iterations with 1, then 2, then 6 concurrent sessions) shows:

$ orabm_tps.sh orabm.t92.log

ORACLE_SID=t92 sess=1 tps=182

ORACLE_SID=t92 sess=2 tps=360

ORACLE_SID=t92 sess=6 tps=364

In this example the server was a 2 CPU model - as a result, 2 concurrent sessions running in orabm are enough to completely utilize all available CPU capacity. Additional sessions should result in the total TPS remaining unchanged, or even falling slightly as the operating system performs context switches to share the overloaded CPU resource between more ready-to-run sessions than available CPUs.

4 Orastress!

Orastress! is a Windows command line utility and Oracle package procedure designed to help answer the question:

"how does my server perform under a wide range of Oracle DBMS workloads"

The code is downloadable from:

http://www.linxcel.co.uk/software_orastress.html

Note: to run Orastress! you need a Windows PC with Oracle9i client software, and one or more Oracle instances accessible via Oracle net.

4.1 Installation

Orastress! requires the same TPC-C schema used by the Orabm CPU stress test. The schema and objects should be installed as shown in the following table.

It’s important to be aware that, while Orabm uses a TPC-C schema with a single Warehouse to ensure all data can be cached in memory, with Orastress! you can choose the number of Warehouses to be a higher value.

Note: the size of the data loaded scales with the number of Warehouses. A 10 Warehouse schema requires approximately 1GB of database space. If you already loaded data for Orabm, run the SQL “DROP USER ORABM CASCADE” first to prepare for Orastress!.

Create the ORABM schema objects, then load and analyze the data as follows:

#	Operation	Command
1	create the ORABM user (assumes TOOLS tablespace, TEMP temporary tablespace)	sqlplus system/pwd @orabm_user
2	create the tables	sqlplus system/pwd @orabm_tab
3	load the data	$ orabmload Warehouses n
4	create the indexes	sqlplus system/pwd @orabm_ind
5	analyze the tables and indexes	sqlplus system/pwd @orabm_analyze
6	Ensure ORABM can run the DBMS_LOCK package	As SYS run: GRANT EXECUTE ON DBMS_LOCK TO ORABM