FND_STATS and DBMS_STATS

Automatic collection of statistics is a new feature introduced from 10g. However, this should be disabled in case of Apps instances as FND_STATS is used to collect statistics instead of DBMS_STATS. This is stated in Metalink Note 368252.1:

6) 10g has a new feature that gather statistics automatically, using the GATHER_STATS_JOB. Can the automatic statistics gathering job be used in 10G databases for Apps 11i?

No. The GATHER_STATS_JOB job collects stats using DBMS_STATS and should be disabled for Apps 11i.

If it is currently running, the automatic statistics gathering job should be disabled by running the following command:
dbms_scheduler.disable(''GATHER_STATS_JOB'');

Note: Normally, it is not necessary to disable this job, because this should be done by the script adstats.sql, which runs during the DB upgrade process.

Metalink Note :556121.1 says that "ORA-08103: object no longer exists" would occur if the GATHER_STATS_JOB is left enabled in an 11i/10g instance.

I went through the code inside FND_STATS and DBMS_STATS. DBMS_STATS code is encrypted and can't be read. FND_STATS code is readable. In many metalink notes, FND_STATS is described as a wrapper over DBMS_STATS package.

I gave this query to cross check:

1 select line,text
2 from dba_source
3 where text like '%DBMS_STATS%'
4* and name='FND_STATS'
SQL> /
249
DBMS_STATS.CREATE_STAT_TABLE(fnd_statown,fnd_stattab);

267
DBMS_STATS.CREATE_STAT_TABLE(schemaname,tabname,tblspcname);

