Frank van Bortel wrote:
> DA Morgan wrote:
>
>> 200,000 rows in 0.62 sec = 3.1 sec/million = 9.3 sec/3 million
>
> Not always.
>
> It depends on how you insert the data (as you already demonstrated),
> and on the version of oracle:
> OS: MS XP Proffesional
> Single CPU, dual core (Intel E6600), 4GB memory installed
> (XP reports: 2.93GB installed)
> Database uses 1 disk (SATA/300)
>
> I took the liberty of running the test with 3,000,000 inserts.
>
> Oracle: 9.2.0.6.0 10.2.0.3.0 11.1.0.4.0
> ======= =========== =========== ===========
> Parent: 00:03:52.40 00:02:04.32 00:03:15.35
> Test: 00:00:17.43 00:00:05.90 00:00:09.26
>
> Please note - last test was 11 beta, and sga sizes between
> 9.2 and 10/11 differ! Does that make all the difference?
> Increase sga, to match processes, db_buffer_size, sga target
> of 10g and reran tests on 9.2.
>
> Does not look like it is the culprit; filling up the parent
> table still needs 00:03:52.54 (same as before), and executing
> test runs for 00:00:11.95, which is slightly faster
> (6 secs off the 17) than before, but still twice as slow as 10G.
>
> What does make a difference, is block size - it's 4 k for the 9.2,
> and 8k for the other 2.
> Same database creation scripts and init.ora files were used
> for 10G and 11G.
>
> Conclusion:
> - inserting one way (parent) or another (child) can
> make a huge difference (factor 20)!
> - on another OS, another version, things may be different
> (- newer is not better? Have to rerun with 11G production!)
Of course "not always." But then if your production server is
running the same 5400RPM drive that is in my laptop you have
more problems than just insert speed.
Everybody's mileage will vary. But the point is that inserting
3M rows need not be either slow or painful. Good decision making
about technique can greatly affect the outcome.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Oct 31 2007 - 16:19:28 CDT