Gather Schema Statistics

The Gather Schema Statistics concurrent will do the following:

• Flush database monitoring info from SGA to dba_tab_modifications view
• Gather statistics for tables that cumulative modifications compared to the dba_tables.num_rows are more than Modification Threshold percents.
• Gather statistics for table that never analyzed (last_analyzed in NULL).
• Gather statistics for tables which does not have monitoring enabled and afterwards enable monitoring for them so we have data in dba_tab_modifications next time we Gather Statistics.
  

The Gather Schema Statistics concurrent, based on fnd_stats package, gather statistics for the required schema(s) with some additional parameters: 

• Schema Name - the schema in which statistics should gathered. You can specify 'ALL' for gather statistics for all schemas registered in FND_PRODUCT_INSTALLATIONS table.
• Estimate Percent - The sampling percents. Default value is 10.
• Degree of parallelism - The degree of parallelism for gathering statistics. Default value is MIN (parallel_max_servers, cpu_count). (database init parameters)
• Backup Flag - Indicate whether to backup last statistics (current situation). BACKUP/NOBACKUP - indicate to save/not save current statistics.
• Restart Request Id - if Gather Schema Statistics concurrent failed, you can rerun it with this parameter set to the failed request_id. The concurrent will continue from where the failed request stopped.
• History Mode - indicate how much history will save. History includes for each object when statistics gather for it, when it ends and more info'. Parameter options are: LAST_RUN - will save history only for the last time for each object. FULL - will save additional history each run. None - don't save history.
• Gather Options - indicates for which objects statistics will gather. Parameter options are: GATHER (the classic & default) - gather stat' for all objects in the appropriate schema. GATHER_AUTO - This option considers how much DML operations executed on each object to decide whether gather statistics or not, I will explain later in details. GATHER_EMPTY - will gather stat only for tables/indexes with no statistics. LIST_AUTO - provide a list of objects for gather statistics if GATHER_AUTO is used. LIST_EMPTY - provide a list of objects for gather statistics if GATHER_EMPTY is used.
• Modifications Threshold - relevant only when using GATHER_AUTO/LIST_AUTO in the previous parameter. This parameter specifies the percentage of modifications on objects that must execute before gathering statistics. I will explain later in details.
• Invalidate Dependent - indicate whether to invalidate cursor for the analyzed objects. The default is 'Y'.

The Gather Schema Statistics concurrent should schedule to run periodically (usually once a week/2 weeks).
With gather options parameter set to GATHER and schema set to ALL, it will take a very long time to run since it gather statistics for all objects in database (for schemas registered in FND_PRODUCT_INSTALLATIONS table).


Using "Monitoring" with Gather Schema Statistics

To reduce Gather Schema Statistics concurrent run time, we can set the Gather Options parameter to "GATHER AUTO", it indicates to gather stats only for tables that have [Modifications Threshold] % changes since last analyze.

First we should enable monitoring for the relevant schemas.
We can do it by executing the following:
exec fnd_stats.ENABLE_SCHEMA_MONITORING (SCHEMA_NAME);


You can specify ‘ALL’ as parameter to enable monitoring for all tables in registered schemas.


The following concurrent programs have been defined to gather and maintain various statistics using the FND_STATS package.

Concurrent Programs

• Analyze All Index Columns
• Gather Table Statistics
• Backup Table Statistics
• Restore Table Statistics
• Gather Schema Statistics

Note:  When invoked using the seeded Concurrent Programs, only use the 'Gather Schema Statistics' or the 'Gather Table Statistics'.  

Additional Information: The Optimizer, Oracle8i Concepts

When using the 'Gathering Schema Statistics' concurrent program, it is recommended that you only pass the schema name parameter and let the other parameters default to their default values.  The schema name passed can be a specific schema name (e.g. GL) or 'ALL' if you prefer to gather CBO stats for Apps modules.  This choice uses the “estimate” method with a sample size of 10% (default).  

When using the 'Gather Table Statistics' concurrent program, only pass the owner of the table (schema name) and the table name.  Let all other parameters default automatically, except when the table is a partitioned table.  

To manually execute FND_STATS from SQL*Plus to gather CBO stats for one or all schemas, or for a particular table, use the following syntax:

  SQL> exec fnd_stats.gather_schema_statistics('GL');          <- One schema
  SQL> exec fnd_stats.gather_schema_statistics('ALL');         <- All schemas
  SQL> exec fnd_stats.gather_table_stats('GL','GL_JE_LINES');  <- One table

The DBMS_STATS package contains all the procedures required to maintain the optimizer statistics of any schema.

you may gather schema statistics once per week with a specific estimate_percent value and gather schema index statistics every night, for this purpose the following simple PL/SQL may be used . I recommend if you create jobs and schedule them as appropriate.

-- Gathering Schema Statistics
execute dbms_stats.gather_schema_stats('SCHEMA1', estimate_percent=>39, cascade=>TRUE);
execute dbms_stats.gather_schema_stats('SCHEMA2', estimate_percent=>45, cascade=>TRUE);
-- Gathering Index Statistics
set serveroutput on
begin
for ind in
(select object_name
from user_objects
where object_type='INDEX'
and object_name like 'YOUR_INDEX_PATTERN_HERE'
)
loop
dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||'.....');
dbms_stats.gather_index_stats('SHEMA1', ind.object_name , estimate_percent=>100);
dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||' is Complete!');
end loop;
end;
/



In fact, gather schema statistics become significantly more efficient, less run time, since we gather statistics for heavy using tables only.
This enables us to run Gather statistics more frequently for less time.

---

Important Documents:

• What is the Gather Statistic Program or Gather Schema Statistics? (Doc ID 155766.1) 
• How Often Should Gather Schema Statistics Program be Run? (Doc ID 168136.1) 
• Gather Schema Statistics with LASTRUN Option does not Clean FND_STATS_HIST Table (Doc ID 745442.1) 
• Does Gather Schema Statistics collect statistics for indexes? (Doc ID 170647.1) 
• Definition of Parameters Used in Gather Schema Statistics Program (Doc ID 556466.1) 
• Gathering Schema or Database Statistics Automatically in 8i and 9i - Examples (Doc ID 237901.1) 
• Gather Schema Statistics Fails for Some Tables Percentages Not Displaying Properly (Doc ID 149603.1) 
• Gather Schema Statistics (FNDGSCST) Hangs with DR$ Tables With Monitoring (Doc ID 435482.1) 
• Gather Schema Statistics Hangs & Returns ORA-600: Internal Error, Arguments: [25012] (Doc ID 454205.1) 
• Gather schema statistics occupies huge TEMP space without releasing it (Doc ID 731674.1) 
• Gather Schema Statistics Completed Err ORA-20000 Index in unusable state (Doc ID 373488.1) 
• How To Gather Table Statistics For Custom Schema Tables (Doc ID 389944.1) 
• Gather Schema Statistics Program Fails When Scheduled (Doc ID 1050582.1) 
• Running Gather Optimizer Statistics On Schema Generates Error (Doc ID 854527.1) 
• Gather Schema Statistics Fails With Errors ORA-04063 and ORA-04068 (Doc ID 215897.1)