DSS Benchchmark.
The latest build of swingbench 2.3 (from build 119) includes a new benchmark capable of generating a more I/O intensive workload. Its based on the “Sales History” schema that ships with Oracle 10g. Based on how much data is generated this benchmark should generate a large amount of disk activity in comparison to the “Order Entry” and “Calling Circle” benchmarks. Currently little tuning has been done against this benchmark and the layout for the storage/partitioning is sub optimal, this of course can be be easily rectified by an experienced DBA. The schema also doesn't use any materialized views on the basis that this would reduce I/O by a significant amount, these are likely to included as an option at some stage in the future. The installation process is more manual than the existing benchmarks, this will change over the coming months.
Generating the data
To install and run the DSS benchmark you'll need both Datagenerator to create the data and Swingbench to generate the load. Installation guides can be found here.
Datagenerator ships with a configuration file for the “Sales History” schema. It can be loaded into Datagenerator with the following command.
$> cd bin
$> ./datagenerator -c sh.xml
Or from within Datagenerator by selecting the open icon and select the “sh.xml” located in the bin or winbin directory. This will launch the following window
The config file contains all of the reference data to build the entire schema but is relatively small in size. The estimated data size shown in the top right of the window is currently based on the size of the data generated and will require significantly less space if technologies like compression is used (default in this benchmark). This can be scaled up (or down) by select the sizer icon. Click on this icon to launch the sizer window. The following dialogue will be displayed.
The slider bar increase the row count in a table by a percentage. However the only tables that need to be selected to scale up this benchmark is those from SALES_1995 to CUSTOMERS. Select these by dragging the mouse from SALES_1995 to CUSTOMERS ie.
Then move the slider across performing this operation will increase all of the tables selected in proportion to one another. Press Finish when the row count reflects your requirements. On returning to the main screen the estimated size will reflect the new row counts.
Pressing the play button will begin the data generation process. This process uses a thread per table and so should utilize all of the CPU's on a server. Some of the tables such “CUSTOMERS” and “SUPPLEMENTARY_DEMOGRAPHICS” will take considerably longer to generate that the others. It is possible to improve this situation by dividing the table into smaller partitions and merging them at the end. If the user chooses to do this it is important to update the tables that use the customer_id as a foreign key (CUSTOMERS). Pressing the play button will launch the following dialogue.
On completion the datagenerator will have created a directory (“data” by default) with
- Data files containing the data to be loaded.
- DDL creation scripts for the tables and referential integrity.
- Loader scripts to load the data.
Creating the schema
To generate the the schema for the “Sales History” benchmark the author has included a series of scripts included under the “$DATAGENERATOR_HOME/bin/scripts/sh” directory. To configure the scripts for your particular environment edit the “sh_install.sql” file.
-- These scripts should be used as a guide. they currently don't parallelise any of the loads or specify individual tablespaces fort he tables/partitions.
define tablespace=SH
define tablespace_size=500M
define datafile='/home/oracle/orabase/product/10.2/db/oradata/DOM102/datafile/sh.dbf'
define indextablespace=SH_INDEX
define indextablespace_size=200M
define indexdatafile='/home/oracle/orabase/product/10.2/db/oradata/DOM102/datafile/shindex.dbf'
define username=sh
define password=sh
define indexprefs=nologging
prompt This script will create a user : &username
prompt With a password : &password
prompt And set its default tablespace to : &tablespace
prompt It will put its indexes in this tablespace : &indextablespace
prompt If this isnt correct hit control C and edit this script, otherwise hit return
accept firstpause
@@sh_createtablespace.sql
@@sh_user.sql
connect &username/&password
@@sh_tables.sql
prompt Now run the loader scripts in another window, theyll be in the data directory.
prompt In unix/linux a command like this will do it. for i in `ls *.ctl`; do sqlldr sh/sh control=$i; done
prompt Hit return when youve loaded them
accept secondpause
prompt
prompt Tidying up, building indexes, analysing data... This may take a while...
prompt
@@sh_exchange.sql
@@sh_constraints.sql
@@sh_indexes.sql
@@sh_tidyup.sql
exec dbms_stats.gather_schema_stats(SYS_CONTEXT('USERENV','CURRENT_SCHEMA'));
prompt
prompt Completed building Sales history schema.
exit;
The text highlighted in red are the values that need to be changed to reflect your own particular preferences.
Log onto a database via SQL*Plus with “sys” privileges in the scripts directory and type
@sh_install
This will start the install and display the following text
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jul 17 16:15:15 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning and Data Mining options
SYS@DOM102 > @sh_install
This script will create a user : sh
With a password : sh
And set its default tablespace to : SH
It will put its indexes in this tablespace : SH_INDEX
If this isnt correct hit control C and edit this script, otherwise hit return
This will take a few moments to create the table definitions and then pause whilst you load the data from the data generation process. In another window run sqlldr to load the data. This can be done in Unix/Linux by running a script such as
for i in `ls *.ctl`; do sqlldr sh/sh control=$i; done
On Windows running the following command in the directory where the data was generated will load the data.
for %x in (*.ctl) do sqlldr sh/sh control=%x
After the data load has completed return to the window running the sqlplus and hit return, this will complete the schema creation. The script will log you of SQL*Plus on competition.
NOTE : If your building very large schema's I'd recommend modify the scripts to parallelise the load and index process (This functionality will be included in future releases).
Running the benchmark
Swingbench now includes a new configuration and java classes to run a load against the “Sales History” schema, this is located in the “$SWINGBENCH_HOME/bin/sample directory”. To launch swingbench with the new configuration file type
$> ./swingbench -c sample/shconfig.xml
from the bin/winbin directory.
NOTE: It might also be necessary to edit sample/shconfig.xml to update some the benchmark parameters which aren't currently available in the GUI (will be included shortly).
It can also be loaded after the initial launch of swingbench. This will launch the following dialogue
Check that the connect string matches your database and press play. Depending on the size of your database it may take a while for the transactions to start registering results.