oacore / forms stdout eating up your CPU and I/O? It’s a bug!!

We’re going live ina few weeks and and my performance tests showed me a distrubing trend. The oacore processes would consume about 40% CPU between them and were churning out 8 GB’s of stdout files for each managed servers in a few hours.

Opening an SR gave me a method to create a filter that would filterout these message but I had to use the EBS scritps to start and stop these managed servers then. And I liked the WL control console 🙂

After a bit of further digging, I chanced upon Oracle Doc ID 1981206.1, which points to bug 18345006.

JBO DEBUG LOGGING ON WITHOUT -D FLAG, NOTICE LEVEL AND PROD MODE=TRUE

After applying the patch jbo messages are no longer being thrown into teh std out, and the CPU and I/O are very normal now

Advertisements

Gather Schema Statistics on EBS 11.5.8 with Gather Auto

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.

Conclusion:

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.

Gather Schema Stats in Oracle EBS 11.5.10

Oracle E-Business Suite (EBS) has a concurrent program called “GATHER_SCHEMA_STATISTICS” which is used to gather stats for the smooth functioning of the CBO (Cost Based Optimizer). It uses FND_STATS, which is basically a wrapper around DBMS_STATS. In this article, I will explain the basic working of the FND_STATS and different options available. I have omitted the explanation on the input parameters which this procedure/program takes and instead have concentrated on the logic which it uses.

The details specified are takes from a 11.5.10 EBS version. But AFSIK, it is equally applicable in 11.5.9 as well.

_FND_STATS.GATHER_SCHEMA_STATS_

If options = GATHER

Select list of tables which satisfy the following criteria:

1) dba_tables.iot_type ‘IOT_OVERFLOW’ or iot_type is null

2) Table is not a temporary table

3) It is not an excluded table (determined from fnd_stats_hist and fnd_exclude_table_stats)

It then calls the FND_STATS.GATHER_TABLE_STATS. The functioning of this procedure is described towards the end of this write-up.

If options = GATHER AUTO

1) For db_versions above 8.1 flush db monitoring info from SGA to the dba_tab_monitoring tables. Else use whatever data is available in dba_tab_monitoring tables

2) Select a list of table satisfying the following criteria:

a. dba_tab_modifications.table_owner = upper(schema name)

b. (nvl(dtm.inserts,0)+nvl(dtm.updates,0)+nvl(dtm.deletes,0)) > modpercent*nvl(dt.num_rows,0))/100

Where modpercent (default value = 10) is the threshold level of staleness after which a table should be picked for Stats gathering. This value can be passes from the concurrent program. This is how the wrapper can control the threshold levels as opposed to the DBMS_STATS package where threshold is fixed at 10% .

The logic of the select query is such that there can be some redundant stats gathering – one for the table and another for the partition, when both have stale data. A note says that such scenario will be very rare and the code will be revisited in future if needed.

3) It submits the FND_STATS.GATHER_TABLE_STATS for each table, partition in the list of tables in step 2.

4) GATHER AUTO includes GATHER EMPTY, so stats are gathered on any unanalyzed tables and/or indexes. This is determined from the dba_tab_modifications.last_analyzed = NULL. In case of indexes, it is dba_indexes.last_analyzed = NULL

5) Check if there are any tables in the schema which does not have monitoring enabled. If yes, gather stats for them using 10% and enable monitoring for such tables so that we have data for them in dba_tab_modifications for next time.

For options = LIST AUTO

It writes the list of tables in step 2 to the log file

For options = GATHER EMPTY

It does gather stats for any unanalyzed tables and/or indexes which are not excluded into the log file.

For options = LIST EMPTY

It writes the list of any unanalyzed tables and/or indexes which are not excluded into the log file

If the schema_name = ALL

It will recursively submit FND_STATS.GATHER_SCHEMA_STATS for all schemas satisfying the below conditions:

1) The schema should have a valid entry in the fnd_product_installations table. This means people migrating from 11.5.8, 11.5.9 to 11.5.10, will have to re-register their custom applications in fnd_product_installations for them to be picked by Gather Schema Statistics & schema name = ALL

If schema_name = SYS

It will give error saying – Gathering statistics on the SYS schema using FND_STATS is not allowed. Please use DBMS_STATS package to gather stats on SYS objects.

_FND_STATS.GATHER_TABLE_STATS_

1) For better performance, tables smaller than small_tab_for_par_thold should be gathered in serial

2) For better stats, tables smaller than small_tab_for_est_thold should be gathered at 100%.

3) If db_versn >= 9.2 then Build up the method_opt if histogram cols are present.

method: = method ||’ ‘|| x.column_name ||’ SIZE ‘||x.hsize — this will give something like FOR ALL COLUMNS SIZE 1

4) If no histogram cols then nullify method, else call it with histogram cols.

5) If version is pre 9.2, use the old method of calling twice.

There are a few other checks which do not have any significance in the current context. The procedure then calls another procedure XXX, which is a Private package that now calls DBMS_STATS.GATHER_TABLE_STATS dynamically depending upon the version of the database. For 8i, dbms_stats is called as before, for higher versions, it is called with the no_invalidate flag.

So, in short, the Gather Schema Statistics concurrent program internally submits DBMS_STATS.GATHER_TABLE_STATS for each eligible table in that particular schema.