Toad World® Forums

Is there a faster way to create a test database?

I have been creating several test databases to test a hardware platformunderload but I have been running into time constrains for building adatabase. For a TPC-H database with a scale factor of 22 (estimated 22GB size for just the tables). This takes many hours, and only createsdata at 4-7 MB/s, when under the actual TPC-H test the queries run at800 MB/s. I am trying to vary the benckmark factory settings to testthe limits of the storage, so I am starting a test run, monitoring whatit does to the storage, then changing to a different test/scalefactor/etc to feel out which is the best test. The many hours ofcreate time between tests, however, is really holding me back.

This post was originally created on the Code Tester forum by user: JAMON.

If this is being used agaisnt a MSSQL database are you using the ODBC, or native client within Benchmark Factory? If you are using the ODBC client try using the native client (much faster). Also regardless of the database engine, when you created your database did you give it an initial size equal to or greater than the size of the database you are wanting to create? If not try that, and I am sure you will experience a faster database creation. however I am curious as to why you are creating the database over and over unless you are using different scale factors? if you are just adjusting the think time, etc… then just create your database once, detach it, copy it, and then reattach the database to run your test, when done, either reindex or detach, copy attach.

The way I get around the database creation time is to use pre-test or pre-iteration scripts to rebuild my database. So my steps go like this:

  1. create BMF project to just populate the objects
  2. run this - it may take some time
  3. create a second schema or database to copy those objects using CTAS (create table as select)
  4. drop the BMF schema or database objects
  5. set pre-scripts to rebuild the objects (takes on a minute or two)
  6. BMF runs as normal

I’ll post my scripts to the site in a moment :slight_smile:

Bert

Here is my Oracle script to initially create the duplicate TPC-C objects:

filename - tpcc_init.sql

connect quest/quest@&1

drop table c_customer cascade constraints purge;
drop table c_district cascade constraints purge;
drop table c_history cascade constraints purge;
drop table c_item cascade constraints purge;
drop table c_new_order cascade constraints purge;
drop table c_order cascade constraints purge;
drop table c_order_line cascade constraints purge;
drop table c_stock cascade constraints purge;
drop table c_warehouse cascade constraints purge;

create table c_customer as select * from bmf.c_customer;
create table c_district as select * from bmf.c_district;
create table c_history as select * from bmf.c_history;
create table c_item as select * from bmf.c_item;
create table c_new_order as select * from bmf.c_new_order;
create table c_order as select * from bmf.c_order;
create table c_order_line as select * from bmf.c_order_line;
create table c_stock as select * from bmf.c_stock;
create table c_warehouse as select * from bmf.c_warehouse;

grant select on c_customer to bmf;
grant select on c_district to bmf;
grant select on c_history to bmf;
grant select on c_item to bmf;
grant select on c_new_order to bmf;
grant select on c_order to bmf;
grant select on c_order_line to bmf;
grant select on c_stock to bmf;
grant select on c_warehouse to bmf;

begin
DBMS_STATS.GATHER_SCHEMA_STATS
(
OwnName => ‘QUEST’
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Block_sample => TRUE
,Method_Opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO ’
,Degree => 4
,Cascade => TRUE
,No_Invalidate => TRUE
);
end;
/

exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;

Here is my Oracle script to reset/restore a fresh usable copy TPC-C objects:

filename - tpcc_reset.sql

connect bmf/bmf@&1

drop table c_customer cascade constraints purge;
drop table c_district cascade constraints purge;
drop table c_history cascade constraints purge;
drop table c_item cascade constraints purge;
drop table c_new_order cascade constraints purge;
drop table c_order cascade constraints purge;
drop table c_order_line cascade constraints purge;
drop table c_stock cascade constraints purge;
drop table c_warehouse cascade constraints purge;

create table c_customer as select * from quest.c_customer;
create table c_district as select * from quest.c_district;
create table c_history as select * from quest.c_history;
create table c_item as select * from quest.c_item;
create table c_new_order as select * from quest.c_new_order;
create table c_order as select * from quest.c_order;
create table c_order_line as select * from quest.c_order_line;
create table c_stock as select * from quest.c_stock;
create table c_warehouse as select * from quest.c_warehouse;

