DataPump Import (impdp) Is Analyzing The Statistics On Table Instead Of Using DBMS_STATS Package (Doc ID 1096917.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.1.0.6 [Release 10.2 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 08-Mar-2016***

Symptoms

You started DataPump import and observed that a huge amount of time is spent to analyze the statistics on some big tables. Why does DataPump import not set the pre-calculated statistics with package DBMS_STATS?

Let's follow the next tests to demonstrate this:

-- create a user and a normal table with an index, analyze these,
-- expdp/impdp with SQLFILE to see what statements are stored into

connect / as sysdba

create or replace directory tmp as '/tmp';

create user test identified by test default tablespace users temporary tablespace temp;
grant connect, resource to test;
grant read, write on directory tmp to test;

connect test/test

create table a_tab
(
   id   number,
   text varchar2(10)
);

insert into a_tab values (1, 'A text');
commit;

create index a_ind on a_tab (id);

analyze table a_tab compute statistics;

host expdp test/test directory=tmp dumpfile=a_tab_1.dmp tables=a_tab

This shows:

Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 28 April, 2010 7:59:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=tmp dumpfile=a_tab_1.dmp tables=a_tab
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."A_TAB"                 5.226 KB         1 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /tmp/a_tab_1.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 07:59:37

Extract the SQLFILE from dump above with:

host impdp test/test directory=tmp dumpfile=a_tab_1.dmp full=y sqlfile=a_tab_1.sql

The written file /tmp/a_tab_1.sql contains:

-- CONNECT TEST
-- new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "TEST"."A_TAB"
  ( "ID" NUMBER,
    "TEXT" VARCHAR2(10)
  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "USERS" ;

-- new object type path is: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "TEST"."A_IND" ON "TEST"."A_TAB" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" PARALLEL 1 ;

ALTER INDEX "TEST"."A_IND" NOPARALLEL;

-- new object type path is: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE IND_NAME VARCHAR2(60);
  IND_OWNER VARCHAR2(60);
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  IND_NAME := 'A_IND'; IND_OWNER := 'TEST';
  INSERT INTO "SYS"."IMPDP_STATS" (type, version, flags, c1, c2, c3, c5,
             n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, d1)
             VALUES ('I', 4, 0, IND_NAME, NULL, NULL, 'TEST', 1, 1, 1,
             1, 1, 1, 0, 1, NULL, NULL, NULL, NULL,
             TO_DATE('2010-04-28 07:58:38', 'YYYY-MM-DD:HH24:MI:SS'));

  DBMS_STATS.IMPORT_INDEX_STATS( '"' || ind_owner || '"', '"' || ind_name ||  '"', NULL, '"IMPDP_STATS"', NULL, '"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/

-- new object type path is: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  INSERT INTO "SYS"."IMPDP_STATS" (type, version, flags,c1, c2, c3,
             c5, n1, n2, n3, n4, n10, n11, n12, d1)
             VALUES ('T', 4, 0, 'A_TAB', NULL, NULL, 'TEST',
             1, 5, 13, 1, NULL, NULL, NULL,
             TO_DATE('2010-04-28 07:58:38', 'YYYY-MM-DD:HH24:MI:SS'));
 
  INSERT INTO "SYS"."IMPDP_STATS" (type, version,
             c1, c2, c3, c4, c5, n1,
             n2, n3, n4, n5, n6, n7, n8, n9, n10, n11,
             d1, r1, r2, ch1, flags)
             VALUES ('C', 4, 'A_TAB', NULL, NULL, 'ID', 'TEST',
             1, 1, 1, 1, 0, 1, 1, 2, NULL, NULL, NULL,
             TO_DATE('2010-04-28 07:58:38', 'YYYY-MM-DD:HH24:MI:SS'),
             'C102', 'C102', NULL, 0);

  INSERT INTO "SYS"."IMPDP_STATS" (type, version,
             c1, c2, c3, c4, c5, n1,
             n2, n3, n4, n5, n6, n7, n8, n9, n10, n11,
             d1, r1, r2, ch1, flags)
             VALUES ('C', 4, 'A_TAB', NULL, NULL, 'TEXT', 'TEST',
             1, 1, 1, 1, 0, 3.38157554782537E+35, 3.38157554782537E+35,
             6, NULL, NULL, NULL,
             TO_DATE('2010-04-28 07:58:38', 'YYYY-MM-DD:HH24:MI:SS'),
             '412074657874', '412074657874', NULL, 0);

  DBMS_STATS.IMPORT_TABLE_STATS('"TEST"', '"A_TAB"', NULL, '"IMPDP_STATS"', NULL, NULL, '"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/

=> For a normal table/index, you see the calls to:

Let's now create a function based index on table and see what happens:

connect test/test
drop table a_tab purge;

create table a_tab
(
   id   number,
   text varchar2(10)
);

insert into a_tab values (1, 'A text');
commit;

-- A DESC index is a function based one
create index a_ind on a_tab (id desc);

analyze table a_tab compute statistics;

host expdp test/test directory=tmp dumpfile=a_tab_2.dmp tables=a_tab

This ends with:

Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 28 April, 2010 9:10:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=tmp dumpfile=a_tab.dmp tables=a_tab reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."A_TAB"                   5.820 KB            1 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /tmp/a_tab.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 09:12:13

The SQLFILE generated with command:

host impdp test/test directory=tmp dumpfile=a_tab_2.dmp full=y sqlfile=a_tab_2.sql

shows:

-- CONNECT TEST
-- new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "TEST"."A_TAB"
  ( "ID" NUMBER,
    "TEXT" VARCHAR2(10)
  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "USERS" ;

-- new object type path is: TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
CREATE INDEX "TEST"."A_IND" ON "TEST"."A_TAB" ("ID" DESC)
 PCTFREE 10 INITRANS 2 MAXTRANS 255
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "USERS" PARALLEL 1 ;

ALTER INDEX "TEST"."A_IND" NOPARALLEL;

-- new object type path is: TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
DECLARE IND_NAME VARCHAR2(60);
  IND_OWNER VARCHAR2(60);
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  IND_NAME := 'A_IND'; IND_OWNER := 'TEST';
  INSERT INTO "SYS"."IMPDP_STATS" (type, version, flags, c1, c2, c3, c5,
               n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, d1)
               VALUES ('I', 4, 0, IND_NAME, NULL, NULL, 'TEST', 1, 1,
               1, 1, 1, 1, 0, 1, NULL, NULL, NULL, NULL,
               TO_DATE('2010-04-28 08:04:38', 'YYYY-MM-DD:HH24:MI:SS'));

  DBMS_STATS.IMPORT_INDEX_STATS( '"' || ind_owner || '"', '"' || ind_name || '"', NULL, '"IMPDP_STATS"', NULL, '"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/

-- new object type path is: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ANALYZE TABLE "TEST"."A_TAB" COMPUTE STATISTICS;

=> The call to DBMS_STATS.IMPORT_TABLE_STATS was replaced by statement ANALYZE TABLE COMPUTE STATISTICS (which is very slow for big tables/indexes).

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms