Performance improvements for month end
closure activity r12
1.1 Metalink IDs
1. How to Create a Custom Concurrent Manager (Doc ID 170524.1)
2. How to Make User Run Any Requests Using Standard Manager Except
Specific Program Run by Custom Manager? (Doc ID 1297419.1)
3. R12 SLA/FAH: How to Improve Performance in Subledger Accounting &
Financials Accounting Hub (Doc ID 791049.1)
4. Generate COGS Recognition Events Performance Issue (Doc ID 1300204.1)
5. Create Accounting Ends In Error: out of memory exception (Doc ID
832483.1)
6. Execute Submit Accounting program with No Report option
1.2 Service Requests
7. SR 3-12799727497: Create accounting for AR running taking more time
than normal duration
2. Points to Remember –
Any CUSTOM MANAGER defined
should have at least one program INCLUDED under them, else the manager would be
considered as STANDARD MANAGER and some of the programs not defined under
either the STANDARD MANAGER or the CUSTOM MANAGER will not be run by either of
them.
Any program INCLUDED in the
CUSTOM MANAGER should be EXCLUED from the STANDARD MANAGER. Any program that is
not included in CUSTOM MANAGER will be run by STANDARD MANAGER by default
Please make a look at the
Sleep cycle of the Custom manager if that is abnormal then it will cause some
performance issues.
If a program is a parent
program spawning multiple child programs then we must explicitly exclude child
programs as well from Standard manager and add them under Custom Manager.
3. Custom manager creation
3.1 Navigate to Concurrent / Manager /
Define.
3.2 Enter required fields –
1. Manager Field: Custom Manager.
2. Short Name: CUST.
3. Type: Concurrent Manager.
4. Program Library: FNDLIBR.
2. Short Name: CUST.
3. Type: Concurrent Manager.
4. Program Library: FNDLIBR.
5. Enter desired cache. (1 recommended for long running programs)
6. Work Shifts: Standard.
7. Enter number of Processes. (8 in our case)
8. Provide Specialization Rules (you can include or exclude program, id, user,
types or combination).
6. Work Shifts: Standard.
7. Enter number of Processes. (8 in our case)
8. Provide Specialization Rules (you can include or exclude program, id, user,
types or combination).
3.3 Activate newly created Custom manager –
3.4 Exclude list of programs from Standard manager –
1. Navigate to Concurrent / Manager / Define
2. Query for Standard Manager -> Specialization Rules
3.4. Include list of programs under Custom manager –
3.5. Take complete bounce of Concurrent managers-
adcmctl.sh stop/start
3.6. Post Checks -
Making sure programs that are included under Custom manager are running
under Custom manager and not Standard manager which was earlier case –
Use below query –
col USER_CONCURRENT_PROGRAM_NAME for a50
col USER_CONCURRENT_QUEUE_NAME for a25
select
d.USER_CONCURRENT_PROGRAM_NAME,b.USER_CONCURRENT_QUEUE_NAME,c.REQUEST_ID from
fnd_concurrent_processes a,fnd_concurrent_queues_vl b, fnd_concurrent_requests
c,FND_CONCURRENT_PROGRAMS_TL d
where a.CONCURRENT_QUEUE_ID =
b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID =
c.controlling_manager
and
c.concurrent_program_id=d.concurrent_program_id
and b.USER_CONCURRENT_QUEUE_NAME
='Custom Manager';
Sample Output –
USER_CONCURRENT_PROGRAM_NAME
USER_CONCURRENT_QUEU REQUEST_ID
--------------------------------------------------
-------------------- ----------
GMF Period Close
Process for Process Organizations Custom Manager 8406888
GMF Period Close
Process for Process Organizations Custom Manager 8406890
GMF Period Close
Process for Process Organizations Custom Manager 8406889
Collect Revenue
Recognition
Information
Custom Manager 8406895
GMF Recreate Batch /
Period
Layers
Custom Manager 8406891
Generate COGS Recognition
Events
Custom Manager 8406896
Record Order
Management
Transactions
Custom Manager 8406894
OPM Actual Cost
Program
Custom Manager 8406892
4. Resolving Out Of Memory
Issue -
4.1 Error Details
Submit accounting for AR module was executed for 10 days and we found
out that this program was taking more than 8 hours to execute and eventually
completed with below error.
ERROR:
****Warning!!! Due to high
volume of data, got out of memory exception...***
****Please retry with scalable
option or modify the Data template to run in scalable mode...***
4.2 Bug Details
The cause of this problem has been identified and verified in an
unpublished Bug 5491055.
4.3 Solution
a. From Responsibility: 'System Adminstrator'.
b. Concurrent:Program > Define.
c. Query for program with short name FAACCPB and XLAACCPB.
d. If there is no value in Options, try adding '-Xmx1024M' in the
Options field and save the record.
e. Retest the issue.
4.4 Execute Submit Accounting program with No Report option
We changed Report option from ‘Detail’ to ‘No Report’ and this was done
to reduce execution time of AR period closure and OPP issues.
Submit accounting parameters used that caused opp issue -
Below parameter was modified to fix this issue -
5. Generate COGS
Recognition Events Performance
5.1 Recommended Patch details
Last Patch resolving performance issues with Generate COGS Recognition
Event process is Patch.19367778:R12.BOM.C (CSTRCMVB.pls 120.45.12010000.85).
5.2 Reorganize 2 critical tables
Run the following
steps before running the "Generate COGS recognition" which is usually
done end of day with less production
activities
Steps to shrink:
alter table
inv.mtl_transactions_interface DEALLOCATE UNUSED;
alter table inv.mtl_transactions_interface ENABLE ROW MOVEMENT;
alter table inv.mtl_transactions_interface shrink space;
alter table inv.mtl_transactions_interface disable ROW MOVEMENT;
alter table bom.cst_revenue_cogs_match_lines DEALLOCATE UNUSED;
alter table bom.cst_revenue_cogs_match_lines ENABLE ROW MOVEMENT;
alter table bom.cst_revenue_cogs_match_lines shrink space;
alter table bom.cst_revenue_cogs_match_lines disable ROW MOVEMENT;
You may
also consider Defragment the INV transaction interfaces execute:
ALTER TABLE INV.MTL_TRANSACTIONS_INTERFACE SHRINK SPACE
COMPACT;
ALTER TABLE INV.MTL_MATERIAL_TRANSACTIONS_TEMP SHRINK SPACE COMPACT;
Gather 30% stats on tables involved in this
view:
exec fnd_stats.gather_table_stats('APPLSYS','FND_CURRENCIES',30);
exec fnd_stats.gather_table_stats('HR','HR_ALL_ORGANIZATION_UNITS',30);
exec
fnd_stats.gather_table_stats('HR','HR_ALL_ORGANIZATION_UNITS_TL',30);
exec fnd_stats.gather_table_stats('HR','HR_LOCATIONS_ALL',30);
exec
fnd_stats.gather_table_stats('HR','HR_ORGANIZATION_INFORMATION',30);
exec
fnd_stats.gather_table_stats('INV','MTL_ONHAND_QUANTITIES_DETAIL',30);
exec fnd_stats.gather_table_stats('INV','MTL_PARAMETERS',30);
exec fnd_stats.gather_table_stats('INV','MTL_SYSTEM_ITEMS_B',30);
exec fnd_stats.gather_table_stats('BOM','CST_ITEM_COSTS',30);
exec fnd_stats.gather_table_stats('GL','GL_LEDGERS',30);
5.3 Improvement analysis
Generate COGS Recognition Events
|
In Hours
|
Time taken before changes
|
10:37:28
|
Time taken after changes
|
00:18:31
|
6. Improve Performance in
Subledger Accounting
6.1 Parallel Execution
of Create accounting
Traverse Steps –
Search for % and this
will change according to your functional configuration.
Click on Update Accounting Option for Subledger Accounting Options
Please note that there are you can run
Accounting Program module wise and you will have to plan for how many programs
you have to run in parallel for a particular module.
In
our case below, we are changing number of Accounting Programs for
Receivables as 8, depending upon number of CPU cores, so for single
Create accounting parent program, there will be 8 child Accounting
Programs with respect to Receivables module.
Click on Update System Options and check for screen below –
Please note that
these modifications donot require any bouncing of services and once we have
saved these changes, Parallel Accounting Programs are spawned in next run.
No comments:
Post a Comment