LATEST TOPICS

Why Datapump Import (IMPDP) is not ignoring Index Statistics?

In today’s article, I would discuss a scenario where I was trying to speed up a Datapump import job by excluding the collection of table and index statistics during the import process.

I was using EXCLUDE=STATISTICS option while using the Datapump import (IMPDP) to exclude both table and index statistics collection. However, IMPDP was only able to ignore the statistics collection for the tables. IMPDP was still collecting statistics for the indexes during the import process.

I have simulated the scenario in my lab sever for demonstration purpose.

Simulation Environment:

Here, I am taking a Datapump export (EXPDP) of schema ‘TEST_SRC’ and would be importing it to a target schema called ‘TEST_DEST’. During the import process, I would try to exclude the statistics collection for both tables and indexes.

    Source Schema: TEST_SRC
    Target Schema: TEST_DEST
    Oracle version: 11.1.0.7

Demonstration:

My source schema ‘TEST_SRC’ has only one table and one index.

------
------ Source schema objects
------
SQL> select owner,object_name,object_type,status from dba_objects where owner='TEST_SRC';

OWNER                          OBJECT_NAME          OBJECT_TYPE         STATUS
------------------------------ -------------------- ------------------- -------
TEST_SRC                       IDX_TEST_T           INDEX               VALID
TEST_SRC                       TEST_T               TABLE               VALID

Lets, check the status of the statistics for the source schema objects.

-----
----- Table Statistics
-----
SQL> select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_tab_statistics where owner='TEST_SRC';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED      STA
------------------------------ ------------------------------ ---------- ------------------ ---
TEST_SRC                       TEST_T                               9934 30-APR-15          NO

-----
----- Index Statistics
-----
SQL> select OWNER,INDEX_NAME,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_ind_statistics where owner='TEST_SRC';

OWNER                          INDEX_NAME                     DISTINCT_KEYS   NUM_ROWS LAST_ANALYZED      STA
------------------------------ ------------------------------ ------------- ---------- ------------------ ---
TEST_SRC                       IDX_TEST_T                             10000      10000 30-APR-15          NO

Now, lets take a Datapump export (EXPDP) of the source schema ‘TEST_SRC’

oracle@mylab01:~> expdp directory=EXPORT dumpfile=test_src_exp.dmp logfile=test_src_exp.log schemas=test_src

Export: Release 11.1.0.7.0 - 64bit Production on Thursday, 30 April, 2015 1:34:10

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_03":  /******** AS SYSDBA directory=EXPORT dumpfile=test_src_exp.dmp logfile=test_src_exp.log schemas=test_src
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 176 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST_SRC"."TEST_T"                         141.9 KB   10000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_03 is:
  /s01/oradata1/export/test_src_exp.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_03" successfully completed at 01:35:01

We will now import the source schema (TEST_SRC) objects in to a new target schema (TEST_DEST) using Datapump import (IMPDP). We would also use the EXCLUDE=STATISTICS option to ignore statistics collection during import.

oracle@mylab01:~> impdp directory=EXPORT dumpfile=test_src_exp.dmp logfile=test_dest_imp.log schemas=test_src remap_schema=test_src:test_dest EXCLUDE=STATISTICS

Import: Release 11.1.0.7.0 - 64bit Production on Thursday, 30 April, 2015 1:40:52

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_02":  /******** AS SYSDBA directory=EXPORT dumpfile=test_src_exp.dmp logfile=test_dest_imp.log schemas=test_src remap_schema=test_src:test_dest EXCLUDE=STATISTICS
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST_DEST"."TEST_T"                        141.9 KB   10000 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Job "SYS"."SYS_IMPORT_SCHEMA_02" successfully completed at 01:41:02

Though the IMPDP logs shows that INDEX and TABLE statistics were not processed, it was actually collected for the imported indexes during the creation of INDEX in the database. Same can be confirmed by querying the database as follows:

-----
----- Table statistics ignored due to EXCLUDE=STATISTICS option
-----
SQL>  select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_tab_statistics where owner='TEST_DEST';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED      STA
------------------------------ ------------------------------ ---------- ------------------ ---
TEST_DEST                      TEST_T


-----
----- Index statistics not ignored even after using EXCLUDE=STATISTICS option
-----
SQL>  select OWNER,INDEX_NAME,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_ind_statistics where owner='TEST_DEST';

OWNER                          INDEX_NAME                     DISTINCT_KEYS   NUM_ROWS LAST_ANALYZED      STA
------------------------------ ------------------------------ ------------- ---------- ------------------ ---
TEST_DEST                      IDX_TEST_T                             10000      10000 30-APR-15

Root Cause:

After searching for while through Oracle docs, found that it is an expected behavior. It seems that, Oracle has automated the statistics collection during index creation/rebuild starting from Oracle version 10.2.0.1.

This behavior of statistics collection for indexes is controlled by the hidden database parameter ‘_optimizer_compute_index_stats’. The default value for this parameter is TRUE (force index stats collection on index creation/rebuild).

To confirm this, I have checked the parameter setting in the database.

SQL> SELECT
a.ksppinm Param ,
b.ksppstvl SessionVal ,
c.ksppstvl InstanceVal,
a.ksppdesc Descr
FROM
x$ksppi a ,
x$ksppcv b ,
x$ksppsv c
WHERE
a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '/_optimizer_compute_index_stats' escape '/'
/  

PARAM                          SESSIONVAL      INSTANCEVAL     DESCR
------------------------------ --------------- --------------- -------------------------------------------------------
_optimizer_compute_index_stats TRUE            TRUE            force index stats collection on index creation/rebuild

Here, we are. ‘_optimizer_compute_index_stats’ is set to TRUE (default) in our case.

To avoid statistics collection during the import (IMPDP) process, we can set the parameter ‘_optimizer_compute_index_stats’ to FALSE (recommended by Oracle itself) as follows.

SQL> alter system set "_optimizer_compute_index_stats"=FALSE;

Conclusion:

Hidden Hidden… Hidden.. So much of hidden parameters :)

Reference:

EXCLUDE=STATISTICS Or EXCLUDE=INDEX_STATISTICS During Datapump Import Still Analyzes The Indexes (Doc ID 793585.1)

9 Comments
  1. oracle
  2. Venkat
  3. george
    • Abu Fazal Abbas
  4. Afzal
    • Abu Fazal Abbas
  5. MunnaMia
    • Abu Fazal Abbas
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed