What is ‘Gather Statistics’?
————————————-
When the data is updated continuously by updating, inserting or deleting of by users (Functional/Technical/End users), it becomes necessary to gather statistics as the performance of database goes down.
Oracle has provided a nice solution for it. This can be done by simply submitting a request from EBS. Oracle E-Business Suite provides concurrent programs that use the package FND_STATS to gather statistics for your applications database objects.
Gather Schema Statistics is one of those concurrent programs.
The following concurrent programs are available for collecting and maintaining statistics:
1) Gather Table Statistics
2) Backup Table Statistics
3) Restore Table Statistics
4) Gather Schema Statistics
5) Gather Column Statistics
6) Purge FND_STATS History Records
What is the difference between DBMS_STATS and FND_STATS
FND_STATS is a PL/SQL wrapper around the DBMS_STATS package, which issues the ANALYZE command. However, we should not use the ANALYZE command or DBMS_STATS package directly. If we do so, it may result in incomplete statistics being generated.
Warning: You should not run DBMS_STATS directly on EBS database.
1) What is ‘Gather Table Statistics’?
————————————————–
The Gather Table Statistics program gathers the table statistics for the specified table.
This program also gathers index statistics for the table by default.
This program can optionally backup the existing statistics in the FND_STATTAB table before gathering new statistics. If the value of backup_flag is BACKUP, then FND_STATS exports the old statistics using dbms_stats.export_table_stats before gathering the new statistics. The exported data is stored in FND_STATTAB. If the value of backup_flag is anything other than BACKUP then the old table statistics are not saved.
Parameters
Owner Name The owner of the table.
Table Name The name of the table.
Estimate Percent The sampling percentage. If left blank, a default value of 10 is used. The valid range is from 0 to 100.
Degree The degree of parallelism to be used for gathering statistics.
If a Degree is not provided, it defaults to the minimum of
parallel_max_servers and cpu_count.
Partition Name The name of the partition.
Backup Flag The backup flag indicates whether to backup statistics. Set
this flag to “BACKUP” to back up your statistics.
Granularity The granularity of statistics to collect (only relevant for
tables that are partitioned). Valid values are:
• DEFAULT – Gather global and partition–level statistics.
• SUBPARTITION – Gather subpartition–level statistics.
• PARTITION – Gather partition–level statistics.
• GLOBAL – Gather global statistics.
• ALL – Gather all (subpartition, partition, and global)
statistics.
History Mode This parameter controls the amount of history records that are created. Valid modes are LASTRUN, FULL and NONE. The default is LASTRUN. For an explanation of the
different modes, please refer to the GATHER_TABLE_STATS Procedure
Invalidate Dependent Cursors This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. This parameter is ignored if you are running a database prior to Oracle 9i Release 2 (9.2.x).
2) What is ‘Backup Table Statistics’?
—————————————————-
This concurrent program backs up the current statistics of the given table into the FND_STATTAB table. This program also backs up the related index and column statistics by default.
An identifier, commonly referred to as STATID, can be associated with the backup up statistics. This STATID allows you to restore a particular version of the statistics using the Restore Table Statistics concurrent program. Statistics for the same object can be backed up with different STATIDs. You can even backup different versions of the statistics for the same object by assigning different STATIDs.
Parameters
Schema Name The name of the schema. The value ALL means all Oracle E-Business Suite schemas.
Table Name The name of the table.
Statistics ID An optional identifier to associate with these statistics within FND_STATTAB. The default STATID is BACKUP.
Partition Name Name of the table partition. If the table is partitioned and if the partition name is NULL, then global and partition table statistics are exported.
3) What is ‘Restore Table Statistics’?
—————————————————
This concurrent program allows you to restore the previously backed up table statistics for a given statistics identifier, commonly referred to as the STATID. All index and column statistics associated with the specified table are restored as well.
Parameters
Schema Name The name of the schema. The value ALL means all Oracle E-Business Suite schemas.
Table Name The name of the table.
Statistics ID An optional identifier to associate with these statistics within FND_STATTAB. The default STATID is BACKUP.
Partition Name Name of the table partition. If the table is partitioned and if the partition name is NULL, then global and partition table statistics are imported.
4) What is ‘Gather Schema Statistics’?
——————————————————–
This is the favourite one among Apps DBAs and developers. Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.
The cost-based optimization (CBO) uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan. Oracle E-Business Suite Release 12 uses cost–based optimization in order to choose the most efficient execution plan for SQL statements. Using this approach, the optimizer determines the most optimal execution plan by costing available access paths and factoring information based on statistics for the schema objects accessed by the SQL statement.
For the query optimizer to produce an optimal execution plan, the statistics in the data dictionary should accurately reflect the volume and data distribution of the tables and indexes. To this end, database statistics should be refreshed periodically. However, that does not necessarily imply that you should gather statistics frequently. Systems that are close to going live typically experience inserts of a large amount of data, as data from legacy systems is migrated. In that scenario, the statistics would probably need to be refreshed quite frequently (for instance, after each major load), as large loads could change the data distribution significantly. Once the system reaches steady state, the frequency of statistics collection at the schema/database level should be reduced to something like once a month. However, statistics on some volatile tables can be gathered as frequently as required.
This procedure (FND_STATS.GATHER_SCHEMA_STATS) gathers statistics for all objects in a schema. This procedure is also available through the concurrent program “Gather Schema Statistics”. If this procedure fails at any time during operation, supplying the request ID for the request that failed can restart it. The request ID can be captured when the program is started from concurrent manager.
Before gathering the statistics, this program can also create a backup of the current statistics, depending on the value of the Backup Flag. If for some reason, the earlier statistics need to be restored, that can be done using the Restore Schema Statistics concurrent program. The STATID used for this backup is NULL.
This program also creates histograms on the columns seeded in the FND_HISTOGRAM_COLS table.
When is it required to be run?
——————————————–
Gathering new statistics is required :
> after the completion of an upgrade or installation
> upon loading large amounts of data into the database
> Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.
> Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.
If you run into a performance issue, ensure statistics have been recently gathered; one may reference gathering schema statistics which would be the recommended action to take.
How to run Gather Schema Statistics concurrent program:
———————————————————————————
1. Log on to Oracle Applications with
Responsibility = System Administrator
2. Submit Request Window
Navigate to: Concurrent > Requests
3. Query for the Gather Schema Statistics
4. Enter the appropriate parameters ( see below for explanation). This can be run for specific schemas by specifying the schema name or entering ‘ALL’ to gather statistics for every schema in the database
5. Submit the Gather Schema Statistics program
Parameters:
Schema Name: Schema for which statistics are to be gathered. Specify ALL for all Oracle Applications schemas (all schemas that have an entry in the FND_PRODUCT_INSTALLATIONS table).
Percent: The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100
Percentage of rows to estimate. If left empty it will default to 10%. The valid range is 0-99. A higher percentage will be more accurate, but take longer to run. If the object(s) that you are gathering statistics for do not change often or the object(s) has data entered that is very similar you may choose a lower number. However, if the data changes frequently a larger number entered for this parameter would be recommended to provide a more accurate representation of your data.
Degree: The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum ofparallel_max_servers and cpu_count.
Backup Flag: If NOBACKUP is used, then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.
Set this flag to BACKUP if you wish to back up the current statistics into the FND_STATTAB table. If the value is ‘BACKUP’ then it does an export_table_stats prior to gathering the statistics.
Restart Request ID: In the case where the Gather Schema Statistics run fails due to whatever reasons, the concurrent request can be re-submitted and it will pick up where the failed run left off, if you provide the concurrent request_id of the failed run.
History Mode: This parameter controls the amount of history records that are created. The history records, stored in FND_STATS_HIST can be queried to find out when stats were gathered on a particular object and the amount of time it took to gather statistics on that object.
a) Last Run – History records for each object are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the object. This is the default behavior
b) Full – This mode does not overwrite any history information. History records are created for each run and are identified by the Request ID. If a Request ID is not provided, one is generated automatically. If this mode is used, the “Purge FND_STATS History Records ” concurrent program should be run periodically to purge the FND_STATS_HIST table.
c) None – This mode does not generate any history information. If this mode is used, the run cannot be restarted.
Gather Options:
a) GATHER: All tables and indexes of the schema <schemaname> are selected for stats gathering. This is the default
b) GATHER AUTO, this option allows FND_STATS to determine automatically the tables for which statistics should be gathered, based on the change threshold.
GATHER AUTO uses a database feature called Table Monitoring, which needs to be enabled for all the tables. A procedure called ENABLE_SCHEMA_MONITORING has been provided to enable monitoring on all tables for a given schema or all Applications schemas.
Automatic Optimizer Statistics Gathering liberates DBAs from the arduous tasks of determining:
- which objects need statistics
- the correct sampling percentages for objects
- histogram bucket sizes based on the workload profile
- degree of parallelism for statistics gathering.
ENABLE_SCHEMA_MONITORING Procedure
This procedure should be used for enabling the Monitoring option for all tables in the specified schema. Monitoring option should be enabled before using the GATHER AUTO or LIST AUTO option of GATHER_SCHEMA_STATS. If the value of the schemaname argument is ALL, then the Monitoring option is enabled for all tables that belong to all schemas registered in Oracle E-Business Suite.
Syntax
FND_STATS.ENABLE_SCHEMA_MONITORING (
schemaname VARCHAR2 DEFAULT ‘ALL’);
DISABLE_SCHEMA_MONITORING Procedure
This procedure should be used for disabling the Monitoring option for all tables in the specified schema. If the value of the schemaname argument is ALL, then the Monitoring option is disabled for all tables that belong to all schemas registered in Oracle
E-Business Suite.
Syntax
FND_STATS.DISABLE_SCHEMA_MONITORING (
schemaname VARCHAR2 DEFAULT ‘ALL’);
c) GATHER EMPTY : Statistics are gathered only for tables and indexes that are missing statistics.
d) LIST AUTO : This option does not gather statistics. It only provides a listing of all the tables that will be selected for statistic gathering, if the GATHER AUTO option is used.
e) LIST EMPTY : This option does not gather statistics. It only provides a listing of all the tables that will be selected for statistics gathering, if the GATHER EMPTY option is used.
Modifications Threshold: Applicable only to GATHER AUTO and LIST AUTO Options. This parameter specifies the percentage of modifications (with respect to the total rows) that have to take place on a table before it can be picked up for AUTO statistics gathering.
Estimate Percentage / Modification threshold defines the percentage which should be used to initiate gather stats for those objects which have actually changed beyond the threshold.
The default is 10% (i.e. meaning any table which has changed via DML more than 10%, stats will be collected, otherwise it will be skipped).
There are no estimated percentages for Gather Schema Statistics with the fact that when the % increases for a table, the accuracy for the results increase and vice versa, so, this can be done only by monitoring and experiencing different Scenarios and observing the results to reach the optimum Percentage for each environment.
Invalidate Dependent Cursors: This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. By default, dependent cursors are invalidated.
What can be the optimum frequency for scheduling GATHER SCHEMA STATISTICS?
The frequency of program is based upon your specific usage of Oracle Applications and is different for every customer.
It will be necessary to try different schedules over time and monitor performance to determine the most effective schedule.
Running gather schema statistics daily is not a recommended practice. The best will be to schedule this program to run with an estimate of 10% once a week and running the concurrent program once a month with 40%.
Advantage of using higher percentage is that it will be more accurate. The disadvantage is it will take more time. The amount of time varies from instance to instance and depends on the amount of data change happening over the time.
As per Oracle docs, If the object(s) that we are gathering statistics do not change often or the object(s) has data entered that is very similar you we can choose a lower number. However, if the data changes frequently a larger number entered for this parameter would be recommended to provide a more accurate representation of data.
We can calculate the time it takes to run for 10% and 40% by having a trial run and monitoring the concurrent program.
5) What is ‘Gather Column Statistics’?
—————————————————-
This concurrent program should be used for gathering the Column Statistics, i.e. creating a histogram on a given column.
The procedure takes a backup into the FND_STATTAB table before gathering the statistics.
Parameters
—————–
Table Owner The owner of the table.
Table Name The name of the table.
Column Name The name of the column.
Estimate Percent The sampling percentage. If left blank, a default value of 10 is used. The valid range is from 0 to 100.
Parallel Degree The degree of parallelism to be used for gathering statistics.
If a Degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.
Bucket Size The number histogram buckets.
Backup Flag The backup flag indicates whether to backup statistics. Set this flag to BACKUP if you wish to back up the current
column statistics into the FND_STATTAB table. If left blank, it defaults to NOBACKUP.
6) What is ‘Purge FND_STATS History Records’?
———————————————————————
This program can be run to purge the history records from the FND_STATS_HIST table. This program should be scheduled to run periodically if statistics are being gathered with History Mode as FULL. You do not need to run this program if you gather statistics with History Mode as NONE or the default – LASTRUN.
Parameters
Purge Mode The Purge Mode can take one of the two values: DATE or REQUEST. If the mode chosen is DATE, history records are purged based on the date range, otherwise, if it is
REQUEST, records are purged based on the Request ID.
From Value Start Date or Request ID
To Value End Date or Request ID.
Why sometimes gather stats runs for longer time than normal.
There are many possible causes but the most common are:
a) Database/application process or processes are running which are updating the database. When GSS runs it will invalidate all of the stats on the object it is analyzing. If a process then trying to update or use that table it will start doing full table scan which will affect the overall database performance, which in turn impact GSS.
b) Large amount of data has been added to the database. The more records you have the longer the GSS will take to complete the process.
c) Recollecting CBO stats on tables that have not changed. If a table has 100 million rows then gathering stats on that table will take a long time, however if no or little changes are made then there is no need to delete all of the old stats and regather them (which is what you are doing)
d) Gathering statistics invalidates cursors which can hamper performance.( Unless you use the ‘No Invalidate’ option)
IMPORTANT TABLES/VIEWS RELATED TO GATHER SCHEMA STATISTICS
1) FND_STATS_HIST
To record the time taken for gathering the statistics for the different types of objects.
2) FND_HISTOGRAM_COLS
Gather Schema stats create the histogram for the specified columns in the tables.
IMPORTANT SCRIPTS RELATED TO GATHER SCHEMA STATISTICS
1) If the custom schema is not registered, it will not show up in the LOV for schema selection for the mentioned concurrent programs.
You can run the following statement to see which schemas are currently registered with the Ebusiness Suite:
select distinct(upper(oracle_username)) sname
from fnd_oracle_userid a, fnd_product_installations b
where a.oracle_id = b.oracle_id order by sname;
How to verify if the current gathered statistics are correct?
We use the Verify Stats report to determine whether the current statistics are accurate.
This report is a utility provided with FND_STATS, and can be run as follows:
SQL> set server output on
SQL> set long 10000
SQL> exec fnd_stats.verify_stats(‘schema’, ‘object_name’);
GATHER SCHEMA STATISTICS NOT RUNNING FOR CUSTOM MODULES. WHY?
When we submit Gather Schema Stats with Parameter ALL, concurrent request will complete successfully but custom schemas may not get analyzed.
Script to check if custom schema is analyzed :
Sql > select count(table_name) from dba_tables where last_analyzed is not null and owner= <custom_schema_name>;
Gather Schema Statistics program gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.
Reason:
Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables
ie FND_ORACLE_USERID and FND_APPLICATIONS_TL
However , when Gather schema statistics is submitted it uses the below query to get schema information
Sql > select distinct upper(oracle_username) sname
from fnd_oracle_userid a,
fnd_product_installations b
where a.oracle_id = b.oracle_id
order by sname;
Note : When custom schemas are created the entry is not made in FND_PRODUCT_INSTALLATIONS and hence it is not picked up in the above query.
Solution :
Go to the Responsibility called Alert Manager and Navigate to the form -> Installations under Systems Menu.
Define custom application in this form. Go to the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.
Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schemas as well.
Points to Remember for Gather Statistics:
- Make sure Gather Shema Stats is running on periodical basis. It can be checked with bde_last_analyzed.sql (Note:163208.1) which verifies stats by schema and index.
- Do not gather statistics excessively on entire schemas or the entire database such as nightly or weekly.
- Do not gather statistics on permanent objects during peak intervals.
- Gathering statistics invalidates cursors .Unless you use the ‘No Invalidate’ option.
- Gathering statistics requires dictionary and object level locks.
- The option ‘GATHER_AUTO’ can be used, to gather statistics only on objects that have changes above the specified ‘Modification Threshold’ (percentage of DML compared to the number of rows of the table).
- Plans are not likely to change if the data distribution has not changed.
- Use only FND_STATS or the Gather Schema and Gather Table Statistics Concurrent Programs.
- Do NOT USE the analyze or dbms_stats command directly. It is not supported, and results in sub-optimal plans.
- To execute the corresponding FND_STATS procedures from SQL*Plus to gather CBO stats for one or all schemas, or for a particular table, use the following examples:[stextbox id=”grey”]# sqlplus apps/<apps_pwd>
SQL> exec fnd_stats.gather_schema_statistics(‘MRP’); <- One schema
SQL> exec fnd_stats.gather_schema_statistics(‘ALL’); <- All schemas
SQL> exec fnd_stats.gather_table_stats(‘MRP’,’MRP_FORECAST_DATES’); <- One table[/stextbox]
IMPORTANT NOTE IDS:
================================================================
Note ID 1184276.1: 11i and R12: Are there any recommended/ideal Percentages for running Gather Schema Statistics across e-Business Suite?
Note ID 113573.1: OM-PERF: Performance Tuning – FAQ
Note ID 176852.1: Integrating Custom Applications with Oracle Applications
Note ID: 168136.1: How Often Should Gather Schema Statistics Program be Run?
Note ID 1497109.1: Why Does Gather Schema Statistics Take So Long To Complete?
Note ID 419728.1: Concurrent Processing – How To Gather Statistics On Oracle Applications Release 11i and/or Release 12 – Concurrent Process,Temp Tables, Manually
Note ID 394555.1: FND_STATS concurrent program does not PICK UP seemingly properly REGISTERED CUSTOM SCHEMAS
Note ID 228186.1: Differences between GATHER STALE and GATHER AUTO
Note ID 331017.1: Gather Schema Statistics program hangs or fails with ORA-54 errors
Note ID 556466.1: Definition of Parameters Used in Gather Schema Statistics Program
Note ID 156968.1: coe_stats.sql – Automates CBO Stats Gathering using FND_STATS and Table sizes
Note ID 396009.1: Database Initialization Parameters for Oracle Applications Release 12
Note ID 1056968.1: How to Disable Automatic Statistics Collection in 11g
Note ID 368252.1: EBPERF FAQ – Collecting Statistics with Oracle Apps 11i and R12
Oracle® E-Business Suite System Administrator’s Guide – Configuration Release 12.1 (Part No. E12893-04)
===================================================================
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
- Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021
Nice. keen observation. Thank you. !
This is an Excellent post Brijesh, fully informative on GSS, very helpful. 🙂
Really Very nice post with detailed explanation !!
Excellent effort