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.
http://expertoracle.com/2013/02/01/gather-statistics-in-r12-and-11i/
