Gather Schema Statistics Failed after Major Upgrades >> Few Common Issues..

Issue: Customer reported "Gather Schema Statistics" concurrent program Erroring out after Major Upgrades as part of PMP.

Troubleshooting:

Reviewed the history and found that request ran successfully earlier 2weeks to this Task.

Req ID : 9065020 Gather Schema Statistics

Error logs :-

ORACLE error 6550 in FDPSTP

Cause: FDPSTP failed due to ORA-06550: line 1, column 7:

PLS-00307: too many declarations of 'GATHER_SCHEMA_STATS' match this call

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

.

After searching Metalink found below related note :

Gather Schema Statistics Fails with PLS-00307: Too Many Declarations Of GSS (Doc ID 951033.1)

Reviewed and worked accordingly

SQL> select ARGUMENT_TEXT from fnd_concurrent_requests where request_id = '9065020';

ARGUMENT_TEXT

--------------------------------------------------------------------------------

ALL, 25, 3, NOBACKUP

Compared to the list of parameters to the successful Request ID, and found that there are many other parameters .

Also took double confirmation comparing with that of Production Instance

Request ID with parameters ALL, 10, 4, NOBACKUP, , LASTRUN, GATHER AUTO, , Y ==>> for Gather Schema Statistics IN Prod

Workaround 1:-

-------------

Ran new request with complete parameters similar to that of Production...

9087325 ==> ALL, 25, 3, NOBACKUP, , LASTRUN, GATHER AUTO, , Y

Now request went bit further and failed again...

******************************************************

****** AGAIN Request Failed , But for diff error *****

******************************************************

New Error Information:

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

Unable to correctly update the history table - fnd_stats_hist.

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

Unable to correctly update the history table - fnd_stats_hist.

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

Unable to correctly update the history table - fnd_stats_hist.

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

Unable to correctly update the history table - fnd_stats_hist.

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

Unable to correctly update the history table - fnd_stats_hist.

-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated

ORA-20005: object statistics are locked (stattype = ALL)

+---------------------------------------------------------------------------+

End of log messages from FND_FILE

+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+

Executing request completion options...

Finished executing request completion options.

+---------------------------------------------------------------------------+

Concurrent request completed

Current system time is 28-AUG-2011 16:51:59

+--------------------------------------------------------------------------

Researched again for this new error in Metalink... and found below note

Workaround 2:

----------------

Solution: Support Note 781813.1

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.

Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

SQL> select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1; 2 3 4

TABLE_NAME COLUMN_NAME COUNT(*)

------------------------------ ------------------------------ ----------

JE_FR_DAS_010 TYPE_ENREG 2

JE_FR_DAS_010_NEW TYPE_ENREG 2

JE_BE_LINE_TYPE_MAP SOURCE 2

JG_ZZ_SYS_FORMATS_ALL_TL JGZZ_EFT_TYPE 2

JE_BE_LOGS DECLARATION_TYPE_CODE 2

JE_CH_PAYMENT_REF PAYMENT_TYPE 2

JG_ZZ_SYS_FORMATS_ALL_B JGZZ_EFT_TYPE 2

JE_BE_VAT_REP_RULES LINE_TYPE 2

JE_BE_VAT_REP_RULES SOURCE 2

JE_BE_VAT_REP_RULES VAT_REPORT_BOX 2

10 rows selected.

-- Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS

where table_name = '&TABLE_NAME'

and column_name = '&COLUMN_NAME'

and rownum=1;

-- Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS

where (table_name, column_name) in

(

select hc.table_name, hc.column_name

from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

where hc.table_name ='&TABLE_NAME'

and hc.table_name= tc.table_name (+)

and hc.column_name = tc.column_name (+)

and tc.column_name is null

);

Deleted all the duplicates .. IN my case I dont have any obsolete rows.. SO next queue is not useful this time...

Verification:

SQL> select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1; 2 3 4

no rows selected

SQL> sho user

USER is "APPS"

9087344 == > Gather Schema statistics >>> Failed again....

******************************************************

****** AGAIN Request Failed , But for diff error *****

******************************************************

After research found that

ORACLE error 20005 in FDPSTP

Cause: FDPSTP failed due to ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "APPS.FND_STATS", line 806

ORA-06512: at line 1

.

General Procedure:

-------------------

Check all the tables which come under Locked status :

select owner,table_name from dba_tab_statistics where stattype_locked is not null;

For each and every table , unlock using below query

dbms_stats.unlock_table_stats(OWNER,TABLE);

Finally Now execute the Gather Schema Statistics and see its getting successful.

Ex : 9087399 ==> Gather Schema Statistics >>> Completed Normal

Thanks

Kiran Ginni