小记:
有次数据库用IMPDP/EXPDP迁移结束后,发现有些表的查询比迁移前更慢了非常,首先检查了该schema 的统计信息,发现db_ind_statistics 表中该schema的 stattype_locked 的值都是all.
分析这次迁移的整个过程:
1)expdp 导出数据结构(content=metadata_only)
2)expdp 导出数据 (content=data_only)
3)impdp 导入数据结构
4)impdp 导入数据
故事发展:
翻查后发现expdp并impdp 数据结构和结构后会将统计信息锁定,如果此时手动去做分析,会出现ora-20005,该错误10g后就存在,因为以前都是全导,故没有碰到这么妖的问题。可以通过如下方法 workaroud 这个问题:
expdp content=metadata_only 的时候使用 exclude=(table_statistics,index_statistics) 。
然后手动导出、导入统计信息:
故事后记:
当我们手动对统计信息操作时候,可以通过如下命令:
1)手动锁定统计信息方法:
exec dbms_stats.lock_schema_stats('TABLE_NAME');
2)手动解锁统计信息方法:
exec dbms_stats.unlock_schema_stats('TABLE_NAME');
IMPDP/EXPDP 方法验证,仅从源端导出表结构,并导入目标端的测试(统计信息果然被lock):
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
SQL> select * from all_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ----------------------------------------------------------------------------
SYS BACKUP /home/oracle/backup
SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
SYS XMLDIR /ade/b/1191423112/oracle/rdbms/xml
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
SYS DATA_PUMP_DIR /u01/app/oracle/admin/wtf/dpdump/
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
9 rows selected.
SQL> !
[oracle@test1 ~]$ expdp scott/tiger directory=backup dumpfile=scott.dmp content=metadata_only
Export: Release 11.2.0.1.0 - Production on Wed Feb 27 02:00:34 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=backup dumpfile=scott.dmp content=metadata_only
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/backup/scott.dump
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 02:02:02
[oracle@test1 ~]$ exit
exit
SQL> show user
USER is "SYS"
SQL> drop user test cascade;
User dropped.
SQL> !
[oracle@test1 backup]$ impdp scott/tiger directory=backup dumpfile=scott.dmp remap_schema=scott:test
Import: Release 11.2.0.1.0 - Production on Wed Feb 27 02:07:39 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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/******** directory=backup dumpfile=scott.dump remap_schema=scott:test
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 02:07:56
[oracle@test1 backup]$ exit
exit
SQL> select owner,table_name,index_name,stattype_locked from dba_ind_statistics where owner='TEST';
OWNER TABLE_NAME INDEX_NAME STATTYPE_LOCKED
------------------------------ --------------------- --------------------- ----------------
TEST DEPT PK_DEPT ALL
TEST DEPT DX_DNAME ALL
TEST EMP PK_EMP ALL
参考文献:
1).Subject: ORA-38029 "Object Statistics Are Locked" - Possible Causes
Doc [ID 433240.1] Type: PROBLEM
Modified Date : 11-JUN-2008 Status: PUBLISHED