ORA-20005: object statistics are locked

ORA-20005 WHEN GATHERING STATISTICS FOR EBS APPS 11i or R12 IN Database 10G or 11G (Doc ID 375351.1)

APPLIES TO:
Oracle EBS Applications Performance - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.
10g or 11g Database with Apps 11i or R12


SYMPTOMS
Gathering statistics using concurrent jobs "Gather Schema Statistics / Gather Table Statistics" or
running FND_STATS fails with "ORA-20005: object statistics are locked".

Example 1:

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=AR.AR_REV_REC_QT***ORA-20005: object statistics are locked (stattype = ALL)***

Example 2:

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP stats on table GAT_REQ_QTBL is locked

CAUSE

This can happen with Advance Queue tables. In 10g, if a queue is created or recreated during the upgrade, automatic statistics
gather is locked (disabled) on these queue.

The following statement can be used to check the tables which have statistics locked:

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

SOLUTION
Unlock statistics gathering on those queues running the commands below. To unlock all the tables in a schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner');

OR

To unlock individual tables (need to run for all tables individually):

exec dbms_stats.unlock_table_stats('table_owner','table_name');

Examples:

SQL> exec dbms_stats.unlock_schema_stats ('AR');
SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT');


NOTE:

1. For Applications Ebusiness Suite, stats SHOULD be collected in AQ tables.
 Therefore, stats should be unlocked in all AQ tables owned by any Apps schema

2. The current recommended version of FND_STATS  is provided in the patch

Patch 8452962 - FND_STATS SHOULD NOT GATHER STATS ON INTERNAL TEXT INDEX TABLES:$I, $R, $K,$N,$P

3. Is using EXEC FND_STATS.LOAD_XCLUD_TAB a valid workaround for this issue?

Although forcing the queue tables to be excluded will workaround the problem, it will eventually lead to performance
problems when those queue tables are used. The baseline support recommendation is that stats should be gathered on
 E-Business suite queue tables. Stats only normally become locked on E-Business suite queue tables when the queues
are dropped and recreated. This can happy manually via SQL and during patching. However the correct procedure here is
 to always unlock the stats in the queues are recreated. One will need to add this check to any post patching document you run onsite.

4. Can one force Gather Schema Statistics to continue if it hits an object with locked stats?

Forcing Gather Schema Statistics to continue if it hits an object with locked stats is not a good idea due to the performance
problems described in point 1. However as a DBA, it is ones database and ones choice about how to maintain your database.
 What one needs to do is monitor the amount of data being stored in the queue tables that are being locked.
If they consistently contain no rows then there is no use in including them in the GSS run so you could use the workaround above.

However the problem now is that GSS will fail if a NEW queue table has it's stats locks,
 this will lead one to a constant monitoring process until all of the queue tables have been excluded.
However for completeness, investigate the issue a little further and while it is possible to force
 DBMS_STATS.GATHER_SCHEMA_STATS to ignore locked stats by passing the "force=>TRUE" parameter.