Release 2.1 for Windows
One particular advantage of Hammerora is is the ability to generate a performance profile as the load increases on your system. Whereas an official TPC-C benchmark gives you a single data-point and a tpyical single-threaded test (such as timing SQL Statements) also gives you a single data-point consider the following graph. This graph shows the relative performance of real tests on different Oracle software configurtions on the same hardware configurations. If executing a single-threaded test it is likely that the only data-point you would see is the one on the far left of the graph leading you to draw the wrong conclusion on the performance potential as the load increases. Similarly an official TPC-C results would give you a single data point similar to one towards the right of the graph meaning you miss the way the performance profile changes as the load increases. Unless you can accurately predict the performance you need in advance you need to be aware of the potential of your system across all levels of system utilisation.
An additional benefit of being familair with the Hammerora TPC tests is it provides an excellent education in understanding and interpreting the real TPC benchmark submissions. Therefore whether discussing the value of benchmarks to 'real-world' applications, analysing the motives of vendors for publishing or not publishing TPC results or researching the purchase of a new system with the latest benchmarks you will have a greater understanding of the data behind the headline figures and more chance of interpreting and discussing it accurately.
Before beginning
the details of how to run the Hammerora TPC-C and TPC-H simulations it
is a useful discussion to set the scene in terms of detailing the sort
of performance you should expect to see from a system depending on the
workload
compared to an existing environment. To do this there are 3 key
hardware elements of database performance that you should be familiar
with and how they interact to get the best out of a system, notably
CPU, memory and I/O (storage and networking). The most important factor
of these is the CPU, however memory and I/O are vital in realising the
potential of the CPU with of course the system and chipset governing
how these components interact. A useful analogy here is to think
of your system as a Formula 1 car. The CPU is the engine, without the
right level of 'horsepower' you won't be competitive. The memory is the
aerodynamics, you need to vary where the aerodynamics are assigned
depending on the circuit and similarly depending on the Oracle workload
you need to vary the amount of memory assigned to both SGA and PGA.
Finally in Formula 1 tyres are the only component in contact with the
circuit, get the tyres wrong and it doesn't matter about the rest of
the setup. Similarly for your Oracle system I/O is vital, I/O is the
only direct contact with your data and failure to set it up correctly
means you will not see anything like the levels of
performance you could from the CPU and memory you have in place. It
should be clear that a question such as what "Oracle performance can I see from
this CPU?" is difficult to answer when asked completely out of
context
of the rest of the configuration. Finally to stretch the Formula 1
analogy it is useful to review some of the configurations used in
official TPC benchmarks especially to observe the levels of memory,
storage and some of the code used for up to date benchmarks. In other
words you would not expect your road car to deliver the same
performance as a Formula 1 car and similarly you should not expect your
system to deliver the same performance as an offically sanctioned
benchmark, But isn't this why you wan't to test it in the first
place?
So what sort of performance can you expect from your system? Well given what we have just learned asumming we have sufficient memory and I/O to drive the throughput then a system with a newer CPU should give us better performance, but by how much? A good place to start is the offical TPC-C results. For example the top TPC-C result in July 2008 for price/performance is 97,083 tpmC on a Dell Poweredge 2900 with Oracle 11g SE One on 1 Intel Xeon QC 5440 2.83GHz. Less than 10 years before in 1999 a Sequent NUMA-Q 2000 recorded 48,793 tpmC with Oracle8 on 32 x 405Mhz Intel Pentium II Xeon processors. The price per transaction of the 2008 system is 0.68 US$ compared to the 127.53 US$ for the 1999 system. So in less than 10 years we have an Oracle database system with 1 processor delivering double the performance of a system with 32 processors at 187 times lower cost for both hardware and software, with the hardware cost reducing from $1,708,833 to $1253.00 and the database software cost reducing from $1,071,372 to $2,498.
It
is worth re-reading a couple of times to
appreciate the incredible difference in both performance and hardware
and software cost. It is also worth browsing the historical
archives of the TPC-C to see that this is not a selective anomaly,
as a general rule performance increase and cost decrease has been
exponential. Clearly a year is a long time in database performance. and
if your
system is more than 18 months old the statistics suggest that a server
refresh could have the potential to more
than double the performance and halve the total cost. So
how is this possible? The
answer is Moore's Law
and
understanding it can reap dividends in driving down your costs. Failing
to understand it or not taking the time to evaluate its impact could
mean you paying the maintenance on a system that costs 3 million US$
that delivers half the performance of a new one that could cost you
less than
3 thousand US$.
In a nutshell Moore's Law is the
prediction dating from1965 that due to advancements in manufacturing
process technology the number of transistors on an integrated circuit
can be doubled every 18 months to 2 years. Whether this is a prediction
or a challenge laid down that the seminconductor manufactuing industry
has met the result has been the same. The consequences of Moore's Law
essentailly means that:
1. The more transistors there are on
a CPU, the more features there can be, for example multiple cores or
larger cache sizes.
2. The potential for increased clock speeds and reduced power
consumption.
3. The processors cost less to produce as more are produced from
each silicon wafer.
It is important to note that Moore's Law does not necessarily mean the performance will increase in direct proportion to the number of transistors on a CPU however the TPC-C results shows that over time Oracle database performance is primarily influenced by improvements in CPU technology.
Clearly an essential component is to ensure that performance testing and evaluation is not a once in a deployment event but should be an ongoing process. Hammerora offers you the ability to do this in your own environment on your own systems and provide a means to constantly lower your Total Cost of Ownership (TCO) year after year. One aspect that is particulalry key for testing is the advent of multi-core processors, traditionally simple performance testing may have taken place with basic "single-threaded" query or transaction testing however this is not adequare for testing the potential of a multi-core processor. Fortunately Hammerora is multi-threaded and therefore ready to test your multi-core environments.
Finally before
looking at running our own simulations it is worth considering one of
the most frequent comments around TPC benchmarks. When discussing these
workloads it won't be long before someone pronounces that "TPC workloads are
meaningless".
This often stems from a misunderstanding of the nature of the
benchmarks themselves, a lack of familairty with the workloads and
their specifcations or even the desire for you not to look too closely
at the data available. Firstly this raises the question of being
meaningless relative to what? Of course any generic benchmark will not
have the same meaning as a performance test of your own application
(and using Hammerora you can and should conduct a performance test of
your own application), however doing so by its very nature makes the
test unique preventing wider comparisons. In addition experience often
shows that your own application no matter how well designed can be
difficult to test the database component for scalability making
it difficult to extrapolate the results. The TPC benchmarks on the
other hand are incredibly well designed to scale to provide a
meaningful comparison of systems and I strongly encourage reading the
benchmarks specifications to understand the complexity of the
workloads. Far from being meaningless, if given a brief to design
a database workload in order to compare systems it would be extremely
difficult to design something better than the workloads designed by the
TPC.
There
is more detailed information on TPC-C and TPC-H workloads in
in Pro Oracle Database 10g RAC on
Linux
To
begin simulating TPC workloads with Hammerora you will need to
select which benchmark you wish to use by choosing select benchmark
from under the TPC menu. This selection governs which schema creation
script will be used and the statistics used in the transaction counter.
TPC-C
The
TPC-C benchmark is the best known
database benchmark. The current version is
5.9 and there is a full
specification in
PDF form on the TPC site. This specification and the example
procedures have provided the foundation for the implementation of
TPC-C in hammerora.
Where possible the SQL has been copied directly
from the example procedures and not optimised in any way. The only
changes made have been to
enable compatibility with Oracle and to correct the one or two errors
in
the SQL present in the specification appendix. It should be noted that
this
implementation is not an exact copy of a TPC-C benchmark, for one of these browse the
full
disclosure reports for the Oracle submitted benchmarks. Instead this
implementation provides as close a simulation as possible to the
benchmark that can be done without additional
expensive Transaction
Management software.
To
set-up a suitable testing environment I recommend that you configure
your target database on one server (or servers with RAC) and set up a
load-test server or servers on a separate system connected to the
target server on a network. Hammerora is extremely lightweight, even a
simple single-core desktop will have the potential to drive a target
server up to hundreds of thousands of transactions. Load test
servers are an ideal usage for your older generation servers you have
retired to replace with a newer generation. A tool such as top on Linux is the best way to
ensure that load-test server is not the bottleneck in the number of
transactions you want to run. Also bear in mind that you can connect
multiple instances of Hammerora across the network in order to build a
more powerful load testing infrastructure. This can be particularly
useful when load testing RAC in order to direct individual load test
servers at individual RAC nodes to fully partition the workload.
To create the TPC-C schema select the TPC-C schema options menu tab
from the top level TPC menu.
Fill
in the values according to the database where the schema will
be built.
Most
of the values should be fairly self explanatory if familiar with the
Oracle Database. If you have difficulty with the meaning of some
of the valeus such as Oracle Service Name I suggest beginning
with the Oracle Database Concepts Guide , otherwise you may find it difficult jumping
straight into performance testing without having a grounding in basic
Oracle database administration. The user will be created however the
tablespace and temporary tablespace must already exist. Allow
approximately 100MB per warehouse in the warehouse you create. The
number of warehouses to create can be selected on a sliding scale of 1
to 100. (HINT: See later in this section if you want to create
more than 100 warehouses). You should choose the number of warehouses
based on the number of user threads that you expect to run to enable
each user thread to have its own home warehouse. This will depend on
your system capabilities and the type of test you want to run. You also
have the option of running the creation in PL/SQL directly on the
server itself. In most cases the standard TCL based creation will be
faster unless network latency is a major factor. Click OK to save the
values you have entered. For a permanent record the values can be
entered directly into the config.xml file detailed on the documentation
page. On starting Hammerora the schema options will already contain the
values you have entered in the corresponding fields, for example:
<schema>
<count_ware>1</count_ware>
<tpcc_user>tpcc</tpcc_user>
<tpcc_pass>tpcc</tpcc_pass>
<tpcc_def_tab>tpcctab</tpcc_def_tab>
<tpcc_def_temp>temp</tpcc_def_temp>
<plsql>0</plsql>
<directory>
</directory>
</schema>
To
begin the schema creation at the buttons in the top level window click
the "Create TPC Schema" button. This button is shown as three coloured
boxes and "Create TPC
Schema" appears in the information box when moused over. On
clicking this button a dialogue box such as the following appears
When you click Yes Hammerora will login to
your chosen service name as the system user and create the user with
the password you have chosen. It will then log out and log in again as
your chosen user, create the tables and stored procedures and then load
the data before creating the indexes and gathering the statistics. It
is worth noting that the TPC-C schema creation script is a standard
Hammerora script like any other so you can save it, modify it and
re-run it just like any other Hammerora script. For example if you wish
to create more than the 1-100 warehouses avaiable in the GUI you may
notice that the last line in the script calls a procedure with all of
the options that you gave in the schema options. Therefore change the
second value to any number you like to create more warehouses, for
example the following will create 1000 warehouses.
do_tpcc
manager oracle 1000 tpcc tpcc tpcctab temp 0 /tmp
Similarly
change any other value to modify your script. If you have made a
mistake simply close the application and run the following SQL to undo
the user you have created.
SQL>drop user tpcc cascade;
It is worth noting that the schema creation
for any reasonably sized schema is not going to be immediate. You are
not just loading data you are creating and inserting a large amount of
data and therefore the creation time required will depend on the
capabilities of your system. The impatient with multicore environments
will find it straightforward to modify the creation script and run
multiple copies of the table loading section to load the tables
simultaneously. I have left the standard script as single core for
simplicity.
I
strongly recommend that for any large schema you plan your creation to
take place in a suitable quiet period and once you have created
your TPC-C schema that you immediately export your schema and save the
dumpfiles
for reuse. The following example details an export using 10g data pump.
As system /manager
SQL> create
directory dump_dir1 as '/home/oracle/dumpfiles';
Directory created.
[oracle@dragonfly
dumpfiles]$ expdp system/oracle SCHEMAS=tpcc
CONTENT=ALL directory=dump_dir1;
Export:
Release 10.2.0.1.0 - Production on Friday, 23 March, 2007
16:11:57
Copyright (c)
2003, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
- Production
With the
Partitioning, OLAP and Data Mining options
Starting
"SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********
SCHEMAS=tpcc CONTENT=ALL directory=dump_dir1
Estimate in
progress using BLOCKS method...
Processing
object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total
estimation using BLOCKS method: 778 MB
Processing
object type SCHEMA_EXPORT/USER
Processing
object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing
object type SCHEMA_EXPORT/ROLE_GRANT
Processing
object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing
object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing
object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing
object type SCHEMA_EXPORT/TABLE/TABLE
Processing
object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing
object type
SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing
object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing
object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing
object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported
"TPCC"."STOCK"
295.4 MB 1000000 rows
. . exported
"TPCC"."ORDER_LINE"
191.4 MB 3033848 rows
. . exported
"TPCC"."CUSTOMER"
158.1 MB 300000 rows
. . exported
"TPCC"."HISTORY"
14.44 MB 303146 rows
. . exported
"TPCC"."ORDERS"
9.652 MB 303164 rows
. . exported
"TPCC"."ITEM"
7.227 MB 100000 rows
. . exported
"TPCC"."NEW_ORDER"
1.205 MB 89958 rows
. . exported
"TPCC"."DISTRICT"
17.08 KB 100 rows
. . exported
"TPCC"."WAREHOUSE"
8.296 KB 10 rows
Master table
"SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully
loaded/unloaded
******************************************************************************
Dump file set
for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dumpfiles/expdat.dmp
Job
"SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:16:00
[oracle@dragonfly
dumpfiles]$ ls -l
total 694900
-rw-r-----
1 oracle oinstall 710868992 Mar 23 16:16 expdat.dmp
-rw-r--r--
1 oracle oinstall 2227
Mar 23 16:16 export.log
-rw-r--r--
1 oracle oinstall 1973
Mar 23 16:24 import.log
To import do the following:
[oracle@dragonfly dumpfiles]$ impdp system/oracle SCHEMAS=tpcc CONTENT=ALL directory=dump_dir1;
Import: Release 10.2.0.1.0 - Production on Friday, 23 March, 2007 16:20:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** SCHEMAS=tpcc CONTENT=ALL directory=dump_dir1
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TPCC"."STOCK" 295.4 MB 1000000 rows
. . imported "TPCC"."ORDER_LINE" 191.4 MB 3033848 rows
. . imported "TPCC"."CUSTOMER" 158.1 MB 300000 rows
. . imported "TPCC"."HISTORY" 14.44 MB 303146 rows
. . imported "TPCC"."ORDERS" 9.652 MB 303164 rows
. . imported "TPCC"."ITEM" 7.227 MB 100000 rows
. . imported "TPCC"."NEW_ORDER" 1.205 MB 89958 rows
. . imported "TPCC"."DISTRICT" 17.08 KB 100 rows
. . imported "TPCC"."WAREHOUSE" 8.296 KB 10 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 16:24:52
Using this method you only need to create the schema you need once and keep the export file to recreate the schema All of the tables, indexes, procedures etc you need will be in your export file. You will soon start to build a portfolio of Hammerora datasets of different sizes to resuse in your environment.
When you have created your schema before running any workload take some time to get familar with it in SQL*PLUS or your favourite admin tool.
SQL> select tname, tabtype from tab;
TNAME TABTYPE
------------------------------ -------
HISTORY TABLE
CUSTOMER TABLE
DISTRICT TABLE
ITEM TABLE
WAREHOUSE TABLE
STOCK TABLE
NEW_ORDER TABLE
ORDERS TABLE
ORDER_LINE TABLE
9 rows selected.
SQL> select * from warehouse;
W_ID W_YTD W_TAX W_NAME W_STREET_1
---------- ---------- ---------- ---------- --------------------
W_STREET_2 W_CITY W_ W_ZIP
-------------------- -------------------- -- ---------
1 773095764 .11 4R0mUe rM8f7zFYdx
JyiNY5zg1gQNBDO v2973cRoiFSJ0z OF 374311111
SQL> select index_name, index_type from ind;
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
IORDL IOT - TOP
ORDERS_I1 NORMAL
ORDERS_I2 NORMAL
INORD IOT - TOP
STOCK_I1 NORMAL
WAREHOUSE_I1 NORMAL
ITEM_I1 NORMAL
DISTRICT_I1 NORMAL
CUSTOMER_I1 NORMAL
CUSTOMER_I2 NORMAL
10 rows selected.
SQL>
SQL> select object_name from user_procedures;
OBJECT_NAME
------------------------------
NEWORD
DELIVERY
PAYMENT
OSTAT
SLEV
You can also browse the stored procedures you have created by looking in the creation script.
At this point the data creation is complete and you are ready to start running a performance test. Before doing so it is worth noting that the schema has been designed in order that you can run multiple tests and it will return the same results. You therefore do not need to refresh the schema after every run for consistent results.
OK, so your schema is created and you have exported a copy, now from Hammerora load the TPC-C driver script. by selecting TPC-C Driver Script from the TPC menu.
This populates the editor pane with a script suitable to run a TPC-C style OLTP workload as shown
You should not need to modify any of the script except for the contents of the EDITABLE OPTIONS section and as with the TPC-C schema creation these options can be set permanently in the config.xml. In the EDITABLE OPTIONS section you have the following default choices that can be modified according to your environment.
set
total_iterations 1000 ;# Number of transactions before logging off
set RAISEERROR "false" ;# Exit script on Oracle error (true or false)
set KEYANDTHINK "true" ;# Time for user thinking and keying (true or
false)
set connect tpcc/tpcc@oracle ;# Oracle connect string for tpc-c user
total_iterations
The
total_iterations value is the number of transactions that the driver
script will execute for each user before logging off. It is
important to draw the distinction between the total_iterations value
and the Iterations value set in the Virtual User Options window. The
Iterations value in the Virtual User Options window determines the
number of times that a script will be run in its entireity. The
total_iterations value is internal to the TPC-C driver script and
determines the number of times the internal loop is iterated ie
for
{set it 0} {$it < $total_iterations} {incr it} { ... }
In
other words if total_iterations is set to 1000 then the executing user
will log on once execute 1000 transactions and then log off. If on the
other hand Iterations in the Virtual User Options window is set to 1000
and total_iterations in the script set to 1 then the executing user
will log on execute one transaction and then log off 1000 times. For
the TPC-C driver script I recommend only modifying the total_iterations
value. The value for total_iterations should be set according to a
couple of factors (also see KEYANDTHINK later in this section before
deciding on a value). Firstly total_iterations should be determined by
the length of time you want the script to run. This will in turn depend
on the performance that the system delivers, a faster system will
complete the transactions in a shorter space of time and therefore need
a comparatively larger value for the script to run for a particular
length of time. I recommend planning for a test to run for at least 10
minutes continuously and setting the total_iterations value
accordingly. Secondly you should factor in the number of users you plan
to run, a larger number of users will result in a lower level of
performance for an individual user and therefore need a lower number of
total_iterations for each individual. Always remember when setting
total_iterations that each user will run this number of transactions
not all the users running the workload so plan accordingly, in TCL
threads an exit command will wait until the script has completed before
terminating the thread and therefore a high total_iterations value will
mean waiting a longer time before the thread exits.
RAISEERROR
RAISEERROR
impacts the behaviour of an individual virtual user on detecting an
Oracle error. If set to TRUE on detecting an Oracle error the user will
report the error into the Hammerora console and then terminate
execution. If set to FALSE the virtual user will ignore the error and
proceed with execuing the next transaction. It is therefore important
to be aware that if set to FALSE firstly if there has been a
configuration error resulting in repeated errors then the workload
might not be reported accurately and secondly you may not be aware of
any occasional errors being reported as they are silently ignored. I
recommend running pre-tests with RAISEERROR set to TRUE to ensure a
configuration is valid before setting it to FALSE for a measured test
run.
KEYANDTHINK
connect
If familiar with an Oracle environment this parameter should be self-explanatory. As long as you can connect to Oracle with SQL*PLUS using exactly the same connect string then Hammerora will also be able to connect. If having difficulty connecting with Hammerora then troubleshoot with the normal tools such as tnsping to resolve the connectivity issues.
OK, so you have now built your test schema and got some familiarity with the TPC-C specification. THe next step is to enter a pre-test phase. The aim of this phase is rather than launch straight into testi to get some familarity with the tool and the workload in order to be able to execute the a load-test in a more proficient manner.
From the top level menu under Virtual Users select Vuser Options
Under the Virtual User Options Window select a suitable low number of users. Also selecting to show output can aid familiarity with the workload.
Click OK and the configuration is saved. Note that at this point the virtual users have not yet been created. At the top level menu icons select the button that displays Load Virtual Users in the information box. In the middle pane you will see the virtual users created with the identifier for their operating system thread If you have also selected to show the Output the Virtual User Output pane will not become active showing the virtual user thread identifiers in a Grid format. At the top level menu select the button with the hammer icon identified by Run Hammerora Loadtest in the information box and if you have followed the steps correctly the users will log into the target database and begin executing the transactions. If you have selected to view output you should see the result of these transactions being displayed.
You can also confirm using SQL*PLUS (or Oracle Enterprise Manager) that the Virtual Users have logged on to Oracle and are executing their workload.
SQL> select username, program from v$session where username = 'TPCC';
USERNAME PROGRAM
------------------------------ ------------------------------------------------
TPCC wish8.5@dragonfly (TNS V1-V3)
TPCC wish8.5@dragonfly (TNS V1-V3)
While the users are executing their workload it is possible to view the number of transactions the system is executing overall by viewing the transaction counter. Under the top level menu select Transaction Counter and then select Counter Options
Under counter options enter the connect string for the system user. If using RAC the RAC Global Transactions ensures that all transactions for the entire cluster are reported, if not selected then only the transactions of the instance that the transaction counter connects to are reported.
Press OK and then select the pencil icon button at the top level menu with Transaction Counter displayed in the information box. This will display the transaction montior reporting the number of transactions per minute executed by the database.
Note that you can now switch between the active Script Editor, Virtual User Output and Transaction Counter tabs.
In addition to the transaction monitor you will also want to capture Oracle statistics in order to profile the performance whilst the workload is running. There are a number of options to do this however the best is to use the statistics already generated and captured by Oracle itself. From Oracle 10g onwards you already have the MMON process running as part of the Oracle instance and it is therefore most efficient to make use of these in-built features. To capture these statistics I recommend that you become familiar with AWR reports for Oracle 10g and 11g Enterprise Edition and STATSPACK for Oracle 9i and Oracle 10g and 11g standard edition. Whichever tool you use there is no difference in the statistics that you will capture apart from the way that they are presented. If you get familiar with AWR and STATSPACK at the same time as running a workload you will also have a portable archive of statistics that will be broadly understood by all of your Oracle DBAs. Fortunately to caputre these statistics is extremely straightforward. Taking AWR as an example, while your Hammerora TPC-C workload is running also login to the target database with a session with DBA privileges eg
oracle@dragonfly:~> sqlplus / as sysdba
and then take an AWR snapshot
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
wait until the workload is nearly complete and then take another snapshot
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
When the virtual users have completed their threads, they will display a complete status.
At your leisure generate an AWR report eg
SQL> @?/rdbms/admin/awrrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3836717229 HAM10 1 HAM10
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
...
Select the two most recent snapshot numbers (note that with the default policy you may be taking snapshots automatically so make sure that the most recent snapshot numbers coincide with the ones you generated) and generate the report. Now take some time to get familiar with the AWR report and the statstics that you have captured, by for example opeing the html format report in your favourite web browser. In particular for your tests you want to be aware of the following sections.
The Load Profile will show you the average number of transactions per second recorded over the duration of the time of the entire test. Multiply this number by 60 to get your transactions per minute value for the entire test. The sharp-eyed of you will notice that this figure will bear an uncanny resemblance to the transactions per minute figure you saw in the Hammerora transaction counter. There is therefore no need to note the transaction counter values on the fly, let the AWR do the work for you. For example the following load profile shows the test did 30582 transactions per minute (509.7 *60)
Load Profile
| Per Second | Per Transaction | Per Exec | Per Call | |
|---|---|---|---|---|
| DB Time(s): | 0.8 | 0.0 | 0.00 | 0.00 |
| DB CPU(s): | 0.6 | 0.0 | 0.00 | 0.00 |
| Redo size: | 2,597,878.0 | 5,096.8 | ||
| Logical reads: | 52,657.7 | 103.3 | ||
| Block changes: | 16,136.9 | 31.7 | ||
| Physical reads: | 21.8 | 0.0 | ||
| Physical writes: | 168.5 | 0.3 | ||
| User calls: | 782.4 | 1.5 | ||
| Parses: | 396.1 | 0.8 | ||
| Hard parses: | 0.0 | 0.0 | ||
| W/A MB processed: | 71,878.2 | 141.0 | ||
| Logons: | 0.1 | 0.0 | ||
| Executes: | 10,544.9 | 20.7 | ||
| Rollbacks: | 0.9 | 0.0 | ||
| Transactions: | 509.7 |
Another section I encourage you to take careful note of is the Top 5 Timed events. You should expect DB CPU to be the top event for a lower numbers of user threads with log file sync growing in prominence as the system gets busier. If however you see log file sync as the top event in your first tests and a low level of CPU utilisation then most likely you will need to review the performance of the disks on which your redo logs are based.
Top 5 Timed Foreground Events
| Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| DB CPU | 189 | 79.56 | |||
| log file sync | 107,395 | 33 | 0 | 13.94 | Commit |
| db file sequential read | 1,757 | 9 | 5 | 3.84 | User I/O |
| db file scattered read | 1,093 | 6 | 6 | 2.64 | User I/O |
| SQL*Net message to client | 117,831 | 0 | 0 | 0.08 | Network |
Finally you should also take a note of the SQL ordered by elapsed time. In particular take a note of the difference between the elapsed time and the CPU time. The wider the gap between these two values the less potential of the CPU you are measuring and the more likely that additional hardware such as memory and CPU could improve performance.
SQL ordered by Elapsed Time
| Elapsed Time (s) | CPU Time (s) | Executions | Elap per Exec (s) | % Total DB Time | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|
| 110 | 103 | 51,285 | 0.00 | 46.06 | 16dhat4ta7xs9 | wish8.5@damselfly1 (TNS V1-V3) | begin neword(:no_w_id, :no_max... |
| 41 | 33 | 50,977 | 0.00 | 17.04 | aw9ttz9acxbc3 | wish8.5@damselfly1 (TNS V1-V3) | BEGIN payment(:p_w_id, :p_d_id... |
| 28 | 28 | 5,102 | 0.01 | 11.73 | d4ujh5yqt1fph | wish8.5@damselfly1 (TNS V1-V3) | BEGIN delivery(:d_w_id, :d_o_c... |
Although these are important sections, don't ignore the rest of the AWR report other sections such as the buffer cache advsory will also help in analysing performance.
Now having run a successful pre-test and gained familiarity with the environment you are now ready to proceed with a comprehensive load-test. If you are happy with what you have read so far and prefer a hands-on approach then stop reading and start testing by manually running tests with a progressive increase in the number of users. I also recommend not displaying the user output when running a test to get the maxium throughput. I also recommend noting your results in a spreadsheet application such as OpenOffice in order to plot and profile the performance of the system as the load increases and to note which AWR reports correspond to which data points.
You may however prefer a hands-off approach and want to let Hammerora do the work for you. As Hammerora runs any script that adheres to the TCL language syntax this is extremely easy to do and this section will show you how.
Firstly we want to tell the Hamerora virtual users to do different things but how do we do this when there is one script input window? The answer is to let each virtual user identify which thread it is running in and the position in the overall threads. The section below will do just that.
set mythread [thread::id]
set allthreads [split [thread::names]]
set totalvirtualusers [expr [llength $allthreads] - 1]
set myposition [expr $totalvirtualusers - [lsearch -exact $allthreads $mythread]]
When you have identified the thread position you can then use the TCL switch statement to tell the users what to do depending on their position.
switch $myposition { ... }
It is worth noting that the transaction counter is also a thread so if the transaction counter is running you will need to increase your values in the switch statement by 1.
So now what you will need to do is to complete the switch statement by telling one user to time the test and run the AWR snapshots while letting the rest of the users run the workload. This means we need additional code for the timing and snapshots while the default TPC-C driver script will do fine for the workload.
Not quite sure what the final script will look like? Although I encourage you to experiment it should look something like this. Once you have downloaded and saved the file, select the Open command from the File menu.
In the file dialogue navigate to the directory where you downloaded the file, change the file type filter to *.tcl, select the downloaded file and click OK. THis will load the modified script into the script pane.
Note that the script will time a ramp-up period for the transactions to increase to a desired level, take a snapshot, time a test and take another snapshot so once you have started the test you can leave it go and have a cup of tea and come back after it has done to generate the AWR report and kick off the next test with more users. Note the transaction levels in your favourite spreadsheet and you should soon be able to generate graphs with the supporting AWR data with minimal manual intervention. Also note that the default load script has been modified to comment out the user output. This means that you can run the tests with Virtual User Output set to on. This will display the AWR snapshots that you need to generate the report between but not display the output for the users running the actual load.
If testing RAC you may want to divide the workload accurately between nodes. Please refer to the documentation page for information on how to operate Hammerora in Master and Slave mode to achieve precisely this.
Now you should have all the information you need in order to generate some meaningful OLTP tests against your Oracle systems while gathering the data to verify your findings at the same time so enjoy your testing
Remember Hammerora is a committed community based project, if when reading this documentation you have an insgiht, tip or trick that will help other users with their testing then let me know at the contact details on the about page and help improve the knowledge of Oracle testing for all.
TPC-H
More detailed TPC-H documentation
is currently in progress (July 2008)
The TPC-H
implementation is provided as a wrapper around the
dbgen and qgen executavbles in the dss directory. To create the
TPC-H schema fill out the details in the TPC-H Schema Options dialog
and select the schema creation button.
You
should note that the the TPC-H schema
creation may be time consuming and for larger confugrations you may
wish to investigage schema creations by using dbgen manually and
SQL*Loader to load the files created. To assist in this
process I recommend creating a scale factor 1 schema with tables and
indexes as follows
SQL> select *
from tab;
TNAME
TABTYPE CLUSTERID
------------------------------
------- ----------
PARTSUPP
TABLE
ORDERS
TABLE
CUSTOMER
TABLE
PART
TABLE
SUPPLIER
TABLE
NATION
TABLE
REGION
TABLE
LINEITEM
TABLE
8 rows
selected.
1*
select index_name from ind
SQL> /
INDEX_NAME
------------------------------
I_L_ORDERKEY
I_C_CUSTKEY
I_O_ORDERKEY
I_PS_PARTKEY_SUPPKEY
You can then
use dbms_metadata to capture the table definitions
SQL> set long
2000
SQL>
select dbms_metadata.get_ddl('TABLE','LINEITEM') from dual;
DBMS_METADATA.GET_DDL('TABLE','LINEITEM')
--------------------------------------------------------------------------------
CREATE
TABLE "TPCH"."LINEITEM"
( "L_SHIPDATE" DATE,
"L_ORDERKEY" NUMBER NOT NULL
ENABLE,
"L_DISCOUNT" NUMBER NOT NULL
ENABLE,
"L_EXTENDEDPRICE" NUMBER NOT
NULL ENABLE,
"L_SUPPKEY" NUMBER NOT NULL
ENABLE,
"L_QUANTITY" NUMBER NOT NULL
ENABLE,
"L_RETURNFLAG" CHAR(1),
"L_PARTKEY" NUMBER NOT NULL
ENABLE,
"L_LINESTATUS" CHAR(1),
"L_TAX" NUMBER NOT NULL
ENABLE,
"L_COMMITDATE" DATE,
"L_RECEIPTDATE" DATE,
"L_SHIPMODE" CHAR(10),
"L_LINENUMBER" NUMBER NOT
NULL ENABLE,
"L_SHIPINSTRUCT" CHAR(25),
"L_COMMENT" VARCHAR2(44)
) PCTFREE 2 PCTUSED 98 INITRANS 8 MAXTRANS 255 NOCOMPRESS
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TPCH"
PARALLEL
SQL>
SQL>
select dbms_metadata.get_ddl('INDEX','I_L_ORDERKEY') from dual;
DBMS_METADATA.GET_DDL('INDEX','I_L_ORDERKEY')
--------------------------------------------------------------------------------
CREATE
INDEX "TPCH"."I_L_ORDERKEY" ON "TPCH"."LINEITEM"
("L_ORDERKEY")
PCTFREE 2 INITRANS 10 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TPCH"
PARALLEL
By capturing these
definitions you can re-create the schema
for loading with SQL*Loader.
FLASHBACK TABLE LINEITEM TO TIMESTAMP
TO_TIMESTAMP('05-FEB-07
8.42.00.00 PM')
FLASHBACK TABLE ORDERS TO TIMESTAMP TO_TIMESTAMP('05-FEB-07 8.42.00.00
PM')