Oracle E-Business Suite (EBS) has a concurrent program called “GATHER_SCHEMA_STATISTICS” which is used to gather stats for the smooth functioning of the CBO (Cost Based Optimizer). It uses FND_STATS, which is basically a wrapper around DBMS_STATS. In this article, I will explain the basic working of the FND_STATS and different options available. I have omitted the explanation on the input parameters which this procedure/program takes and instead have concentrated on the logic which it uses.
The details specified are takes from a 11.5.10 EBS version. But AFSIK, it is equally applicable in 11.5.9 as well.
If options = GATHER
Select list of tables which satisfy the following criteria:
1) dba_tables.iot_type ‘IOT_OVERFLOW’ or iot_type is null
2) Table is not a temporary table
3) It is not an excluded table (determined from fnd_stats_hist and fnd_exclude_table_stats)
It then calls the FND_STATS.GATHER_TABLE_STATS. The functioning of this procedure is described towards the end of this write-up.
If options = GATHER AUTO
1) For db_versions above 8.1 flush db monitoring info from SGA to the dba_tab_monitoring tables. Else use whatever data is available in dba_tab_monitoring tables
2) Select a list of table satisfying the following criteria:
a. dba_tab_modifications.table_owner = upper(schema name)
b. (nvl(dtm.inserts,0)+nvl(dtm.updates,0)+nvl(dtm.deletes,0)) > modpercent*nvl(dt.num_rows,0))/100
Where modpercent (default value = 10) is the threshold level of staleness after which a table should be picked for Stats gathering. This value can be passes from the concurrent program. This is how the wrapper can control the threshold levels as opposed to the DBMS_STATS package where threshold is fixed at 10% .
The logic of the select query is such that there can be some redundant stats gathering – one for the table and another for the partition, when both have stale data. A note says that such scenario will be very rare and the code will be revisited in future if needed.
3) It submits the FND_STATS.GATHER_TABLE_STATS for each table, partition in the list of tables in step 2.
4) GATHER AUTO includes GATHER EMPTY, so stats are gathered on any unanalyzed tables and/or indexes. This is determined from the dba_tab_modifications.last_analyzed = NULL. In case of indexes, it is dba_indexes.last_analyzed = NULL
5) Check if there are any tables in the schema which does not have monitoring enabled. If yes, gather stats for them using 10% and enable monitoring for such tables so that we have data for them in dba_tab_modifications for next time.
For options = LIST AUTO
It writes the list of tables in step 2 to the log file
For options = GATHER EMPTY
It does gather stats for any unanalyzed tables and/or indexes which are not excluded into the log file.
For options = LIST EMPTY
It writes the list of any unanalyzed tables and/or indexes which are not excluded into the log file
If the schema_name = ALL
It will recursively submit FND_STATS.GATHER_SCHEMA_STATS for all schemas satisfying the below conditions:
1) The schema should have a valid entry in the fnd_product_installations table. This means people migrating from 11.5.8, 11.5.9 to 11.5.10, will have to re-register their custom applications in fnd_product_installations for them to be picked by Gather Schema Statistics & schema name = ALL
If schema_name = SYS
It will give error saying – Gathering statistics on the SYS schema using FND_STATS is not allowed. Please use DBMS_STATS package to gather stats on SYS objects.
1) For better performance, tables smaller than small_tab_for_par_thold should be gathered in serial
2) For better stats, tables smaller than small_tab_for_est_thold should be gathered at 100%.
3) If db_versn >= 9.2 then Build up the method_opt if histogram cols are present.
method: = method ||’ ‘|| x.column_name ||’ SIZE ‘||x.hsize — this will give something like FOR ALL COLUMNS SIZE 1
4) If no histogram cols then nullify method, else call it with histogram cols.
5) If version is pre 9.2, use the old method of calling twice.
There are a few other checks which do not have any significance in the current context. The procedure then calls another procedure XXX, which is a Private package that now calls DBMS_STATS.GATHER_TABLE_STATS dynamically depending upon the version of the database. For 8i, dbms_stats is called as before, for higher versions, it is called with the no_invalidate flag.
So, in short, the Gather Schema Statistics concurrent program internally submits DBMS_STATS.GATHER_TABLE_STATS for each eligible table in that particular schema.