Toad World® Forums

How to create specific objects for a benchmark without creating all of them


#1

You can create a Load Scenario that will only create or delete only the objects you wish. The procedure is below.
a) On the Script tab select the root item and choose Wizards Load Scenario and click Next.
b) Choose Create a custom load scenario from the bottom of the list and click next.
c) Select the Replay radio button and choose next.
d) Click the Add User Scenario button and click next on the User Scenario wizard
e) Choose Create a custom user scenario and click next
f) Click on the Add SQL statement button and click next on the Transaction Wizard
g) Select Import Transaction and click next
h) Select the TPC-C Benchmark in the Benchmark drop down
i) Check the Show creation objects check box at the bottom of the screen
j) You may now select the objects you wish to create. If you wish to create one type only it may be easier if you use the Transaction Type drop down to filter the list. Click next twice and finish the wizard.
k) Now you are back in the User Scenario wizard. Make sure your items are in the correct order and adjust with the arrow buttons if necessary. For example, you will need to create a table before the indexes are created. Click Next
l) Enter a name; click next and finish the User scenario wizard.
m) Use next and finish buttons to finish the Load Scenario wizard and your load scenario will be created.


#2

I tried using this procedure to create just the indices for the tpc-h database: All of the index create transactions failed since they tried to use the SYSTEM tablespace (although the default tablespace for the oracle user was different). To change the target tablespace I can’t edit the transactions because “This transaction is imported from a benchmark dll and is not editable.”. Is there a script to edit or a parameter to set that I may have missed?

How can I create just the database objects (table or index) I need on the tablespace I want?

Thanks


#3

Make sure that the benchmark mapping options are not set. This will allow the indexes to be created on the default user tablespace. See the tpch_mapping_off image above.

Or if you want to control where they are set, check the mapping options box (make sure Oracle is selected) and then select the indexes, or tables, and select edit. This will bring up a dialog where you can set options including tablespace.
tpch_mapping_on.jpeg


#4

Make sure that the benchmark mapping options are not set. This will allow the indexes to be created on the default user tablespace. See the tpch_mapping_off image above.

Or if you want to control where they are set, check the mapping options box (make sure Oracle is selected) and then select the indexes, or tables, and select edit. This will bring up a dialog where you can set options including tablespace.
tpch_mapping_off.jpeg


#5

kmd,
Thanks for your reply. I believe the method you describe will re-create all of the database objects and then reload data into the tables ---- this is precisely what I want to avoid. The process Tracy outlines allows one to create specific database objects such as particular index or table without the overhead of re-creating the entire database. The problem with Tracy’s approach is that the “transactions” are implemented as DLLs which can not be edited and the tablespace names appear to be hard-coded/embedded in the DLL. For example, the “Create index H_supplier_idx1” DLL ignores the so called “advanced” tablespace options that have been set in the “Benchmark TPC-H Properties”.

How would you create/re-create just the “H_supplier_idx1” index on tablespace IDX for an existing 100-scale TPC-H database?


#6

I’m sorry, I was not clear on my last post.

If you follow the procedure that Tracey previosly posted to drop and then create the desired indexes. After that particular test is created, you can then modify the benchmark options as I mentioned in my last post to specify what tablespace(s) you want the indexes created on. That way the entire benchmark tables are not recreated, just the indexes are recreated that you specify with your created test. I have added a screenshot of sample that I created for the AS3AP.
custom_creation.jpeg


#7

Thanks kmd for the additional explanation. I got the procedure to work fine.