Concurrent Processing (CP) / APPS Reporting Scripts

INSTRUCTIONS

Please reference the instructions for each script.  It is very important to specialize the queues into fast, medium and slow as this prevents queuing issues when when working with cache sizes.  Work on the principle that the cache is for the manager.  To explain the thinking:

For example, having 1 manager with 10 workers processing at a rate of 1 per sec, advising an initial queue size of 1200, which means that they would get enough entries for about 2 minutes. Having banks with this type of load (sub-second) with 100's of workers and exceptionally high cache sizes, this has solved a lot of throughput issues while reducing the amount of associated I/O and CPU - especially with revisiting the requests table.

For slow queues, then one would generally only have a few workers, and introduce variability using workshifts (more workers in quiet periods) especially if the concurrent jobs cause a significant system load. In this case, the cache size needs to be small. Once a job is in the cache, it can't be re-prioritized. So, here a cache size of 0 (1), 1, or 2 may be a good starting point (and one can also see from the definition screens that this cache is per manager).

CAUTION

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

SCRIPT

Please reference the details for each script.

SAMPLE OUTPUT

- Concurrent Manager Scripts

- Concurrent Requests Scripts

- Printing Script

- Report Review Agent Script

Use These First! If the associated script does not help solve the issue, please upload the output file to an SR if created.

• General Applications Performance Scripts<-- br="" click="" details.="" for="" here="">
  
  Seeded scripts in FND_TOP/sql as of 12.1.3
  
  afcmcreq Prints the log file name of managers that can run a given request
  afcmstat Displays all defined managers and shows current status of manager processes
  afimchk Checks to see if the concurrent manager monitor is actually running
  afimlock Monitors locks that the ICM and CRM are waiting for.
  afimpmon Sets the PMON methd based on the argument (for CONC_PMON_METHOD)
  afimveri Resets the concurrent manager monitor
  afrqpend Lists all the pending/normal requests
  afrqrun Lists all running, terminating, and paused requests
  afrqscm Prints the log file names of managers that can run a given request
  afrqstat Summarizes concurrent request execution since a particular date
  afrquwait Selects all the Pending request with status Q
  
  
  • Script to calculate number of requests run per week for performance gauging:
    
    SELECT COUNT(*) Total, sum(decode(greatest(0,ceil (sysdate - actual_completion_date)), least(7,ceil(sysdate-actual_completion_date) ),1,0)) Week4, sum(decode(greatest(8,ceil (sysdate - actual_completion_date)), least(14,ceil(sysdate-actual_completion_date) ),1,0)) Week3, sum(decode(greatest(15,ceil (sysdate - actual_completion_date)), least(21,ceil(sysdate-actual_completion_date) ),1,0)) Week2, sum(decode(greatest(22,ceil (sysdate - actual_completion_date)), least(28,ceil(sysdate-actual_completion_date) ),1,0)) Week1 FROM FND_CONCURRENT_REQUESTS WHERE ACTUAL_COMPLETION_DATE is not null;
  
  

  Concurrent Manager Scripts

  Concurrent Manager Recovery

  •  Please run the Concurrent Manager Recovery feature to address any Concurrent Manager / Concurrent Processing issues within the Oracle Application Manager.
    
    NOTE: Regular use of the CP Analyzer in Note 1411723.1 can provide an Immediate Analysis and Output of a Concurrent Environment.
  
  
  

  ---

  How To Re-create Concurrent Manager Views

  • How To Re-create Concurrent Manager Views(<-- amp="" br="" click="" details="" download="" for="" here="">This script details how to Re-create the Concurrent Manager Views
    (For all Releases of Apps.)
  
  
  

  ---

  Concurrent Manager Check Script

  • CCM.sql(<-- amp="" br="" click="" details="" download="" for="" here="">This script will diagnose Concurrent Managers and Concurrent Requests
    (For all Releases of Apps.)
  
  
  

  ---

  Concurrent Manager Check Script

  • FNDCCMDiagnostic115.sh(<-- amp="" br="" click="" details="" download="" for="" here="">This script will diagnose Concurrent Managers and Concurrent Requests
    (For Apps. 11.5.4 or higher, not R12)
  
  
  

  ---

  Internal Manager Log Script

  • ICMLOG.sql(<-- amp="" br="" click="" details="" download="" for="" here="">This script will provide the location for Retrieving the Internal Manager Log file
    (For all Releases of Apps.)
  
  
  

  ---

  Concurrent Requests Scripts

  
  
  

  ---

  Concurrent Request Check Script

  • REQCHECK.sql(<-- amp="" br="" click="" details="" download="" for="" here="">Script for diagnosing problematic Concurrent Requests
    (For all Releases of Apps.)
  
  
  

  ---

  Analyze Pending Requests Script

  • ANALYZEPENDING.sql(<-- amp="" br="" click="" details="" download="" for="" here="">Script for diagnosing Pending Concurrent Requests
    (For all Releases of Apps.)
  
  
  

  ---

  Analyze Request Script

  • ANALYZEREQ.sql(<-- amp="" br="" click="" details="" download="" for="" here="">Script for Providing a Detailed Analysis of One Concurrent Request
    (For Applications Release 11 and up.)
  
  
  

  ---

  Who Can Run which Requests

  • WHOCANRUN.sql(<-- amp="" br="" click="" details="" download="" for="" here="">Script that lists Responsibilities that can Run a Given Concurrent Program
    (For all Releases of Apps.)
  
  
  

  ---

  Concurrent Request Diagnostic Script

  • FNDCCMDiagnostic115.sh(<-- amp="" br="" click="" details="" download="" for="" here="">This script will diagnose Concurrent Managers and Concurrent Requests
    (For Applications Release 11.5.4 and up, not R12.)
  
  
  

  ---

  Concurrent Request Diagnostic Script

  • bde_request.sql(<-- amp="" br="" click="" details="" download="" for="" here="">Process and Session info for one Concurrent Request
    (For Applications Release 11.5 and up.)
  
  
  

  ---

  Printing Script

  • FNDPrinterValidation115.sh(<-- amp="" br="" click="" details="" download="" for="" here="">This script checks a customer's Printer configuration/setup within Oracle Applications.
    (For Applications Release 11.5.4 and up, not R12.)
  
  
  

  ---

  Report Review Agent Script

  • FNDValidateFNDFS115.sh(<-- amp="" br="" click="" details="" download="" for="" here="">This script checks basic setup of the concurrent processing tier report review agent.
    (For Applications Release 11.5.4 and up, not R12.)
  
  
  

  ---