Creating a Hammerora TPC-C database

For my next project I wanted to use an Oracle benchmark, something that will help me measure the database performance. After looking around a bit I narrowed it down to Hammerora and Swingbench for the benchmark and stressing the database.

In this post I'll talk about installing and configuring Hammerora, just something basic to see if I can get it to work.

To begin with, I created a small database call "tpcc" using Oracle dbca. Nothing fancy just the basic default database that dbca creates.

[oracle@isvx7 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 10 13:58:21 2011 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select tablespace_name from user_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS SQL> SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ /oradata/tpcc/users01.dbf USERS /oradata/tpcc/undotbs01.dbf UNDOTBS1 /oradata/tpcc/sysaux01.dbf SYSAUX FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ /oradata/tpcc/system01.dbf SYSTEM SQL>

As my server is a x86_64 server running RedHat Linux 5 I downloaded the appropriate Hammerora binaries from http://sourceforge.net/projects/hammerora/files/hammerora/hammerora-2.5/hammerora-2.5-Linux-x86-64/download

 I then executed the hammerora binary as user oracle.

[oracle@isvx7 ~]$ ./hammerora-2.5-Linux-x86-64 [oracle@isvx7 ~]$ WARNING : Failed to load mysqltcl package -  this package is not mandatory for Hammerora Oracle or Web testing functionality but is required for MySQL testing, it requires at least the MySQL client libraries to be installed and the LD_LIBRARY_PATH environment variable set - see http://hammerora.sourceforge.net

Below are the screenshots to install and configure hammerora

 

Select the "TPC-C Schema Options" as shown below

 

• "Oracle Service Name" is the name of the database that we had created earlier using dbca which is tpcc. 

• "System user password" is the password that we had given while creating the database using dbca.

• "TPC-C User" is the name of the new user that hammerora will create for us. I gave the name tpcc to the user, but you can when him anything.

• "TPC-C User Password" is the password for the hammerora user that will be created.

• "TPC-C Default Tablespace" I used the users tablespace that dbca had created, as this was a quick and dirty test. Ideally it would have been best to create and new tablespace and passs that to this field.

• "TPC-C Temporary Tablespace" I used temp for this one.

Now let us check what has been created. To find the tables created by user tpcc, connect as user tcpc to the database and then run.

[oracle@isvx7 ~]$ sqlplus tpcc/tpcc@tpcc SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 10 12:18:20 2011 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select table_name from user_tables; TABLE_NAME ------------------------------ CUSTOMER DISTRICT HISTORY ITEM WAREHOUSE STOCK NEW_ORDER ORDERS ORDER_LINE 9 rows selected. SQL>

To show the current database

SQL> SELECT * FROM global_name; GLOBAL_NAME -------------------------------------------------------------------------------- TPCC SQL>

To show the current user

SQL> show user; USER is "TPCC" SQL>

To describe a table

SQL> desc warehouse;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  W_ID                                               NUMBER(4)  W_YTD                                              NUMBER(12,2)  W_TAX                                              NUMBER(4,4)  W_NAME                                             VARCHAR2(10)  W_STREET_1                                         VARCHAR2(20)  W_STREET_2                                         VARCHAR2(20)  W_CITY                                             VARCHAR2(20)  W_STATE                                            CHAR(2)  W_ZIP                                              CHAR(9) SQL> desc customer;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  C_ID                                               NUMBER(5)  C_D_ID                                             NUMBER(2)  C_W_ID                                             NUMBER(4)  C_FIRST                                            VARCHAR2(16)  C_MIDDLE                                           CHAR(2)  C_LAST                                             VARCHAR2(16)  C_STREET_1                                         VARCHAR2(20)  C_STREET_2                                         VARCHAR2(20)  C_CITY                                             VARCHAR2(20)  C_STATE                                            CHAR(2)  C_ZIP                                              CHAR(9)  C_PHONE                                            CHAR(16)  C_SINCE                                            DATE  C_CREDIT                                           CHAR(2)  C_CREDIT_LIM                                       NUMBER(12,2)  C_DISCOUNT                                         NUMBER(4,4)  C_BALANCE                                          NUMBER(12,2)  C_YTD_PAYMENT                                      NUMBER(12,2)  C_PAYMENT_CNT                                      NUMBER(8)  C_DELIVERY_CNT                                     NUMBER(8)  C_DATA                                             VARCHAR2(500) SQL>

When creating a schema with 25 Warehouses, here are the number of rows per table that gets created by Hammerora.

SQL> select count(*) from customer;   COUNT(*) ----------     750000 SQL> select count(*) from district;   COUNT(*) ----------        250 SQL> select count (*) from history;   COUNT(*) ----------     750000 SQL> select count (*) from item;   COUNT(*) ----------     100000 SQL> select count(*) from warehouse;   COUNT(*) ----------         25 SQL> select count(*) from stock;   COUNT(*) ----------    2500000 SQL> select count(*) from new_order;   COUNT(*) ----------     225000 SQL> select count(*) from orders;   COUNT(*) ----------     750000 SQL> select count(*) from orders;   COUNT(*) ----------     750000 SQL>