R12:Web ADI Performance and Out of Memory issues seen when uploading large amounts of data

ISSUE:

When using BNE Profile BNE: Enable OOXML Standard being set to Yes, Web ADI Performance for Create Document and Upload functionality improves as the performance does not depend on the Client PC desktop capacity, when being set. The performance for the Web ADI Upload and Create Document functionality is better when using the Middle Tier server capacity.

With this being said, if there are issues seen with the Web ADI Uploads related to Performance or Create Document taking time to build the Integrator, then you will need to look into Fine Tuning the Middle Tier’s resources. If the Middle Tier is not sized correctly to handle the added functionality of the BNE Profile BNE: Enable OOXML Standard being set to Yes you can experience overall Web Server/Middle Tier Performance degradation when Server is under load.

This is mainly seen when uploading large amounts of Data in the Web ADI Integrator containing many Rows and even Columns and can occur during times of month end.

CHANGES:

Setting Profile BNE: Enable OOXML Standard = Yes

If the profile option, BNE: Enable OOXML Standard, would be set No, testing the same amount of rows and data would not be able to upload, the desktop Excel will probably hang. Then, the cause of the problem is not that BNE: Enable OOXML Standard is set to Yes. The cause would be related to the Middle Tiers needing to be fine tuned.

CAUSE:

Web ADI was never intended or designed to handle such a large upload. Web ADI was never envisioned to handle more then 20k, 30k or even 40k rows to be uploaded at a single time. However it can be done. In many cases Customer have to Benchmark test the Upload Functionality to see where there limitations are meet.

Test your Integrator with smaller amounts of rows. Try to keep this at or around 10k for testing purposes. Then slowing increase the number of rows added to the Integrator and test.
Increase the Rows until the thresh-hold is hit when the issues mentioned in the SR are hit.

There is no Benchmark testing for Uploading from Oracle as there are too many variables and each customer using this functionality in different methods to be able to provide accurate Benchmarks on the Upload Functionality.

If profile Option “BNE_OOXML_ENABLED” is Yes, larger memory is needed as explained below
https://docs.oracle.com/cd/E26401_01/doc.122/e22007/T443182T443186.htm
Line Region Properties

If your site is using the OOXML format, then the document creation and upload processing is performed on the server. In this case the processing is significantly faster than if you do not use OOXML, but requires a larger server heap memory. You should review the server heap memory setting to ensure it is appropriate for this configuration.

Beginning in Release 12.2.5 or ATG and Framework patching delivering bneprof.ldt 120.14.12020000.5 or higher, you can choose to create Web ADI templates according to the Office Open XML (OOXML) standard, an XML format developed by Microsoft to represent spreadsheets and other types of documents. When using the OOXML format, Oracle Web ADI generates the spreadsheet entirely on the Oracle E-Business Suite server, and the completed spreadsheet is then downloaded to the desktop.

SOLUTION:

Debug:

========

The Required Debug for Support in order to assist in determining if JVM Middle Tier Performance related or hit limitation of the number of Rows and Columns being updated for current sizing of Middle Tier.

1. Please provide the BNE Log File for this issue.

NOTE: Setting BNE to TRACE mode may help identifying the integrator and the operation (upload/download) BUT will degrade even more the performance. It is important quickly enable the BNE Trace reproduce the issue and immediately return BNE Log Level to Error.

1a. Clear existing BNE.log so that there is no old/unrelated data in it.

1b. From the EBS apps (System Administrator > Profile > System) Set the following System Profile Options to enable Debug:
Remember that the BNE Server log profiles only have effect at SITE LEVEL.

* BNE Server Log Filename : bne_perf_ooxml.log
* BNE Server Log Level : TRACE (This must be set at the Site level, it will not work at the other levels)
* BNE Server Log Path : free to choose, eg. in the $LOG_HOME/logs (use the full path, make sure that the applmgr has write permissions)

1c. Restart the Apache Server for changes to take effect.

1d. Have User follow the Steps to reproduce the problem.
1e. Upload the log file defined by profile BNE Server Log Filename for review .
1f. Afterwards, set the BNE Server Log Level to ERROR to avoid excessive growth of the log file. In production, this Profile should be set to ERROR unless debugging or diagnosing an issue.

