util
Swingbench Util... Going Big...
07/03/15 16:55 Filed in: Swingbench
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
will duplicate the data in the soe schema but will first sort the seed data. You should see output similar to this
The following example validates a schema to ensure that the tables and indexes inside a schema are all present and valid
The output of the command will look similar to to this
The next command lists the tables in a schema
To drop the indexes in a schema use the following command
To recreate the indexes in a schema use the following command
You can download the new version of the software here.
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
- “-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”
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