Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: slow migration path

Re: slow migration path

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 31 Oct 2007 14:19:28 -0700
Message-ID: <1193865566.445516@bubbleator.drizzle.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US