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

最新推荐文章于 2021-04-13 11:34:35 发布

转载最新推荐文章于 2021-04-13 11:34:35 发布 · 1k 阅读

OracleERP_BI 专栏收录该内容

31 篇文章

订阅专栏

本文分享了一种在数据库升级过程中优化adstats.sql执行速度的方法，通过手动执行一系列步骤来减少挂起时间，适用于Oracle数据库升级场景。同时强调在升级前验证软件版本，确保兼容性。

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.