ADSTATS.SQL HANG AT DBMS_STATS.GATHER_SCHEMA_STATS. (文档 ID 466294.1)

EBS升级步骤之一就是数据库的升级，升级前建议执行的adstats.sql通常会花费10分钟左右甚至到1个小时左右，如果服务器性能还尚可，可以参考ADSTATS.SQL HANG AT DBMS_STATS.GATHER_SCHEMA_STATS. (文档 ID 466294.1)这份文档，本人曾经在某次升级11i到12.1.3的时候碰到过一次这样的问题，服务器性能很好，但是adstats.sql就是很慢，在方案优化过程中采用这个文档中所提到的方法缩短了部分时间。

原文转载如下：

To implement the solution, please execute the following steps:

You can perform the steps manually as a workaround instead of running the adstats.sql script and replacing the second command with another that doesn't cause a hang and this should complete successfuly.

 1. $ sqlplus "/ as sysdba"
 2. Set aq_tm_processes = 0.
 3. Set job_queue_processes = 0.
 4. SQL> shutdown normal;
 5. SQL> startup restrict;
 6. SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB');
 7. SQL> exec dbms_stats.gather_dictionary_stats;
 8. SQL> exec dbms_stats.gather_fixed_objects_stats;
 9. SQL> shutdown normal;
10. SQL> startup;
11. Revert to the old settings of aq_tm_processes and job_queue_processes.
12. SQL> exit;

OR

According to Note 362203.1 - "Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)", Section 1: Upgrading an 11i Database to Oracle Database 10g Release 2 (10.2.0):

"Before the Database Installation:

1. Verify software versions
The following software component versions must exist in your environment:
Software Minimum Version Tier Details
Oracle Applications (for those staying on 11i) 11.5.9 CU2 Application

Apply either

* Oracle Applications Release 11.5.9 (without Oracle Demand Planning) 1
* 11.5.9 E-Business Suite Consolidated Update 2
* 10g Release 2 interoperability patch for 11.5.9 (4653217)

or

* 11.5.10 E-Business Suite Consolidated Update 2
* 10g Release 2 interoperability patch for 11.5.10 (4653225) "

Since you are now on 11.5.10.2 you should have been applied 10g Release 2 interoperability patch for 11.5.10 (4653225) before the database upgrade.