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: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Wed, 31 Oct 2007 20:54:59 +0100
Message-ID: <fgamif$2nq$1@news1.zwoll1.ov.home.nl>


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!)

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
Received on Wed Oct 31 2007 - 14:54:59 CDT

Original text of this message

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