Import too slow
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Import too slow

  1. #1
    Join Date
    Oct 2001
    Location
    Kuwait
    Posts
    23

    Exclamation Import too slow

    Hi all ,

    I am trying to import a database in orcle 8. My export file
    size is 2.4 GB. My DB block size is 8192 . It's taking around
    12 hours . There are some big indexes on a massaive table.
    Still.... is there are ways to improve on this time ?
    Is this 12 hours to import is something normal ?

    Regards
    Oracle DBA
    National Bank Of Kuwait

  2. #2
    Join Date
    Nov 2002
    Posts
    39
    Definitely, 12 Hrs is not a normal, for a 2.4GB export file. What is the OS? Check if there is any other process going on in your server? Check your CPU utilization also.
    Sathish

  3. #3
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Have you tried to import without indexes first, and after do an import with indexfile ... After that modify the index creation script with nologging, parallel and with sort_area_size high?

    Cheers

  4. #4
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    If you are recalculating your statistics, it will add a lot of time.
    David Knight
    OCP DBA 8i, 9i, 10g

  5. #5
    Join Date
    Oct 2001
    Location
    Kuwait
    Posts
    23
    Hi all ,

    My Operating system is windows NT. How will
    stop calculating statistics. ( or How will I know
    it's calculating statistics )

    Regards
    Oracle DBA
    National Bank Of Kuwait

  6. #6
    Join Date
    Dec 2002
    Location
    Chennai, India
    Posts
    104

    HElo

    Pls increase ur BUFFER(one of the Import Parameter) value other than default one and try.

    Regards,
    Sudheer
    Project Lead,India

  7. #7
    Join Date
    Jul 2000
    Posts
    119
    Most importantly, have you checked the alert log if at all there are any error whil import was in progress, specifically related with temporary tablespace?
    as you said indexes are big, they will need a huge sort area in temporary tablespace for the same.
    Are you using temporary tablespace as LMT ?
    OCP 8.0, 8i, 9i

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131
    Can you attach a copy of the parfile you used.
    Did you set BUFFER= and COMMIT= in the parfile?

    MH
    I remember when this place was cool.

  9. #9
    Join Date
    Oct 2001
    Location
    Kuwait
    Posts
    23
    I was mot specifying the buffer size , so
    the system was using the default.

    Can someone recommand a better buffer size

    How does COMMIT parameter going to affect the
    performance.

    Also can someone suggest a beter SORT_AREA_SIZE.
    Presently the value for sort area & retian size
    is 264000.
    Oracle DBA
    National Bank Of Kuwait

  10. #10
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    132
    try BUFFER=20000000

    and to build the index separately issue the below commands - if you have enough memory you can give more than 20Megs.
    alter session set sort_area_size = 20000000 ;
    alter session set sort_area_retain_size = 20000000 ;

    In addition for large index :
    Use the noLogging feature so the logging does not take place.
    Use parallel degree creation to create the Index in parallel.
    Make sure you give a Lot of Space on the Temp Tablespace.
    Put the Temp Tablespace on a Separate Controller so that there is no contention.
    Also if possible make sure this is the only session which is accesing the temp tablespace.
    Giani

Posting Permissions

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



Click Here to Expand Forum to Full Width