Toad World® Forums

Running the same tpc test without deleting the db objects between tests


#1

If I create a large database for a TPC test, would it be possible to run the same test over and over without rebuilding the database objects each time and still get reliable results? I would bounce the instance to clear anything in memory so it wouldn’t skew the statistics. Since it could take hours to create the TPC DB objects and if i looked at a statspack or awr report during this job, the stats would be skewed because of the millions on inserts.
thanks


#2

You really need to reload the database between test runs since the TPC-C does updates/inserts/deletes to the TPC-C database, just running the test will change the database, so if you really want to get reliable and reproducible results it should be reloaded.

Now with that said it sounds like the issue is that you capture statspack/awr snapshots before and after running the test and with the inserts they throw of the reports. An easy way to get around this is to allow the BMF job to do the snapshots for you before and after the actual test. To do this you need to;

  1. Select the Create Object for the TPC-C step
  2. Insert an “Execute File” test and go through the wizard to insert the sqlplus command to execute a snapshot
  3. Insert another “Execute File” after the TPC-C Transaction Mix test

See the attached screenshot for an example.

You can do the same type of thing for the AWR or just include it in the script executed by the existing Execute File. Please refer to this community video about using the Execute File as part of the job.

http://benchmarkfactory.inside.quest.com/entry.jspa?externalID=1702&categoryID=27

Also there are ways to speed up the dataload. Please refer to this discussion thread.

http://benchmarkfactory.inside.quest.com/thread.jspa?threadID=2653&tstart=15
Execute_Snapshot.jpeg


#3

thanks. i will implement initiating the snapshots after creation.
also, if i was running a tpc-h job, since it is all reads, i wouldn’t think i would need to recreate the DB objects for each run. your thoughts on this?
thanks


#4

You are correct, since the TPC-H is a read-only test, there is no reason to reload the objects between test runs.


#5

Just as an additional note - you should be aware of how snapshots work when considering this method. I typically run the create job as a separate process, then create a full backup before executing the test. When it is time to re-populate I do a restore instead.

Both methods get you to the same place, but they have different implications in terms of performance.

Hope this helps.

John