Re: slow migration path

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@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: slow migration path
    ... and on the version of oracle: ... Increase sga, to match processes, db_buffer_size, sga target ... Does not look like it is the culprit; filling up the parent ... Same database creation scripts and init.ora files were used ...
    (comp.databases.oracle.server)
  • Re: Ok, call me silly...
    ... And so are there number of rdbms products which can beat Oracle ... out it will also use substantially fewer resources than Informix too. ... SQL> CREATE TABLE parent ( ... SQL> CREATE TABLE child AS ...
    (comp.databases.informix)
  • Re: ORA-04031 with Collections and SGA Settings
    ... I am working with a data warehouse using an Oracle 10g R1 database. ... SGA MAX SIZE: 5904 MB ... The database server is also configured as a shared server. ... Do the settings for the SGA raise any questions/comments/concerns? ...
    (comp.databases.oracle.server)
  • RE: DBD::Oracle with oracle child process with parent ID of 1
    ... there when I login to Oracle with SQL*PLUS, only when I run PERL DBI ... connecting to Oracle shows the child process like an orphan process. ... Subject: DBD::Oracle with oracle child process with parent ID of 1 ... The difference between your systems is probably that you are connecting ...
    (perl.dbi.users)
  • Re: Ok, call me silly...
    ... out it will also use substantially fewer resources than Informix too. ... I in fact see SQLite matching Oracle and DB2 in load speed. ... SQL> CREATE TABLE parent ( ... SQL> CREATE TABLE child AS ...
    (comp.databases.informix)