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
