Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am using an oracle database and want to generate test data from a live database, but I do not want to copy the whole database content.

Given a database DB(S, D) consisting of the database schema S (everything described with DDL (tables, columns, constraints,...)) and data D (the actual data in the db, everything described with DML), a query Q and a result set R that is the result of the query when run on the DB. In pseudocode:

R = runQuery(Q, DB(S, D))

Is there a tool which can generate data D2 such that I get the same result set R with query Q?

D2 = generateTestData(S, Q, R)
R2 = runQuery(Q, DB(S, D2))

assert(R == R2)

Is this possible in general? Can toad generate these insert statements?

share|improve this question
    
A very general and complicated problem. But I think I have an idea for something else: generateTestData(Q, DB(S, D)). Would it be of any use in your situation? –  kubanczyk Dec 2 '11 at 19:24
    
Yes, this would also be interesting. Could you please describe in more detail. (Sorry for the late reply.) –  Ben Dec 12 '11 at 11:57

1 Answer 1

I insert a million rows with a script that I have been using since 2000.

I have a calling script that creates a nice report for 10 runs and the average.

 6 of the top 10 fastest databases are running faster due to performance tuning.

INSTANCE SEC_TO_INSERT_250_MEG AVG_SEC_TO_INSERT_25_MEG SYSDATE
-------- --------------------- ------------------------ ---------
PWCMT                     3.71                      .37 21-AUG-14 Test Launch Pad DR with all the tuning parameters
DWADE                     4.23                      .42 14-OCT-14
PWCM1                     4.44                      .44 21-AUG-14 After System Changes Production Lauch Pad 4 times faster
TPLM                      4.81                      .48 21-AUG-14
PADMRT                    5.94                      .59 21-AUG-14 After System Changes refresh runs in 7 hours instead of 11
PWCZ1                     6.17                      .62 21-AUG-14 After System Changes Stage Launch Pad 3 times faster.
CPLM                      6.60                      .66 21-AUG-14
PRACPLM1                  8.66                      .87 21-AUG-14 Indexes built and purge of tables.
QADMRT                    8.93                      .89 21-AUG-14 After System Changes refresh runs in 6 hours instead of 10
DETL                     11.12                     1.11 21-AUG-14
DDCA                     11.81                     1.18 21-AUG-14
QDCA1                    12.28                     1.23 21-AUG-14

...

PIEDI                   110.99                    11.10 21-AUG-14
PSRT                    154.50                    15.45 21-AUG-14
PAUDIT                  187.64                    18.76 21-AUG-14
DHDR                    522.30                    52.23 21-AUG-14
QHDR                    772.22                    77.22 21-AUG-14
PHDR                    989.03                    98.90 21-AUG-14

Here is the script:

-- The Following script shows the time it takes to insert 1 million rows in a database.
-- I used it to determine that:
-- 
-- 1) Optimized Databases perform in relation to the hardware they run on,
--    and all the databases on 1 platform run within 10% of each other.
-- 2) When Rollback, Temp or Redo logs share the same underlying disks as 
--    the "alan" table, the insert takes up to twice as long.
-- 3) When the rollback segments, Temp segments or redo log buffers are > 20 meg as
--    opposed to less than 1 meg, the inserts run 2 to 5 times faster.
--    By making the log_buffer and Rollback extents sizes close to 32k, 
--    the inserts run up to 9 times slower.
set feedback off
set termout off
drop table alan;
set termout on
create table alan (name varchar2(30)) storage (initial 32k next 1m) logging noparallel storage(buffer_pool default); 
insert into alan values ('alan');
insert into alan values ('alan1');
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
insert into alan select * from alan;
set timing on
insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */
;
set timing off
set feedback on
set termout off
--drop table alan;
set termout on

And here is the calling script:

set feedback off
col sec_to_insert_250_meg for 9999.99
col avg_sec_to_insert_25_meg for 9999.99
col tot for 99999.9999
set termout off
drop table alan10;
create table alan10 (tot number(19,5));
insert into alan10 values (0.000);
commit;
-- select * from alan10;
set termout off
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
@rb
-- @v$sqlarea 'insert /*+ PPEND */ into alan logging select * from alan         /* This line shows the elapsed time. */'
set termout off
insert into alan10 (select elapsed_time/1000000 from v$sqlarea where length(sql_text)=105 and sql_text like'%into alan logging select * from alan%');
commit;
set termout on
-- select * from alan10;
col instance for a8
set termout on
select instance_name instance,sum(tot) sec_to_insert_250_meg, sum(tot)/10 avg_sec_to_insert_25_meg,sysdate from alan10,v$instance group by instance_name;
set termout off
drop table alan10;
set termout on
set feedback on
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.