Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, November 24, 2008

FND_STATS doesn't collect statistics on external tables

Recently we ran Gather Schema Statistics for all tables:

1.Log in to Oracle Applications with the System Administrator responsibility.
2.Navigate to the Submit Request window (Request > Run).
3.Submit the Gather Statistics program.
Set the schema name to ALL to gather statistics for all Oracle Applications schemas (having an entry in the FND_PRODUCT_INSTALLATIONS table). In addition to gathering index and table-level statistics, the program gathers column-level histogram statistics for all columns listed in the FND_HISTOGRAM_COLS table.

4. Use GATHER_AUTO option in the program.  This option gathers statistics for objects that are either lacking statistics or whose rows have changed significantly (default 10%) since the last time you gathered statistics.

GSS got stuck when it was collecting statistics on external tables.  GSS is not designed to gather statistics on external tables.  Patch 5876047 needs to be applied to solve this problem.  This patch modifies FND_STATS package to exclude gathering statistics on external tables.  However after applying this patch GSS will again fail with Ora-1 Errors 

Metalink Note:470556.1 gives this workaround:

Take a backup of the FND_STATS_HIST table
Truncate table FND_STATS_HIST
Re-submit Gather Schema Statistics program.

You do NOT need to truncate this table after each run of Gather Schema Statistics. Doing it once will be enough, because each run of GSS will use a new request_id

No comments: