Nice little best practice for statistics and Data Pump when doing either Transportable Tablespaces or Full Transportable Export-Import (credits to Roy and Dean Gagne).
Transport Statistics via a Staging Table
First of all we always recommend to exclude statistics when doing a Data Pump export as the import of such stats takes way longer than transporting them via a stats table. If you are unfamiliar with transporting stats between databases please see the Oracle Performance Tuning Guide with a nice tutorial:
The basic steps to transport statistics from one database to another fast and efficient consist of:
- Create a staging table in your source database with
DBMS_STATS.CREATE_STAT_TABLE - Export your local stats into this staging table by using DBMS_STATS.EXPORT_SCHEMA_STATS
- Export the staging table and import it into your destination database with Data Pump
- Import the statistics held in the staging table by using DBMS_STATS.IMPORT_SCHEMA_STATS
For the regular Data Pump exports we always recommend to set:
EXCLUDE=STATISTICS
to avoid performance penalties during impdp.
But this does not affect Transportable Tablespaces and Full Transportable Export/Import.
How to exclude Statistics for TTS and FTEX?
*** ATTENTION: THIS IS NOT NECESSARY ANYMORE AT LEAST SINCE ORACLE 19c ***
For reasons I don’t know the metadata heterogeneous object for “transportable” is different than all of the others Therefore in order to exclude statistics for Transportable Tablespaces and Full Transportable Export/Import you must set:
EXCLUDE=TABLE_STATISTICS,INDEX_STATISTICS
instead.
See also MOS Note:1517267.1 – TTS Using Datapump Expdp and Impdp Exports Statistics Despite Setting EXCLUDE=STATISTICS
— Thanks Pete!
Keep this in mind during your next migration 🙂
Other Useful Articles About Data Pump
- 30-June-2015:
Some Data Pump issues: + DBMS_DATAPUMP Import via NETWORK_LINK fails + STATUS parameter giving bad performance - 3-July-2012:
Consistent Export - 10-April-2015:
Parallel Index Creation with Data Pump - 19-July-2011:
How to get the Master Table from Data Pump? - 5-December-2011
Exclude Scheduler Jobs from Export? - 18-July-2013
Full Transportable Export/Import White Paper
–Mike
Hello, Mike,
Interesting post, thank you !
1.
The bad thing when we exclude statistics is that we cannot use INCLUDE clause.
So, when we use INCLUDE clause we cannot use EXCLUDE…
Can Oracle allow us use INCLUDE with EXCLUDE=%STATISTICS ?
2.
Statistics is divided into 2 classes: data and rules.
Data – is number of blocks and rows.
Rules – means lock table statistics or collect extended statistics and so on. So the question is: how to export-import statistic’s RULES, but not statistic’s DATA ?
Some years ago i opened SR as enhancement request, but there is no improvements of this kind.
Thank you !
Thank you !
Thanks for sharing. I like it very much
I was searching for this information about Data Pump, thanks Mike.
i’m facing errors while executing.
Great post!!
Yury,
the first thing may be availabe soon (not in the next base release but maybe a bit later).
For the 2nd question please check the Data Pump FILTER slides in the big slide deck to the right and see if this suits you to separate both stats types.
If not, then you may raise and SR and ask to file an Enhancement Request.
Cheers
Mike
Nice timing on this article – was trying to figure it out this morning when I came across the post. Also documented in MOS Note 1517267.1
Thanks a lot, Pete,
wasn’t aware of the MOS note – updated the post with it 🙂
Cheers
Mike
Regarding errors – please see the MOS note as well – and if you still see errors please log an SR as I don’t get errors in my env.
Cheers
Mike
Awesome guys! Will check it out!
Hi MIke,
Does this apply to Full TTS? I checked the MOS note and it says it applies to 10g – 11204 only.
TTS Using Datapump Expdp and Impdp Exports Statistics Despite Setting EXCLUDE=STATISTICS (Doc ID 1517267.1)
Thanks!
Does this apply to 12c Full TTS?
The MOS note says it applied to 10g – 11204 only.
TTS Using Datapump Expdp and Impdp Exports Statistics Despite Setting EXCLUDE=STATISTICS (Doc ID 1517267.1)
Ging,
this applies to Full Transportable Export/Import in any version. The MOS Note may not be correct.
Cheers
Mike
Thanks for this post Mike. Ive just done a TTS export on 11.1.0.7 and it failed to export at the table and index stats/preferences part. Kept on getting a internal error. Adding this paramter meant i could export the metadata. Will be sharing this best practice. Thanks.
Nice