Re: Slow Import

Nuno Souto wrote:
>
> 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

A "complete" set of recommendations (yeah right!) at

http://www.oracledba.co.uk/tips/import_speed.htm

(a little out of date now)

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."