[20160713]impdp与统计信息导入.txt

[20160713]impdp与统计信息导入.txt

--许多做迁移使用导入imdp加入EXCLUDE=STATISTICS参数，避免统计信息导入。具体原因我觉得可能人为重新组织数据后，统计信息不准确。
--希望系统自己重新分析，实际上加入这个参数还导致另外的一种情况出现，测试看看。

1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING         VERSION        BANNER
------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立测试环境：
create table dept1 as select * from dept;
create unique index pk_dept1 on dept1(deptno);

--分析略。检查统计信息是否存在。
SCOTT@book> select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_tab_statistics where owner=user and table_name='DEPT1';
OWNER  TABLE_NAME   NUM_ROWS LAST_ANALYZED       STA
------ ---------- ---------- ------------------- ---
SCOTT  DEPT1               4 2016-07-13 15:54:01 NO

SCOTT@book> select OWNER,INDEX_NAME,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_ind_statistics where owner=user and table_name='DEPT1';
OWNER  INDEX_NAME DISTINCT_KEYS   NUM_ROWS LAST_ANALYZED       STA
------ ---------- ------------- ---------- ------------------- ---
SCOTT  PK_DEPT1               4          4 2016-07-13 15:54:01 NO

SCOTT@book> delete from dept1 where deptno=40;
1 row deleted.

SCOTT@book> commit ;
Commit complete.
--//注意这个时候统计信息已经不准确。

--导出：
$ expdp scott/book dumpfile=dept1.dmp logfile=dept1.log TABLES=scott.dept1
Export: Release 11.2.0.4.0 - Production on Wed Jul 13 16:06:52 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a* dumpfile=dept1.dmp logfile=dept1.log TABLES=scott.dept1
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 "SCOTT"."DEPT1"                             5.914 KB       3 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/book/dpdump/dept1.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 13 16:07:00 2016 elapsed 0 00:00:08

--//注意我没有directory参数，实际上oracle会自动使用缺省的DATA_PUMP_DIR目录名。

SCOTT@book> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ----------------------------------------
DATA_PUMP_DIR                  /u01/app/oracle/admin/book/dpdump/

3.导入：
--先删除表dept1.
SCOTT@book> drop table dept1 purge ;
Table dropped.

$ impdp scott/book dumpfile=dept1.dmp logfile=dept1x.log full=y EXCLUDE=STATISTICS
Import: Release 11.2.0.4.0 - Production on Wed Jul 13 16:07:48 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/a** dumpfile=dept1.dmp logfile=dept1x.log full=y EXCLUDE=STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT1"                             5.914 KB       3 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Wed Jul 13 16:07:52 2016 elapsed 0 00:00:03

SCOTT@book> select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_tab_statistics where owner=user and table_name='DEPT1';
OWNER  TABLE_NAME   NUM_ROWS LAST_ANALYZED       STA
------ ---------- ---------- ------------------- ---
SCOTT  DEPT1

SCOTT@book> select OWNER,INDEX_NAME,DISTINCT_KEYS,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_ind_statistics where owner=user and table_name='DEPT1';
OWNER  INDEX_NAME DISTINCT_KEYS   NUM_ROWS LAST_ANALYZED       STA
------ ---------- ------------- ---------- ------------------- ---
SCOTT  PK_DEPT1               3          3 2016-07-13 16:07:52

--可以发现索引存在统计信息，当然这个信息是准确的。因为导入数据时建立索引时，oracle自动分析了索引统计信息。
--受隐含参数_optimizer_compute_index_stats的影响。

SYS@book> @ &r/hide _optimizer_compute_index_stats
NAME                           DESCRIPTION                                            DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------ ------------------------------------------------------ ------------- ------------- ------------
_optimizer_compute_index_stats force index stats collection on index creation/rebuild TRUE          TRUE          TRUE

--这样就会出现表没有统计信息，而索引有统计信息的情况。不知道会对执行计划产生什么影响。
--补充加入EXCLUDE=STATISTICS,INDEX_STATISTICS也无效,依旧会建立索引统计信息。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2121979/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2121979/