【数据库升级】dbms_registry_sys.gather_stats过程可能过慢

dbms_registry_sys.gather_stats 存储过程在数据库字典升级 catupgrad.sql运行过程中被调用，该存储过程负责收集各 component 包括字典收集统计信息Gather Dictionary Schema Statistics ，如果 系统中AWR保存时间过长 例如30天以上，则可能耗费大量时间在收集一些WRH$、WRI$的AWR基础表上，这是正常的。 经验是 80GB的SYSAUX表空间在EMC中端存储下， Gather Dictionary Schema Statistics大约耗时58分钟； 有国外的朋友遇到过dbms_registry_sys.gather_stats(NULL)超过4小时。

PROCEDURE gather_stats (comp_id IN VARCHAR2);

文档During A Manual Database Upgrade To 11.2, Gathering Dictionary Statistics Takes Too Long (catupgrd.sql, cmpupend.sql) [ID 1425763.1]给出了一些解决方案，包括：

Solution

1. Make sure that you gather dictionary statistics as a preparation step before upgrading, by executing the following command:
SQL> EXECUTE dbms_stats.gather_dictionary_stats;

2. To reduce downtime for the database upgrade, set the following parameter in the init.ora (or spfile) before upgrading:

Note: DBUA will not retain the hidden parameter and this parameter is required to be set for the post-installation steps so setting this parameter will not help if you are upgrading database using DBUA .

_optim_dict_stats_at_db_cr_upg = false

Once all post install steps have been completed, remove (or comment out) the _optim_dict_stats_at_db_cr_upg parameter from the init.ora (or spfile)
NOTE: This parameter is only for reducing downtime during a database upgrade

3. Gather dictionary statistics again after the database has been upgraded successfully.

Please refer to the following articles for complete information about gathering statistics:
How to Gather Statistics on SYS Objects and ‘Fixed’ Objects? (Note:457926.1)
How to Gather Optimizer Statistics on 11g (Note:749227.1)