swingbench large schema indexes temp soe

Large SOE builds... things to watch for

A couple of things to watch for if you are building a large SOE schema. The first is temp space. I guess its obvious but if you are building a 1TB schema with 100GB+ tables the indexes are going to be pretty big as well. If you are creating big indexes you need plenty of TEMP. The number of schema’s I’ve looked at that haven’t had their indexes build is amazing. I guess this is partly my fault as well. I’ll include a start and end validation process in the next build. Should have done this before but I guess people weren’t building such big schema’s

As a guide line for a schema of size “x” I’d have at least “x/6” worth of temp space i.e. 1TB schema needs about 180GB of temp. You can resize it after the build to what ever you decide is appropriate.

As to what it should look like on completion... well something like this
SOE@//localhost/orcl > @tables;Tables======Table Rows Blocks Size Compression  Indexes Partitions Analyzed-------------------- ---------- ----- ------ ----------- ------- ---------- ----------WAREHOUSES       1,000     60    1024k Disabled    2       0 < WeekORDERS     225,000  1,636      13M Disabled    5       0 < WeekINVENTORIES     924,859 10,996      87M Disabled    3       0 < WeekORDER_ITEMS     587,151  2,392      19M Disabled    3       0 < WeekPRODUCT_DESCRIPTIONS 1,000     60    1024k Disabled    2       0 < WeekLOGON      50,000    250       2M Disabled    0       0 < WeekPRODUCT_INFORMATION   1,000     60    1024k Disabled    3       0 < WeekCUSTOMERS     200,000  2,014      16M Disabled    5       0 < Week

Another really important thing is to include the SOE_MIN_CUSTOMER_ID and SOE_MAX_CUSTOMER_ID in the environment variables within the config file. This will reduce the startup time of the benchmark. Follow the instructions below or edit the config file

Select the Environment Variables tab and press the button (you’ll need to do this for each environment variable).

Add two Enviroment variables
  • SOE_MIN_CUSTOMER_ID : The value equals the smallest customer id in the data set, usually 1
  • SOE_MAX_CUSTOMER_ID : The largerst customer id found in the data set

You can determine what thes values are by running a piece of SQL similar to this when logged into the SOE schema


After adding the variables you should end up with something that looks similar to this