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
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 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.