2. At the same time of enabling BNE Logging, enable *JDBC logging* from the console for the WLS managed server.
2a. Provide the exact date and time the outage occurs with the external Application tier node.
Example:
– Login to the console
– Environment > Server > oacore_serverX (ie: oacore_server1)
– Click on Debug tab
– Expand weblogic
– Select jdbc
– Click Enable
– Activate Changes

2b. Enable *Servlet logging* from the console for managed server.

Example
– Login to the console
– Environment > Server > oacore_serverX (ie: oacore_server1)
– Click on Debug tab
– Expand weblogic
– Select servlet
– Click Enable
– Activate Changes

2c. Stop all application services.

2d. Backup/move the existing FMW/OHS log files for managed server X and
clean the logs (ie: move all current logging to a different backup
location so new logs will only exist after reproducing the issue).

Example for OACore managed server:
$EBS_DOMAIN_HOME/servers/oacore*/logs/*
$EBS_DOMAIN_HOME/servers/oacore_server*/adr/diag/ofm/EBS_domain_*/oac*/incident/*
$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OHS/*/*
$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OPMN/opmn/*

2e. Start the middle tier.

2f. Reproduce the issue while **noting the time stamp of the failure.

2g. Upload the following
$EBS_DOMAIN_HOME/servers/oacore*/logs/*
$EBS_DOMAIN_HOME/servers/oacore_server*/adr/diag/ofm/EBS_domain_*/oac*/incident/*
$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OHS/*/*
$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OPMN/opmn/*

3. Provide the value of the BNE Profiles by running the following SQL:
spool bne_profiles
set linesize 132
set pagesize 132
col NAME format A35
col LEVEL_SET format a10
col CONTEXT format a10
col VALUE format A20 wrap
col Server format a10
col resp format a8 wrap
col application format a10
break on NAME
select
p.profile_option_name NAME,
decode(v.level_id,
10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User’,
10005, ‘Server’,
‘UnDef’) LEVEL_SET,
decode(to_char(v.level_id),
‘10001’, ”,
‘10002’, app.application_short_name,
‘10003’, rsp.responsibility_key,
‘10005’, svr.node_name,
‘10006’, org.name,
‘10004’, usr.user_name,
‘UnDef’) “CONTEXT”,
v.profile_option_value VALUE
from
fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where
p.profile_option_id = v.profile_option_id (+) and
p.profile_option_name = n.profile_option_name and
n.user_profile_option_name like ‘%BNE%’ and
usr.user_id (+) = v.level_value and
rsp.application_id (+) = v.level_value_application_id and
rsp.responsibility_id (+) = v.level_value and
app.application_id (+) = v.level_value and
svr.node_id (+) = v.level_value and
org.organization_id (+) = v.level_value
order by name, level_set
/

4. Please provide the out of the following SQL:
SELECT b.bug_number bug,
b.creation_date creation_date ,
b.last_update_date last_update_date ,
decode(bug_number,
17007206, ‘R12.ATG_PF.C.delta.3’,
17909318, ‘R12.ATG_PF.C.delta.4’,
19245366, ‘R12.ATG_PF.C.delta.5’,
21900895, ‘R12.ATG_PF.C.delta.6’,
24690680, ‘R12.ATG_PF.C.delta.7’,
23706874, ‘BNE:Web Application Desktop Integrator Post 12.2.6 Consolidated Critical Fixes’
) Patch
FROM AD_BUGS b
WHERE b.BUG_NUMBER IN (‘17007206′,’17909318′,’19245366′,’21900895′,’24690680′,’23706874’)
ORDER BY patch;

5. Please provide the integrator having the issue. Is the Integrator having issues with the Create Document download or the Upload functionality having the issue?

6. Please compare the operation (download/upload) time BNE: Enable OOXML Standard = Yes vs. BNE: Enable OOXML Standard = No and provide the results.

7. Upload diagnostic Integrator Meatadata for the Integrator you are testing with. See How To Run Diagnostic Script Integrator Metadata Report For Custom Integrators? (Doc ID 1488669.1)

Run the Diagnostics for the Integrator Metadata while still under the Web Applications Desktop Integrator Diagnostics for the Integrator you are testing with.
7a. Expand the Web ADI Diagnostics region and choose the Test “Web ADI Integrator Metadata” and Click ‘Execute’
7b. Add appropriate input parameters for Responsibility ID and the Integrator name and click on the Submit button.
7c. Check the results by refreshing until the diagnostics complete running.
Initially the report status page will show “In Progress”.
Click on the Refresh button until the report(s) show up under the Success column.
7d. Once the report shows up in the Success column, click on the number 1 under the Success column which is a hyper link.
7e. Then click on the View Report green check mark on the right side of the page to open the report in your browser.
7f. Click on “File > Save as” to save the file to your desktop and then upload the report to the SR.

8.You can have issues with Temp Space used for Web ADI when creating the xls/xlsm file being passed to the Client PC.

NFS / NAS / SAN storage can cause performance issue when network storage is not mounted with nolock / llock option.
Below document explains the same.

Oracle E-Business suite do not directly support or certify any Network addressable Storage (NAS) (SAN and NFS fall under this terminology). There is a very good article on this…

Choosing a Shared File System for Oracle E-Business Suite

Frequently Asked Questions: Sharing the Application Tier File System in Oracle Applications 11i (Note 243880.1)
Sharing The Application Tier File System in Oracle E-Business Suite Release 12.0 and 12.1 (Doc ID 384248.1)

The problem comes around the physical differences between a local hard disk and using Network addressable Storage. For example, problems can arise with the increased amount of time that operating system file locks are held on certain files when they are held on a Shared File systems. These lock timings are a lot smaller for local disk so there is no issue. Also this issue might only ever arise on a system that is put under load, this may answer why other systems do not have the issue.

In the first instance I would contact the Shared File System vendor and ask them what settings they recommend for an E-Business suite web/forms tier. Most vendors (e.g. NetApp) have their own specific recommendations.

If the vendor has no preferences then a number of basic recommendations are made by Oracle to ensure that we do not hit any known issues. The recommendations for the Apps tier are

rw,intr,bg,hard,timeo=600,wsize=32768,rsize=32768,nfsvers=3,tcp,nolock,acregmin=0,acregmax=0

This means that you will need to change your ‘Mount’ options to match. If any of these mount options are not recognized then you will need to contact either the Shared File System vendor or the operating system vendor for more help.

The outstanding issues for using the E-Business suite on Shared File Systems are

– Include the ‘nolock’ option in the mount command.

On Linux, it should be using “nolock” option.

On Solaris, it should be using “llock” option.

– The Apache Lockfile MUST be stored on a local disk

You MUST update the $CONTEXT_FILE and change the variable “s_lock_pid_dir” to point to a local directory on the server. Once the update is done autoconfig must be ran on ALL web/forms tiers.

8a. Ensure that the following variables in the $CONTEXT_FILE are set to a local disk (*NON* NFS Mount Point) on EACH of your Application (web/forms) tiers.

– s_lock_pid_dir
– s_pids_dir
– s_web_pid_file

8b. Run autoconfig to make the changes

8c. Per Note 384248.1 verify that your NFS mounts have the “no lock” option set for all your NFS mounts.

For example:

NFS Parameter Recommendations
—————————————–
For Linux Application Tier File System –
rw,intr,bg,hard,timeo=600,wsize=32768,rsize=32768,nfsvers=3,tcp,nolock,acregmin=0,acregmax=0

~~~~~~~~~~~~~~
Per Development:

If one is using any NAS implementation (NetApps, EMC, SGI, etc) then they MUST make sure to mount their NFS mounts with the
“nolock” option. This parameter is different for different OS’s so have your customers refer to the “man pages” in regards to NFS.

For Linux:
mount -t nfs -o nolock nfs_server:/nfs_devices /nfs_mount

Typical Shared File System Logs are under the /var/log. For NFS, you should see a nfs.log. This will help in debugging .

9. Check the OACore_Server(x) JVM Memory settings and the number of OACores being used in the WLS Console for the JVM Start Options.

 Need the -XX:PermSize -XX:MaxPermSize -Xms and -Xmx settings.

The Defualt JVM Memory settings with EBS tend to not be sized correctly to handle Month End Loads with Web ADI.

An Xmx of 4G had been the original recommendation from Development, but they have entirely backed off from that now in favor of 2G. Garbage Collection starts when heap memory gets low and when there is 4G to clean that takes a lot of time and that JVM cannot do any EBS work when garbage collection is going on so therefore more JVMs with smaller heaps are better than fewer JVMs with large heaps.

Typical Solutions:

============

1. Increasing the number of OACores and Increasing the Memory Heap.

Use the following steps to customize the managed server configuration via the WebLogic Server Administration Console.

1a. Log on to the WebLogic Server Administration Console.
1b. Click on the ‘Servers’ link. This link takes you to a page containing a summary of the WebLogic Administration Server and all managed servers.
1c. Click on the managed server whose configuration needs to be updated. A page containing various tabs for the settings of the managed server appears. For eg. oacore_server1.
1d. Navigate to Configuration Tab > Server Start Tab > You can see the Arguments Section.
1e. Click on Lock and Edit button in the ‘Change Center’ panel.
1f. Update the Heap space to the desired value.

2. Using Profile Option BNE: Enable Upload Compression if you are not using BNE: Enable OOXML Standard as set to Yes. If you use the Profile Option BNE: Enable OOXML Standard = Yes, this uses Zip Functionality in the background to Zip and Upload the Data from the Integrator. You can see better Performance when using BNE: Enable OOXML Standard = Yes. However this comes at a cost of Performance issues on the Middle Tier.  This would be a situation were you set BNE: Enable OOXML Standard = No and use the BNE: Enable Upload Compression. This will put the Upload and Zip Functionality back on the Client PC’s Resources rather then the Middle Tier. However Upload Times will be much Slower.

This were Benchmark Testing comes in to test what works best for your company needs.

With Release 12.2, you can use the Profile Option BNE: Enable Upload Compression for larger uploads.
When Upload Compression mode is enabled, Web ADI spreadsheet gives a new Menu Option: Oracle > Settings. Accessing this menu option, user defines the compressing utility and its executable.
Possible compressing utilities to use are: None, WinZip, and 7Zip.

3. Using smaller chunk of data for the Upload. Using the Benchmark Testing as mentioned above to Benchmark yourself.

Test with Seeded Integrator and Seeded Tables vs. Custom Integrator and Custom Tables being used.

Most Performance issues seen with BNE/Web ADI Upload is due to Custom Tables that the Interface Tables are trying to insert data to.

In this case Oracle Web ADI Support can only address the Performance with seeded Integrators and seeded Database Tables.

4. If BNE: Enable OOXML Standard = Yes is having Performance or Out of Memory issues related to Uploading/Downloading, test using Profile BNE: Enable OOXML Standard = No. This will return the functionality of the Integrator being created on the Client PC rather then the Server as described above. If the Client PC is still hanging with BNE: Enable OOXML Standard = No, ensure Patch 27953931:R12.BNE.C has been applied and retest with BNE: Enable OOXML Standard = Yes.

5. Check for BNE/Web ADI Connection Leaks. Currently there are known issues with BNE holding connections from the JDBC Connection Pool.

The following are the Known Patches to resolve the current Connection Leaks.

Patch 25232083:R12.BNE.C
Patch 22575948:R12.BNE.C
Patch 22575951:R12.BNE.C
Patch 20027711:R12.BNE.C

You can run the Connection Leak/Login Analyzer to get current BNE related Patching for Connection Leaks.

Please run the Login/Connection Leak Analyzer, E-Business Suite Applications Login Analyzer (Doc ID 2319360.1)
Upload the Results.

6. Check for Performance Patch 27953931:R12.BNE.C – 1OFF: LOW PERFORMANCE RENDERING INTEGRATOR LAYOUT WHEN BNE OOXML IS ON

REFERENCE:

R12:Web ADI Performance and Out of Memory issues seen when uploading large amounts of data (Doc ID 2506299.1)

  • June 28, 2019 | 53 views
  • Comments