This blog post contributed by Ajay Verma.

Statistics gathering is an important activity in order to enable the
cost based optimizer to take a decision on the execution plan to choose
from the various possible ones with which a particular SQL statement can
be run. It is recommended to gather the statistics for an object when
it is in steady state, i.e. when the corresponding tables have a fair
amount of data giving out statistics which would lead the best execution
plan to be picked by CBO.

From 10g onwards , Oracle database
introduced Automatic Optimizer Statistics Collection feature in which a
scheduled job GATHER_STATS_JOB runs in the nights and gathers stats for
tables with either empty or stale statistics using DBMS_STATS  package APIs. This indeed is a great
feature but for highly volatile tables such as AQ/Streams tables  it is
quite possible that when the stats collection job runs, these tables may
not have the data that is representative of their full load period. One
scenario can be stats collection on queue tables when they were empty,
and the optimizer, as a result, choosing poor execution plans when they
have a lot of data in them during normal workload hours.

As a
direct fix to tackle the above issue, from 10gR2 database onwards, AQ
table stats are getting locked immediately after its creation so that
the auto stats collector doesn’t change it in the future. This can be
confirmed as below:

SQL> CREATE TYPE event_msg_type AS OBJECT   
                  (  name            VARCHAR2(10), 
                     current_status  NUMBER(5),  
                     next_status     NUMBER(5)  
                   ); 
Type created.
--Create Queue Table
SQL> EXEC DBMS_AQADM.create_queue_table(
              queue_table=>'test_queue_tab',
              queue_payload_type=>'event_msg_type')
PL/SQL procedure successfully completed. 
--Create Queue
SQL> EXEC DBMS_AQADM.create_queue(queue_name =>'test_queue',
                             queue_table=>'test_queue_tab');
PL/SQL procedure successfully completed.
--Check for locks on stats
SQL> select owner, table_name, stattype_locked
     from dba_tab_statistics
     where stattype_locked is not null
     and owner='APPS' and table_name='TEST_QUEUE_TAB';
OWNER       TABLE_NAME      STAT
---------- ---------------- ----- 
APPS        TEST_QUEUE_TAB   ALL 
--Gather stats
SQL> EXEC dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB')
BEGIN dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB'); END; 
* 
ERROR at line 1: 
ORA-20005: object statistics are locked (stattype = ALL) 
ORA-06512: at "SYS.DBMS_STATS", line 18408 
ORA-06512: at "SYS.DBMS_STATS", line 18429 
ORA-06512: at line 1

As we can see above , queue table stats are locked and hence further
stats gathering fails. We can still go ahead and use force parameter provided in DBMS_STATS package to override any lock on
statistics:

SQL>exec dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB'
      ,force=>TRUE)
PL/SQL procedure successfully completed.

The recommended use is to gather statistics with a representative
queue message load and keep the stats locked to avoid getting picked up
by auto stats collector.

What happens in Apps Instance?

From Oracle Apps 11i  (on top of Oracle Database 10gR2) instance
onwards, the automatic statistics gathering job GATHER_STATS_JOB is
disabled using $APPL_TOP/admin/adstats.sql , as can be confirmed below:

 
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS 
WHERE job_name = 'GATHER_STATS_JOB';
JOB_NAME                       ENABLE
------------------------------ ------
GATHER_STATS_JOB               FALSE

Oracle Apps provides separate concurrent programs which make use of
procedures in FND_STATS package to gather statistics for apps database
objects. FND_STATS is basically a wrapper around DBMS_STATS and is
recommended by Oracle for stats collection in Oracle Apps environment
because of the flexibility it provides in identifying the objects with
empty and stale stats. The modification threshold ( % of rows used to
estimate) is fixed to 10% in DBMS_STATS while it can vary from 0 to 100%
incase of FND_STATS.

But FND_STATS doesn’t provide the force
parameter to override any lock on statistics and hence fails while
gathering statistics for a queue table whose stats are locked :

 
SQL> select owner, table_name, stattype_locked ,last_analyzed
  2   from dba_tab_statistics where table_name='WF_DEFERRED'
  3  and owner='APPLSYS';
OWNER          TABLE_NAME      STATT LAST_ANAL
-------------- --------------- ----- ---------
APPLSYS        WF_DEFERRED     ALL   23-FEB-09
SQL> exec fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED');
BEGIN fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "APPS.FND_STATS", line 1505
ORA-06512: at line 1

The same error is observed on running the Gather Table Statistics
concurrent program for such queue tables. Gather Schema Statistics
concurrent program in general skips the table incase any error is
encountered while gathering its stats and hence the queue tables with
locked stats are also skipped.

Hence currently ,  in order to carry out stat collection for Workflow
queue tables whose stats are locked,the approach should be to unlock
the queue table temporarily using DBMS_STAT’s Unlock APIs when the table have a representative
load for the correct stats to be gathered, gather the statistics using
FND_STATS and lock it again.

SQL> begin
  2    dbms_stats.unlock_table_stats('APPLSYS','WF_DEFERRED');
  3    fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED');
  4    dbms_stats.lock_table_stats('APPLSYS','WF_DEFERRED');
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL> select owner, table_name, stattype_locked ,last_analyzed
  2  from dba_tab_statistics where table_name='WF_DEFERRED'
  3  and owner='APPLSYS';
OWNER            TABLE_NAME       STATT LAST_ANALYZED
---------------- ---------------- ----- -------------
APPLSYS          WF_DEFERRED      ALL   12-APR-09

Another approach can be to unlock the WF queue table stats during the
adpatch run itself as Oracle’s automatic stats collection feature is
disabled in Apps environments and the stats collection in general occurs
through the Apps provided concurrent programs. What say?