CREATE UNIQUE INDEX C_CUSTOMER_I1 ON BMF.C_CUSTOMER
(C_W_ID, C_D_ID, C_ID)
NOLOGGING
TABLESPACE USERS
PARALLEL (degree 4);

CREATE INDEX C_CUSTOMER_I2 ON BMF.C_CUSTOMER
(C_LAST, C_W_ID, C_D_ID, C_FIRST)
NOLOGGING
TABLESPACE USERS
PARALLEL (degree 4);

CREATE UNIQUE INDEX C_DISTRICT_I1 ON BMF.C_DISTRICT
(D_W_ID, D_ID)
NOLOGGING
TABLESPACE USERS
PARALLEL (degree 4);

CREATE UNIQUE INDEX C_ITEM_I1 ON BMF.C_ITEM
(I_ID)
NOLOGGING
TABLESPACE USERS
PARALLEL (degree 4);

CREATE UNIQUE INDEX C_NEW_ORDER_I1 ON BMF.C_NEW_ORDER
(NO_W_ID, NO_D_ID, NO_O_ID)
NOLOGGING
TABLESPACE USERS
PARALLEL (degree 4);

CREATE UNIQUE INDEX C_ORDER_I1 ON BMF.C_ORDER
(O_ID, O_W_ID, O_D_ID)
NOLOGGING
TABLESPACE USERS
PARALLEL (degree 4);

CREATE UNIQUE INDEX C_ORDER_LINE_I1 ON BMF.C_ORDER_LINE
(OL_O_ID, OL_W_ID, OL_D_ID, OL_NUMBER)
NOLOGGING
TABLESPACE USERS
PARALLEL (degree 4);

CREATE UNIQUE INDEX C_STOCK_I1 ON BMF.C_STOCK
(S_I_ID, S_W_ID)
NOLOGGING
TABLESPACE USERS
PARALLEL (degree 4);

CREATE UNIQUE INDEX C_WAREHOUSE_I1 ON BMF.C_WAREHOUSE
(W_ID)
NOLOGGING
TABLESPACE USERS
PARALLEL (degree 4);

