Recently a client of ours who’s on EBS 11.5.8 and 9i came up with a request – to run Gather Schema Statistics with the Gather Auto Option “without setting statistics_level as typical”. Yes, you heard me right.
I met with many road blocks in this course and now finally, I have overcome all these. Below is a very brief pointers on how I went about it. The reason I am posting this is that I see may hits which come from Google with the Search Item EBS 11.5.8 Gather Stats in my hit counter. So there are people out there who are looking for similar solutions.
I analyzed the FND_STATS package which is called by the program “Gather Schema Statistics (GSS)”. Upon this, I found that FND_STATS is actually a wrapper for DBMS_STATS package. DBMS_STATS package is the db package which enables statistics gathering. FND_STATS adds extra validation and a few important functionalities to the DBMS_STATS. As per Oracle; we require the parameter statistics_level to be set to “typical” in order to use “GATHER AUTO”.
Analyzing the FND_STATS showed that whenever Gather Schema Statistics is run with GATHER AUTO parameter, the package actually gets a list of tables which do not have MONITORING enabled and then it proceeds to gather stats for all tables in this list @ an estimate percent 10 and option as GATHER. It will then execute and ALTER SESSION statement which will enable monitoring for this table. This means from that point of time, data will be gathered in all_tab_modifications table on the INSERT/UPDATE/DELETE action on the said table.
What this means for us: We can run GSS with GATHER AUTO option and it will in the first run do two things:
a. Gather stats at 10% for ALL tables satisfying certain criteria.
b. Enable MONITORING for all tables in above step.
I tried running the normal GSS with GATHER AUTO and the some request completed while some errored out. Upon analyzing the errored SCHEMAS, it was found that they errored out because the session could not get a LOCK with NOWAIT clause – which meant that certain tables in those schemas were being accessed & locked by another process when this happened.
I went back to FND_STATS wrapper and deduced the logic which it used to get the list of tables and enable MONITORING. I then wrote a script which would do this for me schema by schema. I was able to update all tables except those in APPLSYS schema. This is because once the EBS Application is up, it will continuously access certain tables like FND_CONCURRENT_PROCESSES, FND_CONCURRENT_QUEUES, FND_CONCURRENT_REQUESTS etc. I ran this script with the application (incl. CCM) down and all tables was updated.
I ran the request set with LIST AUTO parameter (this will list out all the tables for which stats would be gathered if we use GATHER AUTO). To my surprise, it did not list a single table whereas ideally it should have listed a number of tables. Then, a search on metalink pointed me to a Bug which caused oracle to ignore the default value of 10 for the parameter “modifications threshold”. This parameter is responsible for determining the cut off %change in a table after which stats would be gathered for it. There are not patches as of now; however a workaround exists and I implemented it. (you just have to manually enter the value instead of leaving it blank for Oracle to default it ; neat ha?) I ran the GSS request set with LIST AUTO again and it picked up many tables. Then I submitted the GSS with GATHER AUTO option. The program completed successfully.
Here I have run GSS with GATHER AUTO at a default modifications threshold of 10%. In different environments we might need to have different values for this parameter for a set of tables which are very dynamic. This list can be deduced by monitoring the performance of the Application over a period of time.
This post is supposed to be a pointer for people out there who are trying to achieve something similar. Also also a note: FND_STATS can be can be customized to reflect a customer’s own needs. Though this is not supported by Oracle, no harm in doing this in an already de-sup version. Let me know if any of you are interested.