PT - EXPDP IMPDP using EXPORT| IMPORT _TABLE_STATS Optimizer Statistics

Scenario:

Stats gathering of schema transport using expdp and impdp by creating stats gathering on particular table the by using EXPORT_TABLE_STATS and IMPORT_TABLE_STATS option on stats.

• Create table test2 and insert vlaues on table also create index. 

SQL> CREATE TABLE TEST.TEST2 ( ORDER_ID  NUMBER(12), ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE, ORDER_TOTAL NUMBER(8,2));

Table created.

SQL> INSERT INTO TEST.TEST2 SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL FROM TEST.ORDERS WHERE ORDER_TOTAL >100;

1304324 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX TEST.TEST2_TOTAL_IX ON TEST.TEST2(ORDER_TOTAL);

Index created.

• Statstics gathering of table test2

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

• Create stats table TEST_STATS

SQL> exec DBMS_STATS.CREATE_STAT_TABLE(OWNNAME=>'TEST',STATTAB=>'TEST_STATS');

PL/SQL procedure successfully completed.

• Export TEST_STATS using EXPORT_TABLE_STATS

SQL> exec DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', STATTAB=>'TEST_STATS', STATID=>NULL, STATOWN=>'TEST', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

• Perform EXPDP on  tables TEST_STATS

SQL> host expdp TEST/TEST directory=expdp dumpfile=TEST_STATS.dmp tables=TEST_STATS

Export: Release 12.2.0.1.0 - Production on Thu May 14 17:07:02 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "TEST"."SYS_EXPORT_TABLE_01":  TEST/******** directory=expdp dumpfile=TEST_STATS.dmp tables=TEST_STATS

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

. . exported "TEST"."TEST_STATS"                           18.25 KB       5 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

  C:\APP\EXPDP\TEST_STATS.DMP

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Thu May 14 17:07:22 2020 elapsed 0 00:00:19

SQL>

• We can delete stats of particular table for this example scenario we delete all and same table stats will be imported.

SQL> exec DBMS_STATS.DELETE_TABLE_STATS (OWNNAME=>'TEST', TABNAME=>'TEST2');

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT NUM_ROWS FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST2';

  NUM_ROWS

----------

SQL>

SQL> exec DBMS_STATS.DROP_STAT_TABLE('TEST','TEST_STATS');

PL/SQL procedure successfully completed.

SQL>

SQL> exec DBMS_STATS.DROP_STAT_TABLE('TEST','TEST_STATS');

PL/SQL procedure successfully completed.

• IMPDP of table TEST_STATS on same DB as previously we clean all things before import operation.

SQL> host impdp TEST/TEST directory=expdp dumpfile=TEST_STATS.dmp tables=TEST_STATS

Import: Release 12.2.0.1.0 - Production on Thu May 14 17:11:40 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "TEST"."SYS_IMPORT_TABLE_01":  TEST/******** directory=expdp dumpfile=TEST_STATS.dmp tables=TEST_STATS

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "TEST"."TEST_STATS"                           18.25 KB       5 rows

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

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at Thu May 14 17:11:49 2020 elapsed 0 00:00:09

SQL>

• Import stats of table 

SQL> exec DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'TEST2', STATTAB=>'TEST_STATS',STATID=>NULL,STATOWN=>'TEST');

PL/SQL procedure successfully completed.

SQL>

• Check the output same as earlier

SQL> SELECT NUM_ROWS FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TEST2';

  NUM_ROWS

----------

   1304324

SQL>

SQL> exec DBMS_STATS.DROP_STAT_TABLE('TEST','TEST_STATS');

PL/SQL procedure successfully completed.