/* Formatted on 2007/02/08 14:25 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE c_sp_delivery (
ware_id NUMBER,
carrier_id NUMBER,
order_1 IN OUT NUMBER,
order_2 IN OUT NUMBER,
order_3 IN OUT NUMBER,
order_4 IN OUT NUMBER,
order_5 IN OUT NUMBER,
order_6 IN OUT NUMBER,
order_7 IN OUT NUMBER,
order_8 IN OUT NUMBER,
order_9 IN OUT NUMBER,
order_10 IN OUT NUMBER,
retry IN OUT NUMBER,
cur_date IN DATE
)
AS
TYPE intarray IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;

order_id intarray;
dist_id INTEGER;
cust_id INTEGER;
amount_sum NUMBER;
no_rowid ROWID;
not_serializable EXCEPTION;
PRAGMA EXCEPTION_INIT (not_serializable, -8177);
deadlock EXCEPTION;
PRAGMA EXCEPTION_INIT (deadlock, -60);
snapshot_too_old EXCEPTION;
PRAGMA EXCEPTION_INIT (snapshot_too_old, -1555);

CURSOR o_cur
IS
SELECT no_o_id, ROWID
FROM c_new_order
WHERE no_w_id = ware_id AND no_d_id = dist_id
ORDER BY no_w_id, no_d_id, no_o_id;
BEGIN
FOR i IN 1 … 10
LOOP
dist_id := i;

  LOOP
     BEGIN
        OPEN o_cur;

        FETCH o_cur
         INTO order_id (i), no_rowid;

        IF (o_cur%NOTFOUND)
        THEN
           CLOSE o_cur;

           COMMIT;
           order_id (i) := 0;
           EXIT;
        END IF;

        CLOSE o_cur;

        DELETE FROM c_new_order
              WHERE ROWID = no_rowid;

        UPDATE c_order
           SET o_carrier_id = carrier_id
         WHERE o_d_id = dist_id AND o_w_id = ware_id
               AND o_id = order_id (i);

        SELECT o_c_id
          INTO cust_id
          FROM c_order
         WHERE o_d_id = dist_id AND o_w_id = ware_id
               AND o_id = order_id (i);

        UPDATE c_order_line
           SET ol_delivery_d = cur_date
         WHERE ol_d_id = dist_id
           AND ol_w_id = ware_id
           AND ol_o_id = order_id (i);

        SELECT SUM (ol_amount)
          INTO amount_sum
          FROM c_order_line
         WHERE ol_d_id = dist_id
           AND ol_w_id = ware_id
           AND ol_o_id = order_id (i);

        UPDATE c_customer
           SET c_balance = c_balance + amount_sum,
               c_delivery_cnt = c_delivery_cnt + 1
         WHERE c_id = cust_id AND c_d_id = dist_id AND c_w_id = ware_id;

        COMMIT;
        EXIT;
     EXCEPTION
        WHEN not_serializable OR deadlock OR snapshot_too_old
        THEN
           ROLLBACK;
           retry := retry + 1;
     END;
  END LOOP;

END LOOP;

order_1 := order_id (1);
order_2 := order_id (2);
order_3 := order_id (3);
order_4 := order_id (4);
order_5 := order_id (5);
order_6 := order_id (6);
order_7 := order_id (7);
order_8 := order_id (8);
order_9 := order_id (9);
order_10 := order_id (10);
END;
/

CREATE OR REPLACE PROCEDURE c_sp_new_order (
ware_id NUMBER,
dist_id NUMBER,
cust_id NUMBER,
ord_ol_cnt NUMBER,
ord_all_local NUMBER,
cust_discount OUT NUMBER,
cust_last OUT VARCHAR2,
cust_credit OUT VARCHAR2,
dist_tax OUT NUMBER,
ware_tax OUT NUMBER,
ord_id IN OUT NUMBER,
retry IN OUT NUMBER,
cur_date IN DATE
)
AS
dist_rowid ROWID;
not_serializable EXCEPTION;
PRAGMA EXCEPTION_INIT (not_serializable, -8177);
deadlock EXCEPTION;
PRAGMA EXCEPTION_INIT (deadlock, -60);
snapshot_too_old EXCEPTION;
PRAGMA EXCEPTION_INIT (snapshot_too_old, -1555);
integrity_viol EXCEPTION;
PRAGMA EXCEPTION_INIT (integrity_viol, -1);
BEGIN
LOOP
BEGIN
SELECT c_district.ROWID, d_tax, d_next_o_id, w_tax
INTO dist_rowid, dist_tax, ord_id, ware_tax
FROM c_district, c_warehouse
WHERE d_id = dist_id AND d_w_id = w_id AND w_id = ware_id;

     UPDATE c_district
        SET d_next_o_id = ord_id + 1
      WHERE ROWID = dist_rowid;

     SELECT c_discount, c_last, c_credit
       INTO cust_discount, cust_last, cust_credit
       FROM c_customer
      WHERE c_id = cust_id AND c_d_id = dist_id AND c_w_id = ware_id;

     INSERT INTO c_new_order
          VALUES (ord_id, dist_id, ware_id);

     INSERT INTO c_order
          VALUES (ord_id, dist_id, ware_id, cust_id, cur_date, 11,
                  ord_ol_cnt, ord_all_local);

     EXIT;
  EXCEPTION
     WHEN not_serializable OR deadlock OR snapshot_too_old OR integrity_viol
     THEN
        ROLLBACK;
        retry := retry + 1;
  END;

END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE c_sp_order_status_id (
ware_id NUMBER,
dist_id NUMBER,
cust_id IN OUT NUMBER,
bylastname NUMBER,
cust_last IN OUT VARCHAR2,
cust_first OUT VARCHAR2,
cust_middle OUT VARCHAR2,
cust_balance OUT NUMBER,
ord_id IN OUT NUMBER,
ord_entry_d OUT DATE,
ord_carrier_id OUT NUMBER,
ord_ol_cnt OUT NUMBER
)
IS
TYPE rowidarray IS TABLE OF ROWID
INDEX BY BINARY_INTEGER;

cust_rowid ROWID;
ol BINARY_INTEGER;
c_num BINARY_INTEGER;
row_id rowidarray;
not_serializable EXCEPTION;
PRAGMA EXCEPTION_INIT (not_serializable, -8177);
deadlock EXCEPTION;
PRAGMA EXCEPTION_INIT (deadlock, -60);
snapshot_too_old EXCEPTION;
PRAGMA EXCEPTION_INIT (snapshot_too_old, -1555);

CURSOR mo_cur
IS
SELECT o_id, o_entry_d, o_carrier_id, o_ol_cnt
FROM c_order
WHERE o_d_id = dist_id AND o_w_id = ware_id AND o_c_id = cust_id
ORDER BY o_w_id, o_d_id, o_c_id, o_id DESC;
BEGIN
LOOP
BEGIN
SELECT c_balance, c_first, c_middle, c_last
INTO cust_balance, cust_first, cust_middle, cust_last
FROM c_customer
WHERE c_id = cust_id AND c_d_id = dist_id AND c_w_id = ware_id;

     OPEN mo_cur;

     FETCH mo_cur
      INTO ord_id, ord_entry_d, ord_carrier_id, ord_ol_cnt;

     CLOSE mo_cur;

     EXIT;
  EXCEPTION
     WHEN not_serializable OR deadlock OR snapshot_too_old
     THEN
        ROLLBACK;
  END;

END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE c_sp_order_status_name (
ware_id NUMBER,
dist_id NUMBER,
cust_id IN OUT NUMBER,
bylastname NUMBER,
cust_last IN OUT VARCHAR2,
cust_first OUT VARCHAR2,
cust_middle OUT VARCHAR2,
cust_balance OUT NUMBER,
ord_id IN OUT NUMBER,
ord_entry_d OUT DATE,
ord_carrier_id OUT NUMBER,
ord_ol_cnt OUT NUMBER
)
IS
TYPE rowidarray IS TABLE OF ROWID
INDEX BY BINARY_INTEGER;

cust_rowid ROWID;
ol BINARY_INTEGER;
c_num BINARY_INTEGER;
row_id rowidarray;
not_serializable EXCEPTION;
PRAGMA EXCEPTION_INIT (not_serializable, -8177);
deadlock EXCEPTION;
PRAGMA EXCEPTION_INIT (deadlock, -60);
snapshot_too_old EXCEPTION;
PRAGMA EXCEPTION_INIT (snapshot_too_old, -1555);

CURSOR c_cur
IS
SELECT ROWID
FROM c_customer
WHERE c_d_id = dist_id AND c_w_id = ware_id AND c_last = cust_last
ORDER BY c_w_id, c_d_id, c_last, c_first;

CURSOR mo_cur
IS
SELECT o_id, o_entry_d, o_carrier_id, o_ol_cnt
FROM c_order
WHERE o_d_id = dist_id AND o_w_id = ware_id AND o_c_id = cust_id
ORDER BY o_w_id, o_d_id, o_c_id, o_id DESC;
BEGIN
LOOP
BEGIN
c_num := 0;

     FOR c_id_rec IN c_cur
     LOOP
        c_num := c_num + 1;
        row_id (c_num) := c_id_rec.ROWID;
     END LOOP;

     cust_rowid := row_id ((c_num + 1) / 2);

     SELECT c_id, c_balance, c_first, c_middle, c_last
       INTO cust_id, cust_balance, cust_first, cust_middle, cust_last
       FROM c_customer
      WHERE ROWID = cust_rowid;

     OPEN mo_cur;

     FETCH mo_cur
      INTO ord_id, ord_entry_d, ord_carrier_id, ord_ol_cnt;

     CLOSE mo_cur;

     EXIT;
  EXCEPTION
     WHEN not_serializable OR deadlock OR snapshot_too_old
     THEN
        ROLLBACK;
  END;

END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE c_sp_payment_id (
ware_id NUMBER,
dist_id NUMBER,
cust_w_id NUMBER,
cust_d_id NUMBER,
cust_id IN OUT NUMBER,
bylastname NUMBER,
hist_amount NUMBER,
cust_last IN OUT VARCHAR2,
ware_street_1 OUT VARCHAR2,
ware_street_2 OUT VARCHAR2,
ware_city OUT VARCHAR2,
ware_state OUT VARCHAR2,
ware_zip OUT VARCHAR2,
dist_street_1 OUT VARCHAR2,
dist_street_2 OUT VARCHAR2,
dist_city OUT VARCHAR2,
dist_state OUT VARCHAR2,
dist_zip OUT VARCHAR2,
cust_first OUT VARCHAR2,
cust_middle OUT VARCHAR2,
cust_street_1 OUT VARCHAR2,
cust_street_2 OUT VARCHAR2,
cust_city OUT VARCHAR2,
cust_state OUT VARCHAR2,
cust_zip OUT VARCHAR2,
cust_phone OUT VARCHAR2,
cust_since OUT DATE,
cust_credit IN OUT VARCHAR2,
cust_credit_lim OUT NUMBER,
cust_discount OUT NUMBER,
cust_balance IN OUT NUMBER,
cust_data OUT VARCHAR2,
retry IN OUT NUMBER,
cur_date IN DATE
)
AS
TYPE rowidarray IS TABLE OF ROWID
INDEX BY BINARY_INTEGER;

cust_rowid ROWID;
ware_rowid ROWID;
dist_ytd NUMBER;
dist_name VARCHAR2 (11);
ware_ytd NUMBER;
ware_name VARCHAR2 (11);
c_num BINARY_INTEGER;
row_id rowidarray;
cust_payments PLS_INTEGER;
cust_ytd NUMBER;
cust_data_temp VARCHAR2 (500);
not_serializable EXCEPTION;
PRAGMA EXCEPTION_INIT (not_serializable, -8177);
deadlock EXCEPTION;
PRAGMA EXCEPTION_INIT (deadlock, -60);
snapshot_too_old EXCEPTION;
PRAGMA EXCEPTION_INIT (snapshot_too_old, -1555);
BEGIN
LOOP
BEGIN
SELECT ROWID, c_first, c_middle, c_last,
c_street_1, c_street_2, c_city, c_state,
c_zip, c_phone, c_since, c_credit,
c_credit_lim, c_discount, c_balance - hist_amount,
c_payment_cnt, c_ytd_payment + hist_amount,
DECODE (c_credit, ‘BC’, c_data, ’ ')
INTO cust_rowid, cust_first, cust_middle, cust_last,
cust_street_1, cust_street_2, cust_city, cust_state,
cust_zip, cust_phone, cust_since, cust_credit,
cust_credit_lim, cust_discount, cust_balance,
cust_payments, cust_ytd,
cust_data_temp
FROM c_customer
WHERE c_id = cust_id AND c_d_id = cust_d_id AND c_w_id = cust_w_id;

     cust_payments := cust_payments + 1;

     IF cust_credit = 'BC'

Hi Bert,

I think the c_sp_stock_level should have the following select statement, please confirm:

SELECT COUNT (DISTINCT s_i_id)

INTO low_stock

FROM c_order_line, c_stock, c_district

WHERE d_id = dist_id

AND d_w_id = ware_id

AND d_id = ol_d_id

AND d_w_id = ol_w_id

AND ol_i_id = s_i_id

AND ol_w_id = s_w_id

AND s_quantity < threshold

AND ol_o_id BETWEEN (d_next_o_id - 20) AND (d_next_o_id - 1);

Without the s_quantity < threshold I kept getting an error when trying to compile the stored procedure and when I ran show error procedure c_sp_stock_level; I got a ORA-00920 Invalid relational operator error.

This also seems to be confirmed by other implementations of TPC-C that I can find through the Full Disclosure Reports.

You’ve emailed me directly so I’ll work with you there - just closing this thread so not to leave anything dangling :slight_smile:

And yes by the way - looks like a cut and paste error where the toad world blog posting interface chewed up and ate some of my text. You should have just created your own script by reverse engineering via toad (ot whatever) rather than counting on running my stuff per se. I presented my script as an example. Next time I’ll say example - not for consumption.

Very nice Bert, thank you