Dbms_datapump API exclude statistics during import

dbms-datapumporaclestatistics

We are using dbms_datapump to copy data between different servers or to setup new schemas quickly.

However importing statistics usually takes ages and has the additional disadvantage that schema statistics are locked after the import.

So I'm trying to find a way to skip importing the statistics.

According to Metalink Doc ID 1392981.1 this should be possible using:

dbms_datapump.metadata_filter(handle => l_job_handle, 
      name => 'EXCLUDE_PATH_LIST', 
      value => '''STATISTICS''');

However when I try that, I get an "ORA-39001: invalid argument value" error.

I also tried various other formats found in different places:

dbms_datapump.metadata_filter(handle => l_job_handle, 
      name => 'EXCLUDE_PATH_LIST', 
      value => 'STATISTICS');

dbms_datapump.metadata_filter(handle => l_job_handle, 
       name => 'EXCLUDE_PATH_EXPR', 
       value => 'like ''%/TABLE/STATISTICS/TABLE_STATISTICS''');

dbms_datapump.metadata_filter(handle => l_job_handle, 
       name => 'EXCLUDE_PATH_EXPR', 
       value => 'like ''%STATISTICS%''');

But all of them return with an ORA-39001.

The version I'm using is:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

Operating system is a Windows Server, but this also occurs on a Linux installation.

Any ideas how I can skip the statistics during import (or export) through the DBMS_DATAPUMP API?

Best Answer

You need to use:

DBMS_DATAPUMP.METADATA_FILTER(l_job_handle,
                              'EXCLUDE_PATH_EXPR',
                              'IN (''STATISTICS'')');

Related Solutions

Oracle 11g .DMP viewer

First of all you need to create a directory on the database.

Create or Replace Directory IMP_DIR as 'Path\Path\';

Ensure the user that will import the file has the privilige to Read & Write on the directory (In case you are not using SYS)

GRANT Read, Write ON DIRECTORY IMP_DIR TO user;

Then, include the directory parameter within the import statement

impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt directory=IMP_DIR

Oracle not trusting statistics after an import

A number of factors can change on import.

Fill factor for data blocks is likely more optimal. Partially filled data blocks from the development system will be filled on import. This can change over time if data is updated and added, but does not appear to be a factor in this case.
Changes in block placement of rows may alter the number of block required to retrieve by index. This can have positive or negative impacts on the data.
Cost parameter for data and or index blocks may be different on the two databases resulting in different cost weights for plans.
Block sizes may be different on the two databases resulting in different costs.
Newer versions of Oracle will recalculate statistics automatically as data changes. The exported statistics are normally a subset of the full statistics. There is a DBA_STATS package which can be used to export and import the full set of statistics.
Histogram sizes may be different on the two systems resulting in differences in level of detail. (Its been a while since I tuned this kind of thing.) If the histograms are missing, Oracle will sample the data and estimate. Different sample sizes can cause very different behavior.
Oracle may decide to use bitmap indexes in some cases. This can require regenerating the indexes on each query. You can add bitmap indexes for the required columns. This option can be disabled at the database level (it may be an undocumented setting).

Try generating cost statistics for the queries you are using on both the Development and Production systems. You may also want to examine the Statistics on both systems.

You may get more consistent results using transportable tablespaces for the tables and indexes. This will eliminate the import/export step.

EDIT: Added bitmap, statistics, and histogram info above.

It is possible to just run an explain plan without running the queries. There are various tools which assist in this. I have used Aqua Data Studio, but SQL Developer should work as well. For parsed queries (running or not) there is a view available from which the plan can be extracted.

There are available views which can be used to view the current statistics for a table. Comparing used blocks on both systems will give you a quick idea how much statistics should have changed. Histogram data is can result in different plans.

Related Question