官网:
作者:Geoff Ingram 曾就职于oracle开发部门,是《High Performance Oracle》一书的作者
· Orabmload: loads TPC-C compliant data into a user-chosen number of
Warehouses --测试数据加载工具
· Orabm: stresses the CPU and memory of an Oracle DBMS
server --CPU和内存测试工具,开源
· Orastress!: stresses the DBMS server with a
multi-session, multi-instance
load --多会话,多并发测试工具,需要申请key
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:
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.
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%.
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%)...
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:
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.
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.
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:
Note: to run Orastress! you need a Windows PC with
Oracle9i client software, and one or more Oracle instances
accessible via Oracle net.
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
The Orastress! workload for each session is generated by the
ORABM.ORASTRESS.STRESS package procedure. To create the tables
required for the Orastress! insert tests, and install the package
against the database identified by
(where you loaded
the test data), run:
sqlplus
orabm/orabm@
@orastress_tab.sql
sqlplus
orabm/orabm@
@orastress.plh
sqlplus
orabm/orabm@
@orastress.plb
By default you can run one session per database at any
time. This in itself provides useful information
on server performance.
You need to register and get a key to create
more than one concurrent load session per database.
To get a trial key free of charge, please complete the following
online form. Your trial key details will be
emailed to you shortly thereafter.
Your Serial number is displayed by running Orastress! without
command line arguments from a DOS command window e.g.:
C:\orabm>orastress!
Serial: D5F8-C0A0
After receiving the key, install as follows by entering the Name
and Key into the dialog box that displays when you run:
C:\orabm>orastress! register
Orastress! contains significant enhancements over Orabm:
· it provides 4 different types of workloads.
· it can execute against multiple instances in each iteration, from
the same command line.
Command line usage:
orastress! –s sess-iterations-list –t
transacts-per-sess –c tns-alias-list –m mode [
-s batchsize]
where
transactions-per-session in each iteration is run
concurrently against all aliases in tns-alias-list
mode is one of
READ OLTP INS DIO
batchsize is
optional number of rows inserted per transaction for INS and DIO
mode (default 10000)
Note: a COMMIT takes place after each batchsize insert in INS
or DIO mode.
Note: when Orastress! runs in READ mode against a single
Warehouse, the code executed is identical to Orabm.
Example 1:
orastress! –s 1 –t 50000 –c inst1.world –m
oltp
Run one iteration - comprising 50000 transactions in 1 session -
against inst1.world.
Example 2:
orastress! –s 2 –t 50000 –c
inst1.world,inst2.world –m oltp
Run one iteration comprising 2 concurrent
sessions of 50000 transactions on each of inst1.world,inst2.world
giving a total of 4 concurrent
sessions. The location of the instances specified
by inst1.world,inst2.world is entirely down to the
user. They could be:
· 2 instances of a RAC cluster
· different non RAC instances on the same server
· different non RAC instances on different servers (e.g. one on
Linux, one on Solaris)
Example 3:
orastress! –s 1,2,4 –t 50000 –c
inst1,inst2,inst3,inst4 –m read
Run 3 iterations each
of 50000 transactions on each
of four instances: inst1, inst2, inst3 and inst4,
where the number of concurrent sessions in each iteration is:
1st
iteration: 1 concurrent session on each instance, giving 4 sessions total
2nd
iteration: 2 concurrent sessions on each instance, giving 8 sessions total
3rd iteration: 4 concurrent sessions on each instance, giving 16
sessions
Output is very similar to Orabm, showing the transactions per
second for each session in each iteration and the
transaction split between the five TPC-C transactions for OLTP
mode:
---begin mode=oltp sess=1 txn=5000 TNS=l Fri Sep 19 22:11:06
2003
LXD1.WORLD #1 w=3 txn(all)=5000 xn(sam)=3999 t(sam)=431 tps=92.8
end=190903-12:25:39
---end - Fri Sep 19 22:20:19 2003
You need to add the tps= values in each iteration session to
give the total tps for that iteration.
Note: the instance id is given by #1.
Orastress! in OLTP mode enables you to monitor performance via
SQL during execution as follows:
-- use this SQL to show total
Transactions/Sec (TPS) for a single instance
select sum(substr(module,instr(module,'#')+1))
tps from
v$session where module like 'oltp%#%';
-- use this SQL to show total TPS per
instance for RAC
select
inst_id,sum(substr(module,instr(module,'#')+1)) tps from
gv$session where module like 'oltp%#%'
group by inst_id
-- use this SQL to show total TPS
cluster-wide for RAC
select sum(substr(module,instr(module,'#')+1))
tps from
gv$session where module like 'oltp%#%'
To convey the maximum information about performance during
execution, a graphical presentation of wait events and statistics
is required. For example, you can use the Event Profiles and
Statistic Profiles options in the Enterprise Edition free trial of
empower! to show wait events and statistics for all nodes in
a RAC cluster on a single display. You can download empower!
from:
The following screenshot shows significant amounts of waits for
I/O (“db file sequential read”) and row level locks (“enqueue”)
during a three-session iteration of Orastress! on Red Hat Linux ES
2.1, running on a single instance:
The following hardware and Oracle configuration produced ~100
TPS for a single session in repeated tests:
· Oracle 9.2.0.4
· Red Hat Linux ES 2.1 kernel 2.4.9-e.12
· 1 x Pentium P4 1.4GHz processor
· 1 GB ECC RAM
· database located on 10000rpm Ultra160 18GB SCSI drive
· 10 Warehouse schema (Orabmload – 50 mins load time)
· 8K blocksize database
· 3 x 100MB redo logs
· 500K redo log buffer
TOOLS tablespace definition as follows:
CREATE TABLESPACE TOOLS BLOCKSIZE 8192
DATAFILE
'/u03/oradata/LXD1/tools01.dbf' SIZE 2000M
AUTOEXTEND ON
NEXT 1280K MAXSIZE UNLIMITED
ONLINE PERMANENT EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO
/
Tip: set the TOOLS tablespace file size to ensure no
autoextend takes place during the monitored interval.
Significant Oracle initialization parameters:
db_cache_size 268435456
db_file_multiblock_read_count 16
log_buffer 524288
log_checkpoint_interval 0
pga_aggregate_target 25165824
shared_pool_size 83886080
sga_max_size 403772716
workarea_size_policy AUTO
If the supplied executables of orabm and orabmload don't
work
for your OS or version of Oracle, you can create both from the
source code.
· orabmload.pc is a Pro*C program.
· orabm.c is an OCI program.
For orabmload you need Pro*C installed (check for
$ORACLE_HOME/bin/proc).
For orabmload and orabm, you need a C compiler. On Linux, gcc is
available.
You can also download ready-built binaries of gcc for many other
OS, including Solaris.
Note: The table and index creation scripts assume a TOOLS
tablespace. For 10g this doesn’t exist by
default, so you need to create it.
$ cd $ORACLE_HOME/rdbms/demo
Copy orabmload.pc to this directory.
Edit demo_rdbms.mk and add orabmload to the DEMOS symbol
e.g.:
DEMOS=orabmload cdemo1 …
$ make -f demo_rdbms.mk orabmload PROC=$ORACLE_HOME/bin/proc
$ cd $ORACLE_HOME/rdbms/demo
Copy orabm to this directory.
Edit demo_rdbms.mk and add orabm to the DEMOS symbol e.g.:
DEMOS=orabm cdemo1 …
$ make -f demo_rdbms.mk orabm