- check out http://www.oracledba.co.uk/tips/import_speed.htm
- does the one big table contains a LONG ? if so increasing your import
buffer won't change a thing - if commit=y import
will commit this table after EACH record - so you're looking at 8.5 million
commits and a lot of waits ...
I would create one cowabunga rollback segment in a locally managed uniform
sized tablespace and offline all the rest of RBS - set COMMIT=N ... so one
commit after each table
- set analyze=n will save you quite a bit too - analyse your data
afterwards
- 1.5Gb is a small DB - I hope you didn't throw it all on one disk or one
RAID5 volume now did you ???
"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message
news:E2F6A70FE45242488C865C3BC1245DA72D94D0_at_lnewton.leeds.lfs.co.uk...
> Cameron,
>
> I would add to your command line, the following :
>
> buffer=1024000 commit=yes
>
> If you have the memory, make the buffer bigger - I have seen 8M
> recommended just this week on this NG.
> That way, you won't hit a possible problem of running oput of rollback
> segment space. At present you are committing once, at the very end of
> the import. This will be quicker than every 'buffersize', but needs a
> lot of RBS.
>
> Other tints and hips :
>
> I presume you are importing into a blank user (lack of ignore=yes on
> command line) so dropping indexes woun't help here. If the users tables
> were simply truncated, lose the indexes too, so you don't update them
> with the table data during the import. I have read that imports go much
> 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.
>
>
> The constraints on your tables will be built as part of the import. I
> suspect putting a primary key on your big table will take time.
> Are there a lot of stored procedures, packaged etc - they get imported
> as well.
>
> 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.
>
> Do you have the export file on the same discs/controller as the database
> datafiles/temp files ?
> If so, not a good idea.
>
> Did the export specify compress=y, again, not a good idea if you have
> carefully set up the tablespaces to have equal initial and next
> extents. However, if the exported database has something like an initial
> = next = 40Kb, then there will be a lot of dynamic extension going on as
> each extend is required and allocated.
>
> I'm sure someone else will have other suggestions, but I hope this lot
> helps.
>
>
> Oh, exports always ruin much quicker than imports :o)
>
>
> Regards,
> Norman.
>
> ------------------------------------------------------------------------
> -----
> Norman Dunbar EMail: Norman.Dunbar_at_LFS.co.uk
> Database/Unix administrator Phone: 0113 289 6265
> Fax: 0113 289 3146
> Lynx Financial Systems Ltd. URL: http://www.Lynx-FS.com
> ------------------------------------------------------------------------
> -----
>
>
>
>
> -----Original Message-----
> From: Cameron Abbott [mailto:cvabbott_at_altavista.com]
> Posted At: Friday, November 02, 2001 2:07 PM
> Posted To: server
> Conversation: Slow Import
> Subject: Slow Import
>
>
> Hi All,
>
> Is there any way to spped up a database import? Here is the situation.
>
> I did an export of a Oracle 7.3.4 database, it took 49 minutes using the
> direct option. The database contained 274 tables and the export file is
> 1.5
> gigabytes. I am trying to import this data into a new database, Oracle
> 8.1.5. After 18 hours of importing it has only imported 116 tables.
> The
> 116th table has 8.5 million records which seems to be taking alot of
> time to
> import.
>
> Is there an option like direct that I can use to speed up the import?
>
> My import command is
>
> imp user/password fromuser=user_id touser=user_id file=exp.dmp
>
>
> Thanks
>
> Cameron
>
>
Received on Fri Nov 02 2001 - 10:03:23 CST
