Performance improvements for month end closure activity r12

1.  References-

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.

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).

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 –

set lines 1000

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 –

General Ledger Super User

         Financials

                               Accounting Setup Manager

                                             Accounting Setups

          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.