ORA-20005: stats on table AQ$_WF_CONTROL_P is locked

FNDGSCST module: Gather Schema Statistics
+---------------------------------------------------------------------------+
Current system time is 26-FEB-2011 17:00:12
+---------------------------------------------------------------------------+
**Starts**26-FEB-2011 17:00:12
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:  and was exe
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 20 degree = 16 internal_flag= NOBACKUP
stats on table AQ$_WF_CONTROL_P is locked 
ORA-20005: object statistics are locked (stattype = ALL)
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
Successfully resubmitted concurrent program FNDGSCST with request ID 442226 to start at 26-FEB-2011 19:00:00 (ROUTINE=AFPSRS)

Gather Schema Statistics In Oracle

Looking way to optmize database by running gather schema statistics in Oracle? You as DBA can generate statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

The cost-based optimization (CBO) approach uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan.

The optimizer uses the selectivity of a predicate to estimate the cost of a particular access method and to determine the optimal join order and join method.

You should gather statistics periodically for objects where the statistics become stale over time because of changing data volumes or changes in column values.

Gather Schema Stats Syntax

In Oracle E-Business Suite, you can run the concurrent request name Gather Schema Statistics in System Administrator. This is similar to running the DBMS_STATS.gather_schema_stats from Oracle database.

SQL<span style="color: rgb(102, 204, 102);">></span> <span style="color: rgb(153, 51, 51); font-weight: bold;">EXEC</span> DBMS_STATS<span style="color: rgb(102, 204, 102);">.</span>gather_schema_stats<span style="color: rgb(102, 204, 102);">(</span>ownname <span style="color: rgb(102, 204, 102);">=></span> <span style="color: rgb(255, 0, 0);">'APPLSYS'</span><span style="color: rgb(102, 204, 102);">,</span> estimate_percent <span style="color: rgb(102, 204, 102);">=></span> <span style="color: rgb(204, 102, 204);">99</span><span style="color: rgb(102, 204, 102);">)</span>;

There’s one time we faced the error when we run the Gather Schema Statistics for APPLSYS.

<span style="color: rgb(153, 51, 51); font-weight: bold;">IN</span> GATHER_SCHEMA_STATS <span style="color: rgb(102, 204, 102);">,</span> schema_name<span style="color: rgb(102, 204, 102);">=</span> APPLSYS percent<span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(204, 102, 204);">99</span> <br />degree <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(204, 102, 204);">9</span> internal_flag<span style="color: rgb(102, 204, 102);">=</span> NOBACKUP<br />Error #<span style="color: rgb(204, 102, 204);">1</span>: ERROR: While GATHER_TABLE_STATS:<br />object_name<span style="color: rgb(102, 204, 102);">=</span>APPLSYS<span style="color: rgb(102, 204, 102);">.</span>AQ$_WF_CONTROL_P<span style="color: rgb(102, 204, 102);">***</span><br />ORA<span style="color: rgb(102, 204, 102);">-</span><span style="color: rgb(204, 102, 204);">20005</span>: object statistics are locked <span style="color: rgb(102, 204, 102);">(</span>stattype <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">ALL</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">***</span><br />Error #<span style="color: rgb(204, 102, 204);">2</span>: ERROR: While GATHER_TABLE_STATS:<br />object_name<span style="color: rgb(102, 204, 102);">=</span>APPLSYS<span style="color: rgb(102, 204, 102);">.</span>WF_NOTIFICATION_OUT<span style="color: rgb(102, 204, 102);">***</span><br />ORA<span style="color: rgb(102, 204, 102);">-</span><span style="color: rgb(204, 102, 204);">20005</span>: object statistics are locked <span style="color: rgb(102, 204, 102);">(</span>stattype <span style="color: rgb(102, 204, 102);">=</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">ALL</span><span style="color: rgb(102, 204, 102);">)</span><span style="color: rgb(102, 204, 102);">***</span>

According to some of the notes, this can happen with Advance Queue table in Oracle Database 10g.

If a queue is created or recreated during the upgrade, automatic statistics gather is locked (disabled) on these queue.

You can use the following statement can be used to check the tables which have statistics locked.

SQL<span style="color: rgb(102, 204, 102);">></span> <br /><span style="color: rgb(153, 51, 51); font-weight: bold;">SELECT</span> owner<span style="color: rgb(102, 204, 102);">,</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">TABLE_NAME</span><span style="color: rgb(102, 204, 102);">,</span> stattype_locked <span style="color: rgb(153, 51, 51); font-weight: bold;">FROM</span> dba_tab_statistics <br /><span style="color: rgb(153, 51, 51); font-weight: bold;">WHERE</span> stattype_locked <span style="color: rgb(153, 51, 51); font-weight: bold;">IS</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">NOT</span> <span style="color: rgb(153, 51, 51); font-weight: bold;">NULL</span> AND owner = 'APPLSYS';

You can run this command as below to unlock the statistics but please confirm with Oracle Support before proceed.

SQL<span style="color: rgb(102, 204, 102);">></span> <span style="color: rgb(153, 51, 51); font-weight: bold;">EXEC</span> dbms_stats<span style="color: rgb(102, 204, 102);">.</span>unlock_schema_stats <span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">'APPLSYS'</span><span style="color: rgb(102, 204, 102);">)</span>; <br />SQL<span style="color: rgb(102, 204, 102);">></span> <span style="color: rgb(153, 51, 51); font-weight: bold;">EXEC</span> dbms_stats<span style="color: rgb(102, 204, 102);">.</span>unlock_table_stats<span style="color: rgb(102, 204, 102);">(</span><span style="color: rgb(255, 0, 0);">'APPLSYS'</span><span style="color: rgb(102, 204, 102);">,</span> <span style="color: rgb(255, 0, 0);">'</span><span style="color: rgb(102, 204, 102);"></span>AQ$_WF_CONTROL_P<span style="color: rgb(255, 0, 0);">'</span><span style="color: rgb(102, 204, 102);">)</span>;

Most Oracle experts only recommend scheduled re-analysis for highly dynamic databases.

Advertisements

About this entry