Swingbench Util... Going Big...

I’ve added a utility to swingbench that perhaps I should’ve done a long time ago. The idea behind it is that it enables you to validate, fix or duplicate the data created by the wizards. I’m often contacted by people asking me how to fix an install of “Order Entry” or the “Sales History” benchmark after they’ve been running for many hours only to find they run out of temp space for the index creation. I’m also asked how they can make a really big “Sales History” schema when they only have relatively limited compute power and to create a multi terabyte data set might take days. Well the good news is that now, most of this should be relatively easy. The solution is a little program called “subtil” and you can find it in your bin or win bin directory.

Currently sbutil is command line only and requires a number of parameters to get it to do anything useful. The main parameters are
  • “-dup” indicates the number of times you want data to be duplicated within the schema. Valid values are 1 to n. Data is copied and new primary keys/foreign keys generated where necessary. It’s recommended that you first increase/extend the tablespace before beginning the duplication. The duplication process will also rebuild the indexes and update the metadata table unless you specifically ask it not to with the “-nic” option. This is useful if you know you’ll be reduplicated the data again at a later stage.
  • “-val” validates the tables and indexes in the specified schema. It will list any missing indexes or invalid code.
  • “-stats” will create/recreate statistics for the indicated schema
  • “-delstats” will delete all of the statistics for the indicated schema
  • “-tables” will list all of the tables and row counts (based on database statistics) for the indicated schema
  • “-di” will drop all of the indexes for the indicated schema
  • “-ci” will recreate all of the indexes for the indicated schema
Alongside of these verb based parameters are a number of associated attributes
  • “-u” : required. the username of the schema
  • “-p” : required. the password of the schema
  • “-cs” : required. the connect string of the schema. Some examples might be “localhost:1521:db12c”, “//oracleserver/soe” “//linuxserver:1526/orcl” etc.
  • “-parallel” the level of parallelism to use to perform operations. Valid values are 1 to n.
  • “-sort” sort the seed data before duplicating it.
  • “-nic” don’t create indexes or constraints at the end of a duplication
  • “-ac” convert the “main” tables to advanced compression
  • “-hcc” convert the main tables to Hybrid Columnar Compression
  • “-soe” : required. the target schema will be “Order Entry”
  • “-sh” : required. the target schema will be “Sales History”
So lets take a look at a few examples.

sbutil -u soe -p soe -cs //oracleserver/soe -dup 2 -parallel 32 -sort -soe

will duplicate the data in the soe schema but will first sort the seed data. You should see output similar to this

Getting table Info
Got table information. Completed in : 0:00:26.927
Dropping Indexes
Dropped Indexes. Completed in : 0:00:05.198
Creating copies of tables
Created copies of tables. Completed in : 0:00:00.452
Begining data duplication
Completed Iteration 2. Completed in : 0:00:32.138                                                   
Creating  Constraints
Created  Constraints. Completed in : 0:04:39.056
Creating  Indexes
Created  Indexes. Completed in : 0:02:52.198
Updating Metadata and Recompiling Code
Updated Metadata. Completed in : 0:00:02.032
Determining New Row Counts
Got New Row Counts. Completed in : 0:00:05.606
Completed Data Duplication in 0 hour(s) 9 minute(s) 44 second(s) 964 millisecond(s)
----------------------------------------------------------------------------------------------------------
|Table Name          |  Original Row Count|       Original Size|       New Row Count|            New Size|
----------------------------------------------------------------------------------------------------------
|ORDER_ITEMS         |         172,605,912|             11.7 GB|         345,211,824|             23.2 GB|
|CUSTOMERS           |          40,149,958|              5.5 GB|          80,299,916|             10.9 GB|
|CARD_DETAILS        |          60,149,958|              3.4 GB|         120,299,916|              6.8 GB|
|ORDERS              |          57,587,049|              6.7 GB|         115,174,098|             13.3 GB|
|ADDRESSES           |          60,174,782|              5.7 GB|         120,349,564|             11.4 GB|
----------------------------------------------------------------------------------------------------------


The following example validates a schema to ensure that the tables and indexes inside a schema are all present and valid


./sbutil -u soe -p soe -cs //ed2xcomp01/DOMS -soe -val

The output of the command will look similar to to this

The Order Entry Schema appears to be valid.

--------------------------------------------------
|Object Type    |     Valid|   Invalid|   Missing|
--------------------------------------------------
|Table          |        10|         0|         0|
|Index          |        26|         0|         0|
|Sequence       |         5|         0|         0|
|View           |         2|         0|         0|
|Code           |         1|         0|         0|
--------------------------------------------------


The next command lists the tables in a schema


./sbutil -u soe -p soe -cs //ed2xcomp01/DOMS -soe -tables Order Entry Schemas Tables ---------------------------------------------------------------------------------------------------------------------- |Table Name | Rows| Blocks| Size| Compressed?| Partitioned?| ---------------------------------------------------------------------------------------------------------------------- |ORDER_ITEMS | 17,157,056| 152,488| 11.6GB| | Yes| |ORDERS | 5,719,160| 87,691| 6.7GB| | Yes| |ADDRESSES | 6,000,000| 75,229| 5.7GB| | Yes| |CUSTOMERS | 4,000,000| 72,637| 5.5GB| | Yes| |CARD_DETAILS | 6,000,000| 44,960| 3.4GB| | Yes| |LOGON | 0| 0| 101.0MB| | Yes| |INVENTORIES | 0| 0| 87.0MB| Disabled| No| |PRODUCT_DESCRIPTIONS | 0| 0| 1024KB| Disabled| No| |WAREHOUSES | 0| 0| 1024KB| Disabled| No| |PRODUCT_INFORMATION | 0| 0| 1024KB| Disabled| No| |ORDERENTRY_METADATA | 0| 0| 1024KB| Disabled| No| ----------------------------------------------------------------------------------------------------------------------


To drop the indexes in a schema use the following command
./sbutil -u sh -p sh -cs //oracle12c2/soe -sh -di
Dropping Indexes
Dropped Indexes. Completed in : 0:00:00.925 


To recreate the indexes in a schema use the following command
./sbutil -u sh -p sh -cs //oracle12c2/soe -sh -ci
Creating Partitioned Indexes and Constraints
Created  Indexes and Constraints. Completed in : 0:00:03.395

You can download the new version of the software here.
Comments