Re: Slow Import

In a valiant and sublime effort,Norman Dunbar frowned, dipped a thumb in soot and doodled:

>I would add to your command line, the following :
>
> buffer=1024000 commit=yes

Yeah, that will definitely help. I use 10M all the time.

>segment space. At present you are committing once, at the very end of
>the import.

Small correction: committing once at the end of each table.

>much quicker if you :
>- import a dummy run with indexfile=myindexes.sql (no data is imported,
>and a script to build your indexes is created)
>- import the data only with indexes=no
>- run the myindexes.sql agains the schema when the import completes
>To me, it seems 'silly' that three passes would be quicker than just
>one, however, until I try it properly, I'm not going to dismiss it.

If you have parallel processing installed, what you do is edit the myindexes.sql to stick a parallel degree (n) to greatly speed up index creation.

Also, recall that exp and imp progress in alphabetical order of table names. If you have tables with RI enabled and they are not related in alphabetical order, you can get into some quirky problems with what imp does. Particularly when triggers are involved. You may also run into problems with grants.

I like to bring in just the schema, no rows, no indexes, no grants. Check that allocations are what they should be. Then I disable/drop anything that may slow down the import of data. After that I import the rows. Then I run the indexes with parallel as well as a final pass to bring in the grants=y, with rows=n and indexes=n. This last one cleans up views screwed up by access rights.

This cycle is complex, but usually completes in a fraction of the time of a vanilla default import.
An example:
about 5000 tables and 7500 indexes, db is 30Gb exp takes just under 2 hours.
imp of same into emptied db (using this cycle) takes 4.5. imp with vanilla options takes over 10 hours.

>
>Check out your sort parameters in initSID.ora - they might be too small
>and the sorting required for building indexes is taking place on disc.

Yes, that usually helps.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Fri Nov 02 2001 - 10:01:54 CST