FRM Performance issue on R12

Issue :

Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications R12

Issue Confirmation :

From " top " command output, FRMWEB shows 100% of CPU usages on all FE nodes.

Node 1

Tasks: 252 total, 4 running, 248 sleeping, 0 stopped, 0 zombie
Cpu(s): 39.1% us, 0.2% sy, 0.0% ni, 60.5% id, 0.2% wa, 0.0% hi, 0.0% si
Mem: 33277600k total, 7763548k used, 25514052k free, 120288k buffers
Swap: 6731224k total, 0k used, 6731224k free, 857572k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
7954 applmgr 25 0 100 7:38.16 0.3 130m 84m 20m R frmweb
23357 applmgr 25 0 100 254:40.80 0.2 121m 75m 19m R frmweb
29362 applmgr 25 0 100 241:52.23 0.2 109m 62m 18m R frmweb
19146 applmgr 15 0 5 0:08.18 0.2 99.8m 55m 16m S frmweb
10994 applmgr 16 0 3 8:26.54 3.8 1918m 1.2g 7036 S java
10224 applmgr 16 0 2 2:02.79 0.3 133m 86m 20m S frmweb
26093 applmgr 17 0 1 0:00.04 0.0 56472 9264 6068 S frmweb
26068 applmgr 16 0 1 0:00.07 0.0 2644 1108 760 R top
10993 applmgr 17 0 0 11:24.62 3.8 1901m 1.2g 10m S java
14619 applmgr 15 0 0 0:05.47 0.1 86212 41m 13m S frmweb


Node 2

Tasks: 242 total, 3 running, 238 sleeping, 0 stopped, 1 zombie
Cpu(s): 15.0% us, 0.2% sy, 0.0% ni, 84.8% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 33277600k total, 7983180k used, 25294420k free, 111600k buffers
Swap: 6731224k total, 0k used, 6731224k free, 878220k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
1677 applmgr 25 0 100 238:31.17 0.1 85976 38m 14m R frmweb
29169 applmgr 15 0 13 0:04.91 0.2 107m 62m 17m S frmweb
31279 applmgr 15 0 2 1:14.63 0.3 147m 98m 22m S frmweb
12726 applmgr 15 0 2 0:11.64 0.2 99.1m 54m 16m S frmweb
30388 applmgr 17 0 1 0:00.04 0.0 56472 9264 6068 S frmweb


Node 3

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
16225 applmgr 25 0 101 250:24.90 0.2 120m 74m 19m R frmweb
24725 applmgr 25 0 101 141:44.18 0.2 100m 55m 17m R frmweb
8339 applmgr 25 0 99 375:46.78 0.2 114m 68m 19m R frmweb
26492 applmgr 25 0 99 167:46.88 0.2 119m 72m 19m R frmweb
32477 applmgr 25 0 99 151:14.55 0.2 120m 74m 19m R frmweb
1 root 16 0 0 0:02.42 0.0 2652 508 432 S init

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.

Solution :

1. The cleaner and consistent way is to set "s_forms_catchterm"=0 in the CONTEXT FILE and run autoconfig.

2. If you can't afford autoconfig now, then we need to update the following files with FORMS_CATCHTERM=0

$APPL_TOP/$CONTEXT_NAME.env
$ORA_CONFIG_HOME/10.1.2forms/server/socket.env
$ORA_CONFIG_HOME/10.1.2forms/server/default.env

Reference :

Metalink Doc : 745711.1 - Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications R12