由于收集统计信息可能很慢，尤其对于大库。所以在迁移升级数据库的时候，我们可以考虑在测试阶段，把接近生产数据的测试环境做好统计信息的收集。
然后在生产割接的时候直接导入原测试环境导出的统计信息即可。这样能保障数据库性能在上线速度最快，并且保障性能可控（和测试期间效果至少相当)
导出统计信息：include=statistics
[oracle@lncs ~]$ expdp jyc/jyc directory=ORADMP cluster=n dumpfile=stat.dmp logfile=stat.log include=statistics schemas=jyc
Export: Release 11.2.0.4.0 - Production on Fri Jan 19 09:29:35 2024
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
FLASHBACK automatically enabled to preserve database integrity.
Starting "JYC"."SYS_EXPORT_SCHEMA_01": jyc/******** directory=ORADMP cluster=n dumpfile=stat.dmp logfile=stat.log include=statistics schemas=jyc
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "JYC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_SCHEMA_01 is:
/oracle/dmp/stat.dmp
Job "JYC"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jan 19 09:30:15 2024 elapsed 0 00:00:37
[oracle@lncs ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 19 09:30:44 2024
Copyright (c) 1982, 2013, Oracle. 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
SQL> exec DBMS_STATS.DELETE_SCHEMA_STATS(ownname=>'JYC');
PL/SQL procedure successfully completed.
SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lncs ~]$ sqlplus jyc/jyc
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 19 09:31:27 2024
Copyright (c) 1982, 2013, Oracle. 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
SQL> select table_name,num_rows,last_analyzed from user_tables where num_rows>0;
no rows selected
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lncs ~]$ impdp jyc/jyc directory=ORADMP cluster=n dumpfile=stat.dmp logfile=stat-1.log include=statistics schemas=jyc
Import: Release 11.2.0.4.0 - Production on Fri Jan 19 09:31:50 2024
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 "JYC"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_SCHEMA_01": jyc/******** directory=ORADMP cluster=n dumpfile=stat.dmp logfile=stat-1.log include=statistics schemas=jyc
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "JYC"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri Jan 19 09:31:54 2024 elapsed 0 00:00:03
[oracle@lncs ~]$ sqlplus jyc/jyc
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 19 09:32:12 2024
Copyright (c) 1982, 2013, Oracle. 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
SQL> select table_name,num_rows,last_analyzed from user_tables where num_rows>0;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
T 10 2024-01-19 09:27:58
TAB1 1 2024-01-19 09:27:58
SQL> select index_name,table_name,num_rows,last_analyzed from user_indexes;
INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
LAST_ANALYZED
-------------------
PK_T T 10
2024-01-19 09:27:58
IDX T 10
2024-01-19 09:27:58
SQL> select table_name,num_rows,last_analyzed from user_tables;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
T 10 2024-01-19 09:27:58
TAB1 1 2024-01-19 09:27:58
SQL> exec dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'T',method_opt=> 'for all columns',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE);
BEGIN dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'T',method_opt=> 'for all columns',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 1
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'TAB1',method_opt=> 'for all columns',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE);
BEGIN dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'TAB1',method_opt=> 'for all columns',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 1
SQL> EXEC DBMS_STATS.unlock_schema_stats(ownname => 'JYC');
PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,last_analyzed from user_tables;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
T 10 2024-01-19 09:27:58
TAB1 1 2024-01-19 09:27:58
SQL> exec dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'TAB1',method_opt=> 'for all columns',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'T',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,last_analyzed from user_tables;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
T 10 2024-01-19 09:47:07
TAB1 1 2024-01-19 09:46:15
SQL> select index_name,table_name,num_rows,last_analyzed from user_indexes;
INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
LAST_ANALYZED
-------------------
PK_T T 10
2024-01-19 09:47:07
IDX T 10
2024-01-19 09:47:07
手动收集统计信息命令参考：
alter session set parallel_force_local=true;
alter session set workarea_size_policy=manual;
alter session set sort_area_size=1073741820;
alter session set sort_area_retained_size=1073741820;
alter session set db_file_multiblock_read_count=128;
--按表
exec dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'XXX',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE);
--数据字典
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
--按用户
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'JYC',ESTIMATE_PERCENT=>100,cascade=>true,force=>true,degree=>64);
--全库（测试期间可以测试评估下时间）
exec dbms_stats.gather_database_stats(degree=>64,estimate_percent =>'100',cascade=>true);
--删除用户统计信息
exec DBMS_STATS.DELETE_SCHEMA_STATS(ownname=>'JYC');
其它锁定和解锁查询
如果要收集表的统计信息，使用下面SQL
exec dbms_stats.gather_table_stats('TEST', 'TEST',force=>true);
当然还有一种方法就是，先给表解锁统计信息，收集统计信息，然后锁定表的统计信息，如下所示：
exec dbms_stats.unlock_table_stats('TEST','TEST');
exec dbms_stats.gather_table_stats(ownname =>'TEST', tabname =>'TEST',cascade => true);
exec dbms_stats.lock_table_stats('TEST','TEST');
查看统计信息锁定的对象表或索引:
SET LINESIZE 680;
COL OWNER FOR A16
COL INDEX_NAME FOR A30
COL TABLE_OWNER FOR A16
COL TABLE_NAME FOR A30
COL PARTITION_NAME FOR A30
COL SUBPARTITION_NAME FOR A30
SELECT D.OWNER,
D.INDEX_NAME,
D.TABLE_OWNER,
D.TABLE_NAME,
D.PARTITION_NAME,
D.SUBPARTITION_NAME,
D.OBJECT_TYPE
FROM DBA_IND_STATISTICS D
WHERE STATTYPE_LOCKED IN('ALL', 'DATA', 'CACHE')
UNION ALL
SELECT '---',
'---',
D.OWNER,
D.TABLE_NAME,
D.PARTITION_NAME,
D.SUBPARTITION_NAME,
D.OBJECT_TYPE
FROM DBA_TAB_STATISTICS D
WHERE STATTYPE_LOCKED IN('ALL', 'DATA', 'CACHE');
STATTYPE_LOCKED为空代表统计信息未锁定。
查看统计信息锁定的表：
SET LINESIZE 680;
COL OWNER FOR A16
COL TABLE_OWNER FOR A16
COL TABLE_NAME FOR A30
COL PARTITION_NAME FOR A30
COL SUBPARTITION_NAME FOR A30
SELECT
D.OWNER,
D.TABLE_NAME,
D.PARTITION_NAME,
D.SUBPARTITION_NAME,
D.OBJECT_TYPE
FROM DBA_TAB_STATISTICS D
WHERE STATTYPE_LOCKED IN('ALL', 'DATA', 'CACHE');
解锁单个表的统计信息
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('scott','emp');
--解锁用户统计信息
解锁某个用户下(例如，scott用户）的表的统计信息
EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('scott');
