Wednesday, July 6, 2016

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

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, check the parameter setting in the database.
#######################################################
 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;



Reference:

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




No comments:

Post a Comment