Sunday, July 5, 2015

Datapump excluding statistics ; makes faster import !!!



Exclude statistics in data pump

Normally, when I tried to play with Data Pump, the expdp utility will always take less time when compared to impdp. The impdp takes more time in importing the collected statistics into the new schema.
So, yesterday I tried to use the exclude parameter in impdp/expdp to utilize the power of the data pump. Actually what I did was I didn't collect the statistics during expdp,
expdp exclude=statistics...
After this, the entire impdp task was very fast. The same exclude statistics can also be used in the impdp utility as given below,
impdp exclude=statistics...

But you should not use the exclude=statistics parameter if you have already done this during the expdp. It will throw an error "ORA-39168: Object path STATISTICS was not found."The Data Pump is a good utility.




>>  Using the EXCLUDE=STATISTICS parameter value of impdp prevents the automatic of gathering of object statistics by impdp.  Setting this parameter value will greatly improve impdp performance.

Version: 10.2.0.4
Assume:
1.)      expdp included table/index statistics
2.)      the following parameter on the target db is set to 
false:"_optimizer_compute_index_stats",

It is my understanding that impdp will _only_ import statistics, not do a 
regather statistics operation.

No comments:

Post a Comment