Re: how to improve impdp

• I have a lot of tables with stats in both DW and of course PS.
• When using datapump expimp to clone schemas across dbs, I found it's actually faster to not expimp statistics for each table.
• One way around the need to then recalc stats is to save the stats in a special table - see the DBMS_STATS doco on how to do this - then clone that table with a simple CTAS across a dblink and then use DBMS_STATS package aqgain to reset stats from it in the target db. Overall this is heaps faster than a stats recalc and significantly faster than letting datapump manipulate stats on a table-by-table base.

Usual disclaimers apply and I am running 11.2.0.3.

-- 
Cheers
Nuno Souto
dbvision_at_iinet.net.au




On 11/07/2013 12:47 AM, Michael McMullen wrote:


>

>

> I do a lot of peoplesoft refreshes and the slowness is usually in the metadata, dropping the user takes a few hours as there is over 25k tables and so does building stats. Working with the ps team has allowed me to export just the tables/views and exclude statistics.

> On imdp, I drop the views first and then use table_exists_action=replace as ps tables don't have foreign keys. I then let oracle just build up the stats with its nightly job.

> Process went from 7 hrs to 2.5 and the users are happy with the data.

>


--
http://www.freelists.org/webpage/oracle-l