359
DBMS_STATS.EXPORT_SCHEMA_STATS(schemaname, fnd_stattab, statid,

363
DBMS_STATS.EXPORT_SCHEMA_STATS(c_schema.sname, fnd_stattab, statid,

429
DBMS_STATS.EXPORT_TABLE_STATS(schemaname,

449
DBMS_STATS.IMPORT_SCHEMA_STATS(schemaname, fnd_stattab, statid,

453
DBMS_STATS.IMPORT_SCHEMA_STATS(c_schema.sname, fnd_stattab, statid,

517
DBMS_STATS.IMPORT_TABLE_STATS(ownname,tabname,partname,

532
DBMS_STATS.IMPORT_INDEX_STATS(ownname,indname,partname,fnd_stattab,

548
DBMS_STATS.IMPORT_COLUMN_STATS(ownname, tabname, colname, partname,

587
DBMS_STATS.IMPORT_COLUMN_STATS(c_rec.ownname,c_rec.tabname,

640
DBMS_STATS.GATHER_TABLE_STATS( ownname => ownname ,

655
l_tmp_str:= 'BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname ,'||

712
DBMS_STATS.GATHER_INDEX_STATS( ownname => ownname ,

718
l_tmp_str:= 'BEGIN DBMS_STATS.GATHER_INDEX_STATS( ownname => :ownname ,'||

1030
EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; END;' ;

1226
dlog('Please use DBMS_STATS package to gather stats on SYS objects.');

1294
DBMS_STATS.EXPORT_INDEX_STATS( ownname, indname, null,

1465
DBMS_STATS.EXPORT_TABLE_STATS(ownname, tabname, partname,

1544
-- Due to the limitations of in DBMS_STATS in 8i we need to call

1707
DBMS_STATS.EXPORT_COLUMN_STATS(list_ownname(i),

2048
DBMS_STATS.EXPORT_COLUMN_STATS ( ownname,

2099
DBMS_STATS.SET_TABLE_STATS(ownname,

2143
DBMS_STATS.SET_INDEX_STATS(ownname,


24 rows selected.


As per eTRM:

APPS.FND_STATS references the following:

APPLSYS

FND_HISTOGRAM_COLS

FND_ORACLE_USERID

FND_PRODUCT_INSTALLATIONS

FND_STATS_HIST

FND_STATS_HIST_S

APPS

FND_FILE - show dependent code

FND_GLOBAL - show dependent code

FND_HISTOGRAM_COLS

FND_INSTALLATION - show dependent code

FND_ORACLE_USERID

FND_PRODUCT_INSTALLATIONS

FND_STATS - show dependent code

FND_STATS_HIST

FND_STATS_HIST_S

PUBLIC

DBA_HISTOGRAMS

DBA_INDEXES

DBA_IND_COLUMNS

DBA_TABLES

DBA_TAB_COLUMNS

DBA_TAB_PARTITIONS

DBMS_OUTPUT

DBMS_SPACE

DBMS_STATS

DUAL

PLITBLM

V$INSTANCE

V$PARAMETER

SYS

DBA_INDEXES

DBA_IND_COLUMNS

DBA_TABLES

DBA_TAB_COLUMNS

DBA_TAB_HISTOGRAMS

DBA_TAB_MODIFICATIONS

DBA_TAB_PARTITIONS

DBMS_OUTPUT - show dependent code

DBMS_SPACE - show dependent code

DBMS_STANDARD - show dependent code

DBMS_STATS - show dependent code

DUAL

PLITBLM - show dependent code

STANDARD - show dependent code

V_$INSTANCE

V_$PARAMETER

Some more details about FND_STATS are given in Metalink Note 419728.1 which describes 3 ways of collecting statistics in 11i: concurrent process, temp tables and manually.

FND_STATS also collects Histograms automatically which is not done by DBMS_STATS. Metalink Note 429002.1 has more details:

Goal

How to collect histograms in Apps Ebusiness Suite using FND_STATS

Solution

Histograms are useful when a column has skewed data. The CBO (Cost Based Optimizer) may use that information when trying to find the best plan to retrieve the requested data.
If no histograms are present, the CBO assumes an even distribution of data. Histograms tell the CBO when data is not evenly distributed, and can help the CBO to estimate the number of rows returned from a table join (called "cardinality"). Having histograms on skewed column may aid the optimizer in making a proper decision.

FND_STATS collects histograms information if a column is listed in FND_HOSTOGRAM_COLS.

To create histogram on columns using FND_STATS you can use the procedure FND_STATS.LOAD_HISTOGRAM_COLS, like in the example:

1.sqlplus apps/
set lines 200;
set pagesize 35;
set trim on;
set trims on;
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
alter session set timed_statistics = true;
set feedback on;
select to_char(sysdate) time from dual;

2. After finding which column should have a histogram, use the procedure 'load_histogram_cols' to update the table 'fnd_histograms_cols, like:

execute fnd_stats.load_histogram_cols(action => 'INSERT', appl_id => '', tabname => '', colname => '', hsize => '');

Example:

exec fnd_stats.load_histogram_cols(action => 'INSERT', appl_id => 274, tabname => 'FEM_NAT_ACCTS_HIER', colname => 'LAST_UPDATED_BY', hsize => '100');

4. Check that the data was inserted in fnd_histogram_cols:

Select column_name, hsize from applsys.fnd_histogram_cols where table_name =
'FEM_NAT_ACCTS_HIER';

5. Check the current data :

select * from dba_tab_histograms where table_name = 'FEM_NAT_ACCTS_HIER' and owner = 'FEM';

6. Gather new statistics

exec fnd_stats.gather_table_stats('FEM','FEM_NAT_ACCTS_HIER');

7. Check the new data collected:

select * from dba_tables where table_name = 'FEM_NAT_ACCTS_HIER' and owner = 'FEM';
select * from dba_tab_histograms where table_name = 'FEM_NAT_ACCTS_HIER' and owner = 'FEM';
select * from dba_tab_columns where table_name = 'FEM_NAT_ACCTS_HIER' and owner = 'FEM';

Notes :

1) Manually inserting into FND_HOSTOGRAM_COLS is not supported in Oracle applications.

2) Once a column was added to FND_HISTOGRAM_COLS, there is no way to distinguish between seeded histograms and the custom created histograms (make sure to take note of that).

3) Make sure that you really need histogram in a particular column. If you added a histogram and it did not help in the problem that you were investigating, drop it.

References

Note 122371.1 - How To Gather Statistics For Oracle Applications 11i

However FND_STATS can not be used to gather statistics for SYS schema:

1 select line,text
2 from dba_source
3 WHERE TEXT LIKE '%SYS%'
4* AND NAME='FND_STATS'
SQL> /

LINE
TEXT
--------------------------------------------------------------------------------
16 fnd_statown varchar2(30) := 'APPLSYS'; -- Owner of the backup table
939 if (upper(schemaname) <> 'SYS') then
1224 else -- schema is SYS, print message in log.
1225 dlog('Gathering statistics on the SYS schema using FND_STATS is not allowed.');
1226 dlog('Please use DBMS_STATS package to gather stats on SYS bjects.');
1227 end if; -- end of schema<> SYS

6 rows selected.

So, you should run this command once daily on your instance to be current on SYS statistics as collection of SYS statistics is mandatory in 10g:

execute dbms_stats.gather_dictionary_stats();

I have asked Oracle in an SR about how often this should be run. Will update the post once I learn.