Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Shanghai , China
    Posts
    63

    Unanswered: fasten the import and export process

    Let's have a nice conclusion :-)

    How to fasten the import and export process (provided that CPU and Memory is enough ):

    following are my idea ,

    For Export :
    A. Change archivelog mode to nonarchivelog mode
    B. Set Buffer parameter to big enough (Buffer=10M)

    For Import :
    A. Change archivelog mode to nonarchivelog mode
    B. Set Buffer parameter to big enough (Buffer=10M)
    C. Set Commit parameter to 'Y'

    How do you think ?
    Oracle is an ocean . I am just a little fish

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    The bottleneck are the disks.
    Make sure your disks are fast enough. Maybe you could even use striping to speed up the processes.
    rws

  3. #3
    Join Date
    Aug 2002
    Posts
    2
    hi

    ok u want to improve the exp and imp performance if u want it do as follows

    for exp
    1.)give buffer size more
    2.)if u r doing exp with direct path (direct-y) increase recordlength parameter to more

    no need to turn off archoive log when u do export. the reason is it is taking backup but there will be no sql statements running behind to log info into redo log file

    for imp

    1.) increase sort_area_size and sort_area_retained_size
    2.)give indexes=n and give file name for indexfile=<'file'>
    3.)increase log_checkpoint_interval
    4.)assign large rollback segment
    5.)change archive log mode to noarchivelog mode

    so these are some of the tips to tune exp and imp

  4. #4
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    Re: fasten the import and export process

    Disabling archiving is -almost always- a stupid thing to do,
    because you have already loosed.
    Never trade out data recoverability for a performance target, if you are not willing to go into big troubles.

    Buffers are a usually a good idea (let's say 40M and more).
    A better idea is not to put the export file in the same disks as the database. Try these before all else.

    If you are importing commit=y is ok and a bigger redo log file group size can be a nice plus. Remember to have enough buffer cache blocks, and start writing dirty buffer at 1/2 of buffer cache size, before imp is stucked with free blocks starvation.

    If you do not have time to wait for export or import, you are doing the business the wrong way.

    good luck
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    The most you can gain is if:

    - you disable all fk constraints on the imported tables;

    - you disable the on insert triggers on the imported tables;

    - import w/o the indexes and build the indexes later. the total time of the two operations would be less if you do in one run.

    - also you can do tables= import and split your import on many parallel subpartitions of your tables= list. if you have many CPU's that means less time to import.

    Hope that helps,

    clio_usa
    OCP - DBA

    .
    .
    .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •