This is a little note, primarily to myself I guess, about the creation of the order entry schema (part of Swingbench, written by Dominic Giles) when no VNC sessions are available (although you can almost always use port-forwarding :). Instead, you can create the schema on the command line. I always execute commands on remote systems in screen for increased peace of mind. Should the network drop, the order entry generation will continue as if nothing ever happened.
Like many others I use Swingbench during trainings and presentations to have some activity on a system. Very useful for demonstrating ASH and OEM, and many other things too!
This article references swingbench 2.5, the latest version at the time of writing. It requires a JDK to be present, I simply used the one bundled with Oracle 12.1.0.2.0 which is my target database version. You first start by downloading Swingbench from Dominic’s website and unzip it in a directory from where you want to run it. It is usually a good idea to keep the load generator on a different (set of) machine(s). As long as you can connect to the database you want to use to house the SOE schema you’re fine.
Creating the SOE schema
Before you can run swingbench (or minibench/charbench) you need to create the SOE schema. The oewizard is the tool of choice. If you haven’t done so, add java to your path. Then you can have a look at the available options:
$ JAVA_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1/jdk
$ export PATH=$PATH:$JAVA_HOME/bin
$ ./oewizard -h
usage: parameters:
-allindexes build all indexes for schema
-bigfile use big file tablespaces
-c <filename> wizard config file
-cl run in character mode
-compositepart use a composite paritioning model if it exisits
-compress use default compression model if it exists
-create create benchmarks schema
-cs <connectString> connectring for database
-dba <username> dba username for schema creation
-dbap <password> password for schema creation
-debug turn on debugging output
-debugf turn on debugging output to file (debug.log)
-df <datafile> datafile name used to create schema in
-drop drop benchmarks schema
-dt <driverType> driver type (oci|thin)
-g run in graphical mode (default)
-generate generate data for benchmark if available
-h,--help print this message
-hashpart use hash paritioning model if it exists
-hcccompress use HCC compression if it exisits
-nocompress don't use any database compression
-noindexes don't build any indexes for schema
-nopart don't use any database partitioning
-normalfile use normal file tablespaces
-oltpcompress use OLTP compression if it exisits
-p <password> password for benchmark schema
-part use default paritioning model if it exists
-pkindexes only create primary keys for schema
-rangepart use a range paritioning model if it exisits
-s run in silent mode
-scale <scale> mulitiplier for default config
-sp <soft partitions> the number of softparitions used. Defaults to cpu
count
-tc <thread count> the number of threads(parallelism) used to
generate data. Defaults to cpus*2
-ts <tablespace> tablespace to create schema in
-u <username> username for benchmark schema
-v run in verbose mode when running from command
line
-version <version> version of the benchmark to run
Note that not all partitioning schemes are valid in this case, and neither are all compression methods. I found the below command to create the schema the way I want it.
./oewizard -allindexes -cl -create -cs //cluster-scan/sbench -dba martin \
> -dbap notTellingYouHere -ts soe_tbs -part -p soe -scale 8 -tc 8 -u soe -v
SwingBench Wizard
Author : Dominic Giles
Version : 2.5.0.949
Running in Lights Out Mode using config file : oewizard.xml
Connecting to : jdbc:oracle:thin:@//cluster-scan/sbench
Connected
Starting script ../sql/soedgcreateuser.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 104 millisecond(s)
Starting run
Starting script ../sql/soedgdrop2.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 55 millisecond(s)
Starting script ../sql/soedgcreatetableshash2.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 520 millisecond(s)
Starting script ../sql/soedgviews.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 19 millisecond(s)
Starting script ../sql/soedgsqlset.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 84 millisecond(s)
Inserting data into table ADDRESSES_11499978
Inserting data into table ADDRESSES_3999993
-- more data
Completed processing table LOGON_7943271 in 0:00:25
Connection cache closed
Starting script ../sql/soedganalyzeschema2.sql
Script completed in 0 hour(s) 1 minute(s) 20 second(s) 701 millisecond(s)
Starting script ../sql/soedgconstraints2.sql
Script completed in 0 hour(s) 1 minute(s) 41 second(s) 520 millisecond(s)
Starting script ../sql/soedgindexes2.sql
Script completed in 0 hour(s) 0 minute(s) 44 second(s) 685 millisecond(s)
Starting script ../sql/soedgsequences2.sql
Script completed in 0 hour(s) 0 minute(s) 3 second(s) 53 millisecond(s)
Starting script ../sql/soedgpackage2_header.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 215 millisecond(s)
Starting script ../sql/soedgpackage2_body.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 127 millisecond(s)
Starting script ../sql/soedgsetupmetadata.sql
Script completed in 0 hour(s) 0 minute(s) 0 second(s) 614 millisecond(s)
============================================
| Datagenerator Run Stats |
============================================
Connection Time 0:00:00.005
Data Generation Time 0:10:38.185
DDL Creation Time 0:03:51.630
Total Run Time 0:14:29.823
Rows Inserted per sec 151,713
Data Generated (MB) per sec 12.1
Actual Rows Generated 97,718,288
Connecting to : jdbc:oracle:thin:@//cluster-scan/sbench
Connected
Post Creation Validation Report
===============================
The creation of the schema appears to have been unsuccessful. See the following sections for further details.
Valid Objects
=============
Valid Tables : 'ORDERS','ORDER_ITEMS','CUSTOMERS','WAREHOUSES','ORDERENTRY_METADATA','INVENTORIES',
'PRODUCT_INFORMATION','PRODUCT_DESCRIPTIONS','ADDRESSES','CARD_DETAILS'
Valid Indexes : 'ORD_CUSTOMER_IX','ORD_ORDER_DATE_IX','ORD_WAREHOUSE_IX','ORDER_ITEMS_PK','ITEM_ORDER_IX',
'ITEM_PRODUCT_IX','CUST_EMAIL_IX','CUST_ACCOUNT_MANAGER_IX','CUST_FUNC_LOWER_NAME_IX',
'CARDDETAILS_CUST_IX''PRD_DESC_PK','PROD_NAME_IX','PRODUCT_INFORMATION_PK','PROD_SUPPLIER_IX',
'PROD_CATEGORY_IX','INVENTORY_PK','INV_PRODUCT_IX','INV_WAREHOUSE_IX','ORDER_PK',
'ORD_SALES_REP_IX','WAREHOUSES_PK','WHS_LOCATION_IX','CUSTOMERS_PK','ADDRESS_PK',
'ADDRESS_CUST_IX',
'CARD_DETAILS_PK'
Valid Views : 'PRODUCTS','PRODUCT_PRICES'
Valid Sequences : 'CUSTOMER_SEQ','ORDERS_SEQ','ADDRESS_SEQ','LOGON_SEQ','CARD_DETAILS_SEQ'
Valid Code :
Invalid Objects (1)
================
Invalid Tables :
Invalid Indexes :
Invalid Views :
Invalid Sequences :
Invalid Code : 'ORDERENTRY'
Missing Objects (0)
================
Missing Tables :
Missing Indexes :
Missing Views :
Missing Sequences :
Missing Code :
Schema Created
To compile the package SYS needs to grant execute on DBMS_LOCK to SOE.
This results in a schema with the following number of rows:
SQL> declare
2 v_cnt number;
3 begin
4 for i in (select table_name from dba_tables where owner='SOE') loop
5 execute immediate 'select count(*) from soe.' || i.table_name into v_cnt;
6 dbms_output.put_line(i.table_name || ' - ' || to_char(v_cnt, '999,999,999'));
7 end loop;
8 end;
9 /
ORDER_ITEMS - 34,316,243
ORDERS - 11,438,320
INVENTORIES - 896,853
PRODUCT_INFORMATION - 1,000
LOGON - 19,063,872
PRODUCT_DESCRIPTIONS - 1,000
ORDERENTRY_METADATA - 4
CUSTOMERS - 8,000,000
ADDRESSES - 12,000,000
CARD_DETAILS - 12,000,000
WAREHOUSES - 1,000
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.70
SQL> compute sum of m on report
SQL> break on report
SQL> select sum(bytes)/power(1024,2) m, segment_name, segment_type
2 from dba_segments where owner = 'SOE'
3 group by segment_name, segment_type
4 order by 1;
M SEGMENT_NAME SEGMENT_TYPE
--------------- ------------------------------ ------------------
.06 WAREHOUSES TABLE
.06 PRODUCT_INFORMATION_PK INDEX
.06 ORDERENTRY_METADATA TABLE
.13 WHS_LOCATION_IX INDEX
.13 PROD_CATEGORY_IX INDEX
.13 WAREHOUSES_PK INDEX
.13 PROD_SUPPLIER_IX INDEX
.13 PROD_NAME_IX INDEX
.13 PRD_DESC_PK INDEX
.25 PRODUCT_INFORMATION TABLE
.31 PRODUCT_DESCRIPTIONS TABLE
16.69 INV_WAREHOUSE_IX INDEX
16.69 INV_PRODUCT_IX INDEX
18.00 INVENTORY_PK INDEX
88.00 INVENTORIES TABLE
169.00 CUST_ACCOUNT_MANAGER_IX INDEX
177.06 CUSTOMERS_PK INDEX
204.06 CUST_DOB_IX INDEX
242.19 ORD_SALES_REP_IX INDEX
253.38 ORDER_PK INDEX
254.31 CUST_FUNC_LOWER_NAME_IX INDEX
265.06 ORD_CUSTOMER_IX INDEX
265.69 ADDRESS_PK INDEX
265.75 CARD_DETAILS_PK INDEX
276.88 CARDDETAILS_CUST_IX INDEX
278.13 ORD_WAREHOUSE_IX INDEX
278.50 ADDRESS_CUST_IX INDEX
291.88 ORD_ORDER_DATE_IX INDEX
347.81 CUST_EMAIL_IX INDEX
717.88 ITEM_PRODUCT_IX INDEX
788.94 ITEM_ORDER_IX INDEX
864.00 LOGON TABLE PARTITION
968.00 CARD_DETAILS TABLE PARTITION
1,352.00 CUSTOMERS TABLE PARTITION
1,408.00 ADDRESSES TABLE PARTITION
1,544.00 ORDERS TABLE PARTITION
2,368.00 ORDER_ITEMS TABLE PARTITION
---------------
13,721.38
37 rows selected.
Happy benchmarking!
Martins Blog 