Gather statistics for Oracle Applications 11i

Following the last post Monitoring DML operations, In this post I'm going to talk about Gathering Statistics in Oracle Applications 11i system. All of us gathering statistics in our databases, the question is whether it is the best way to do it? We are struggling with a lot of questions regarding for which schema needs to run? How much percent sampling? In what frequency it considered enough? Etc... So I will try to make some light in the dark... The topics in this post will be: • The classic way to gather statistics - by the "Gather Schema Statistics" concurrent with Gather Options parameter set to "GATHER". • Using "Monitoring" with Gather Schema Statistics to enhance Gather Statistics run time and efficiency. You can read more about monitoring in my previous post Monitoring DML operations 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 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. As you can see, it’s not necessary to enable monitoring before gather statistics with Gather Auto option, since it does it anyway. (But I did…:-)) 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. Try it! For any questions or if you have additional information you are welcome to leave a comment.