Data Pump – Exclude Stats differently for TTS and FTEX

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:

  1. Create a staging table in your source database with DBMS_STATS.CREATE_STAT_TABLE
  2. Export your local stats into this staging table by using DBMS_STATS.EXPORT_SCHEMA_STATS
  3. Export the staging table and import it into your destination database with Data Pump
  4. 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?

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

13 thoughts on “Data Pump – Exclude Stats differently for TTS and FTEX

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

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

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

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *