Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: slow migration path
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