解决R12 frmweb 耗cpu 非常高的问题

现象：
    某些进程 frmweb 经常耗cpu 非常高，系统忙的时候能达到 100%，最终能导致form服务死掉！

解决办法：
   设置参数 FORMS_RECORD_GROUP_MAX=10000 FORMS_CATCHTERM=0

参考 metalink Note:745711.1

Applies to:

Oracle Application Object Library - Version: 12.0 to 12.0.5
This problem can occur on any platform.

Symptoms

On Oracle Applications R12 when checking the top processes on the OS level for the middle tier, you find that forms process (frmweb) almost consumes 100% of the CPU.

Cause

The root cause of the issue is that returning rows from LOVs in core forms causes the forms process to grow up into memory depending on the number of rows returned.

When an end user login to forms and start working with LOV within core forms sometimes and according to the search criteria that the user will provide to filter the results in LOV, it may fetch huge numbers of records in which causes the frmweb process to grow very large, and in extreme cases this can even lock up the current process or even the whole machine.

So when executing a LOV query, every row is fetched into memory on the middle tier, the frmweb process can get extremely large, and the larger it gets the more likely it is to start paging.
Eventually it starts consuming excessive CPU just paging the process in and out of memory, which is probably what you can see here in this case as the amount of memory consumed when the LOV records are fetched into memory obviously depends on the amount of data in each record.

This has been mentioned in the following bug:
Bug 6519700 - ESC: CSE: R12SIP: 6513826 FRMWEB RUNAWAY PROCESS CONSUMING 100% CPU-MIDDLE TIER

Solution

To implement the solution, please execute the following steps:

1. Stop all services on the middle tier.

2. Set following forms environment variables:

FORMS_RECORD_GROUP_MAX to 10000 or if that proves too restrictive, increase it to 20000 or 30000.
FORMS_CATCHTERM=0

In order to set the above forms variables so next time autoconfig run does not override those values, do the following steps :

1- For Forms Variable "FORMS_CATCHTERM" the context vairable name is: "s_forms_catchterm" and you can update the context file located in ($INST_TOP/appl/admin/<SID_HOSTNAME.xml>)

2- For other forms variable "FORMS_RECORD_GROUP_MAX" there is no variable defined in Autoconfig for that one and have to customize the autoconfig for the forms variables to set that environment as following:
a- Go to the autoconfig Template folder:
$cd $AD_TOP/admin/template
b- Create new directory named (custom)
$ mkdir custom
c- Make sure that new directory has same file permissions as ($AD_TOP/admin/template)
d- Copy the following autoconfig template to the new custom directory:
$cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/admin/template/custom/APPLSYS_ux.env
e- Edit the file copied file under custom directory and add the following 2 lines at the end of section:

####################################
# Oracle Forms environment variables
####################################

FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX

f- Save and exit from the file.
g- Next time autoconfig run, it will read the custom directory and check for any customizations there.

3. Run Autoconfig on the middle tier and make sure it is completed successfully.

4. Startup all services.

5. Monitor the forms process to see its CPU usage, and you will see that form process usage is reduced and not causing any more CPU consumption up to 100% as before.

6. Migrate the solution as appropriate to other environments.

IMPORTANT NOTE: If you find that setting (FORMS_RECORD_GROUP_MAX=30000) still too restrictive for certain LOV's then Oracle product team needs to consider redesigning the LOV's in their form and at that time raise a new SR with the Oracle Support team so they can raise a new bug with development for that specific LOV to get it redesigned but this may happen ONLY in RARE cases.

References