datapump imports getting slower and slower

[daljitsb]

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

[davey23uk]

have you traced the database session to see where the time is being taken

[waitecj]

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.

[craigdba]

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

[hacketta1]

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.

[yxez]

Hacket, i heard someone posted here that copying stats will result
into different search path/plan

[JMac]

Exclude the stats and regenerate them? No? (As Waitecj suggests above - seems to be the obvious answer)