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
