Gather Stats in Oracle E-Business Suite with improved (Auto sampling) features of Oracle Database 11gR2
Schedule Concurrent Request:
Schema Name:
Estimate Percent:
Degree:
Backup Flag:
Restart Request ID:
History Mode:
Backup existing statistics prior to gather new statistics
Gather Options: (
GATHER_AUTO Option - gather statistics for tables based upon % change)
Modifications Threshold:
Invalidate Dependent Cursors:
Know Issue and fixes
Error 1- ORA-20005: object statistics are locked
**Starts**24-JUN-2014 21:00:54
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 780
ORA-06512: at line 1
.
The SQL statement being executed at the time of the error was: SELECT R.Con
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag=
stats on table AQ$_WF_CONTROL_P is locked
stats on table FND_CP_GSM_IPC_AQTBL is locked
stats on table FND_SOA_JMS_IN is locked
stats on table FND_SOA_JMS_OUT is locked
ORA-20005: object statistics are locked (stattype = ALL)
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
Solution:
Unlock object statistics:
exec dbms_stats.unlock_table_stats('APPLSYS','AQ$_WF_CONTROL_P');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_IPC_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_OUT');
Error 2- ORA-20005: object statistics are locked
**Starts**24-JUN-2014 21:11:03
ORACLE error 20001 in FDPSTP
Cause: FDPSTP failed due to ORA-20001: SYS_NT8GWEU//JUHJGQKJAFGGSUG== is an invalid identifier
ORA-06512: at "APPS.FND_STATS", line 774
ORA-06512: at line 1
.
The SQL statement being executed at the time of the error was: SE
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag=
ORA-20001: SYS_NT8GWEU//JUHJGQKJAFGGSUG== is an invalid identifier
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
Solution:
apply patch - 9542112 [ERROR IN R12 FOR GATHER SCHEMA STATS WITH GATHER AUTO OPTION to a non critical environment]
Reference:
When Using Gather Auto Option Errors - Is an Invalid Identifier (Doc ID 1363044.1)
Before - Overall Average Age in Days
