ADSTATS.SQL HANG AT DBMS_STATS.GATHER_SCHEMA_STATS. (Doc ID 466294.1)

 

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 to 10.2.0.5 [Release 10.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

During an Oracle Application upgrade according to Note 362203.1 and when performing STEP 28, the adstats.sql script hangs and never completes.

Cause

Adstats.sql runs the following commands :

dbms_scheduler.disable('GATHER_STATS_JOB');

dbms_stats.gather_schema_stats('SYS',degree=>30,estimate_percent=>100,cascade=>TRUE);

dbms_stats.gather_fixed_objects_stats;

It's usually hanging while running the second command.

 

Solution

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.

References