XLAAARPT- Account Analysis Report is running for hours
The main cause for account analysis performance issue is
Cause:
- Lower version of XLAAARPT.xml , DeflateFilter.java and InflateFilter.java
- Old table statistics
- Few processes defined for "Output Post Processor"
- Using Scalable flag as Y
Solution:
1. Apply Patch
a. Apply below Patch to get latest version of files for modules AR,GL & XLA.
* patch 21340722 is 'R12 SLA RPC Aug 2015' OR
In case if there is huge customization and you can't go with this patch as it will affect customized reports.
You can proceed with patch 20363988 which gives Similar version for XLAAARPT.xml but with less no of files.
SO, get the patch impact analysis done by functional team for these patches and go with the suitable one.
However I will suggest to go with patch 20363988 as customization is always there with this big schemas like GL.
Confirm the following file versions:
XLAAARPT.xml 120.29.12010000.17
b. Apply patch 7306874:R12.XDO.B
Confirm the following file versions:
DeflateFilter.java 120.1.12010000.2
InflateFilter.java 120.1.12010000.2
2. Please use the following commands to gather statistisc for these tables:
sqlplus apps/pwd
exec fnd_stats.gather_table_stats ('AP','AP_SUPPLIER_SITES_ALL', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('AP','AP_SUPPLIERS', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('APPLSYS','FND_DOCUMENT_SEQUENCES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('APPLSYS','FND_FLEX_VALUES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('APPLSYS','FND_FLEX_VALUES_TL', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('APPLSYS','FND_LOOKUP_VALUES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('APPLSYS','FND_NEW_MESSAGES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('APPLSYS','FND_USER', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('AR','HZ_CUST_ACCOUNTS', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('AR','HZ_CUST_ACCT_SITES_ALL', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('AR','HZ_CUST_SITE_USES_ALL', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('AR','HZ_PARTIES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('AR','HZ_PARTY_SITES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('FUN','FUN_SEQ_VERSIONS', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_BALANCES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_BUDGET_VERSIONS', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_CODE_COMBINATIONS', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_DAILY_CONVERSION_TYPES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_ENCUMBRANCE_TYPES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_IMPORT_REFERENCES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_JE_BATCHES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_JE_CATEGORIES_TL', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_JE_HEADERS', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_JE_LINES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_JE_SOURCES_TL', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_LEDGERS', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('GL','GL_PERIOD_STATUSES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('XLA','XLA_AE_LINES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('XLA','XLA_AE_HEADERS', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('XLA','XLA_EVENTS', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('XLA','XLA_LOOKUPS', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('XLA','XLA_REPORT_BALANCES', percent=>99,degree=>NULL,cascade=>TRUE);
exec fnd_stats.gather_table_stats ('XLA','XLA_TRANSACTION_ENTITIES', percent=>99,degree=>NULL,cascade=>TRUE);
Allow the above commands to finish.
This might take a while.
3. Set this profile options as follows:
- Concurrent: OPP Process Timeout = 43200 ( 12 hours )
4. Set OPP processes to 4
1. Log on to Applications with "System Administrator" responsibility.
2. Navigate to Concurrent -> Manager -> Define.
3. Query for the "Output Post Processor" service.
4. Click on "Work Shifts"
5. Increase the number of processes.
Processes = 4
6. Save
5. Increase Java heap size to 2.5G:
Check current size:
SELECT service_id, service_handle, developer_parameters
FROM fnd_cp_services
WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');
Increse it to 2.5G if not already:
UPDATE fnd_cp_services
SET developer_parameters =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2560m'
WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');
6. Please insure the following settings:
* Logon to Applications with "XMLPublisher Administrator" responsibility.
* Select the Admin tab and insure the following settings:
- set 'Use XML Publisher's XSLT processor' to True
- set 'Enable scalable feature of XSLT processor' to TRUE
- set 'Enable XSLT runtime optimization' to True
7.
1. Navigate to Payables Manager Responsibility --> Setup --> Accounting Setups --> Subledger Accounting Setup
2. Click on Open Account Balances Listing Definitions
3. Click on "Process Options" Button.
4. Navigate to your Ledger
5. Number of Processors = 64
6. Processing Unit Size = 200
7. Click Apply.
8. set Scalable Flag to N
Go to the ScalableFlag parameter
- check the the DISPLAY checkbox to make it visible
- set the default value to N (In several Metalink docs, including the one I referred, you will be asked to set it to Y, but it is just for diagnostic purpose which will just create logs and reports will run longer, so set it to N)
Reference Doc: R12 AP: Account Analysis Report - XLAAARPT Performance Issue (Doc ID 1946616.1)
Thanks,
Achal
