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

Thursday, June 26, 2008

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:

SchemaAPPLSYS
TableFND_HISTOGRAM_COLS
TableFND_ORACLE_USERID
TableFND_PRODUCT_INSTALLATIONS
TableFND_STATS_HIST
SequenceFND_STATS_HIST_S
SchemaAPPS
PL/SQL PackageFND_FILE - show dependent code
PL/SQL PackageFND_GLOBAL - show dependent code
SynonymFND_HISTOGRAM_COLS
PL/SQL PackageFND_INSTALLATION - show dependent code
SynonymFND_ORACLE_USERID
SynonymFND_PRODUCT_INSTALLATIONS
PL/SQL PackageFND_STATS - show dependent code
SynonymFND_STATS_HIST
SynonymFND_STATS_HIST_S
SchemaPUBLIC
SynonymDBA_HISTOGRAMS
SynonymDBA_INDEXES
SynonymDBA_IND_COLUMNS
SynonymDBA_TABLES
SynonymDBA_TAB_COLUMNS
SynonymDBA_TAB_PARTITIONS
SynonymDBMS_OUTPUT
SynonymDBMS_SPACE
SynonymDBMS_STATS
SynonymDUAL
SynonymPLITBLM
SynonymV$INSTANCE
SynonymV$PARAMETER
SchemaSYS
ViewDBA_INDEXES
ViewDBA_IND_COLUMNS
ViewDBA_TABLES
ViewDBA_TAB_COLUMNS
ViewDBA_TAB_HISTOGRAMS
ViewDBA_TAB_MODIFICATIONS
ViewDBA_TAB_PARTITIONS
PL/SQL PackageDBMS_OUTPUT - show dependent code
PL/SQL PackageDBMS_SPACE - show dependent code
PL/SQL PackageDBMS_STANDARD - show dependent code
PL/SQL PackageDBMS_STATS - show dependent code
TableDUAL
PL/SQL PackagePLITBLM - show dependent code
PL/SQL PackageSTANDARD - show dependent code
ViewV_$INSTANCE
ViewV_$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.

2 comments:

ORACLE DBA SOP said...

Very nice info about stats gather in apps database

ORACLE DBA SOP said...

Nice info about stats gather in apps database.