datapump imports getting slower and slower
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: datapump imports getting slower and slower

  1. #1
    Join Date
    Jul 2003
    Posts
    135

    datapump imports getting slower and slower

    Oracle 10g
    Windows 2003 server

    Lately we are seeing lot of slowness in our schema imports while using datapump imports. Eveything looks fine until Import process reaches its last statges (processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS) it waits long time (5-7 minutes) before moving to next step and finishing the import. see attached file.

    The import process is getting slower and slower as database is aging. DB is about 1 year old now. We do lot of expdp and impdp on daily basis on this db and impdp is done into the new schema (new user).

    Please suggest any improvement steps and reasoning.
    Thanks

    -D
    Attached Images Attached Images

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    have you traced the database session to see where the time is being taken

  3. #3
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Are you adding new objects to this schema on a regular basis? From the picture it looks as though you are creating a lot of permanent "temporary" tables... however, that is complete specualtion as we don't have all the information required... Anyway, if that is the case then of course this step with take longer as there are more object statistics to export\import.

    You could try the export/import without stats and create them afterwards in the remapped schema, or perhaps look at the objects you have and decide what is required and what isn't.

    I'll stop speculating now.
    Assistance is Futile...

  4. #4
    Join Date
    Feb 2003
    Posts
    63
    As your database grows then the export will grow with that which in turn will slow the import down, what parameters are you using for the import etc

  5. #5
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    I suspect you're hitting a similar bug to on I hit. Take a look a bug 5071931 on MetaLink. Simple workaround is to exclude statistics, or use dbms_stats to copy them.

  6. #6
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Hacket, i heard someone posted here that copying stats will result
    into different search path/plan

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Exclude the stats and regenerate them? No? (As Waitecj suggests above - seems to be the obvious answer)

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