The Concurrent Processing Analyzer (Note 1411723.1) is a self-service health-check script that reviews the overall footprint, analyzes current configurations and settings for the environment and provides feedback and recommendations on best practices. Your application data is not altered in any way when you run this analyzer.
Framework 4.3.4 update has no new features, but includes bug fixes for:
Includes critical bug fix that causes Java exception when generating DX summary in 11i environments (EBSAF-239).
For analyzers that are not running on EBS 11i, this should not be critical.
Framework 4.3.3 update has no new features, but includes bug fixes for:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
Print only not applied patches in DX for recommended patch signatures
Put the Framework version in the html output
We’re listening to your feedback and made the following changes:
Persistent table headers.
If sql output has lot of rows, you will still see the column headers when scrolling down.
Print parameters in the log even when validation fails and the process stops without creating an output
Data scrambling can be enabled for selected columns based on a user-entered key
Hamburger icon in section (tile) view is moved and made more visible
Start/End Time in Execution Details showing down to seconds only
New features for MGD compatibility:
- Tags can be added to signatures and used for filtering
- Extra information tokens can be defined and their values will be replaced in the signature solution
Change Feedback URL dynamically and point to internal KM note when analyzer output is opened in ISS viewer
select 'FND_CONCURRENT_REQUESTS' "TABLE", '<div align="right">'||to_char(max(rownum),'999,999,999,999')||'</div>' "# ROWS" from FND_CONCURRENT_REQUESTS
union
select 'FND_RUN_REQUESTS' "TABLE", '<div align="right">'||to_char(max(rownum),'999,999,999,999')||'</div>' "# ROWS" from FND_RUN_REQUESTS
union
select 'FND_CONC_REQUEST_ARGUMENTS' "TABLE", '<div align="right">'||to_char(max(rownum),'999,999,999,999')||'</div>' "# ROWS" from FND_CONC_REQUEST_ARGUMENTS
union
select 'FND_DUAL' "TABLE", '<div align="right">'||to_char(max(rownum),'999,999,999,999')||'</div>' "# ROWS" from FND_DUAL
union
select 'FND_CONCURRENT_PROCESSES' "TABLE", '<div align="right">'||to_char(max(rownum),'999,999,999,999')||'</div>' "# ROWS" from FND_CONCURRENT_PROCESSES
union
select 'FND_CONC_STAT_LIST' "TABLE", '<div align="right">'||to_char(max(rownum),'999,999,999,999')||'</div>' "# ROWS" from FND_CONC_STAT_LIST
union
select 'FND_CONC_STAT_SUMMARY' "TABLE", '<div align="right">'||to_char(max(rownum),'999,999,999,999')||'</div>' "# ROWS" from FND_CONC_STAT_SUMMARY
union
select 'FND_ENV_CONTEXT' "TABLE", '<div align="right">'||to_char(max(rownum),'999,999,999,999')||'</div>' "# ROWS" from FND_ENV_CONTEXT
union
select 'FND_CONC_PP_ACTIONS' "TABLE", '<div align="right">'||to_char(max(rownum),'999,999,999,999')||'</div>' "# ROWS" from FND_CONC_PP_ACTIONS
union
select 'FND_RUN_REQ_PP_ACTIONS' "TABLE", '<div align="right">'||to_char(max(rownum),'999,999,999,999')||'</div>' "# ROWS" from FND_RUN_REQ_PP_ACTIONS
Number of rows:
10 rows selected
Elapsed time:
1.185 Sec
TABLE
# ROWS
FND_CONCURRENT_PROCESSES
8,557
FND_CONCURRENT_REQUESTS
163,295
FND_CONC_PP_ACTIONS
63,128
FND_CONC_REQUEST_ARGUMENTS
66,101
FND_CONC_STAT_LIST
FND_CONC_STAT_SUMMARY
FND_DUAL
1
FND_ENV_CONTEXT
808,234
FND_RUN_REQUESTS
3,188
FND_RUN_REQ_PP_ACTIONS
Warning: The FND_CONCURRENT_REQUESTS Table has 163,272 rows of completed runtime data which exceeds the acceptable thresholds that were entered to run this Analyzer.
This Gauge is merely a simple indicator about volume of completed Concurrent Request runtime data on OP11ZNCE1, which can be adjusted when running this Analyzer.
It displays GREEN if less than 3,500 rows are found, ORANGE if less than 5,000, and RED if over 5,000 rows are found.
Findings and Recommendations:
Clean up Concurrent Request Data and move the needle to green by scheduling the FNDCPPUR - Purge Concurrent Request and/or Manager Data on a regular basis.
Run these queries to gather more details about all old requests
Show Summary of counts By Year
select to_char(actual_start_date,'YYYY') "STARTED", to_char(actual_completion_date,'YYYY') "ENDED",
count(request_id) "COUNT"
from fnd_concurrent_requests
where actual_start_date < sysdate-365 --Show Requests started over 1 year ago
group by to_char(actual_start_date,'YYYY'), to_char(actual_completion_date,'YYYY')
order by 1;
Show Summary of counts by Concurrent Program
select fcp.USER_CONCURRENT_PROGRAM_NAME, to_char(r.actual_start_date,'YYYY') "STARTED", to_char(r.actual_completion_date,'YYYY') "ENDED", count(r.request_id) "COUNT"
from fnd_concurrent_requests r, fnd_concurrent_programs_tl fcp
where r.concurrent_program_id = fcp.concurrent_program_id
and r.actual_start_date < sysdate-365 --Show Requests started over 1 year ago
group by fcp.USER_CONCURRENT_PROGRAM_NAME, to_char(r.actual_start_date,'YYYY'),
to_char(r.actual_completion_date,'YYYY')
order by 2;
For more information please review: Doc ID 104282.1 - Concurrent Processing - Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) Doc ID 1057802.1 - Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite
select 'FND_RUN_REQ_LANGUAGES' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from fnd_run_req_languages
WHERE parent_request_id not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_TEMP_FILES' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from Fnd_Temp_Files
WHERE Request_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_CONC_RELEASE_PERIODS_TL' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_RELEASE_PERIODS_TL
WHERE (APPLICATION_ID, CONCURRENT_PERIOD_ID) IN
(SELECT APPLICATION_ID, CONCURRENT_PERIOD_ID
from FND_CONC_RELEASE_PERIODS
Where OWNER_REQ_ID not in (SELECT request_id from fnd_concurrent_requests))
union
select 'FND_CONC_RELEASE_PERIODS' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_RELEASE_PERIODS
WHERE OWNER_REQ_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_CONC_RELEASE_STATES_TL' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_RELEASE_STATES_TL
WHERE (APPLICATION_ID, CONCURRENT_STATE_ID) IN
(SELECT APPLICATION_ID, CONCURRENT_STATE_ID
from FND_CONC_RELEASE_STATES
Where OWNER_REQ_ID not in (SELECT request_id from fnd_concurrent_requests))
union
select 'FND_CONC_RELEASE_STATES' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_RELEASE_STATES
WHERE OWNER_REQ_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_CONC_RELEASE_CLASSES_TL' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_RELEASE_CLASSES_TL
WHERE (APPLICATION_ID, RELEASE_CLASS_ID) IN
(SELECT APPLICATION_ID, RELEASE_CLASS_ID
from FND_CONC_RELEASE_CLASSES
Where OWNER_REQ_ID not in (SELECT request_id from fnd_concurrent_requests))
union
select 'FND_CONC_RELEASE_CLASSES' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_RELEASE_CLASSES
WHERE OWNER_REQ_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_CONC_RELEASE_DISJS_TL' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_RELEASE_DISJS_TL
WHERE (APPLICATION_ID, DISJUNCTION_ID) IN
(SELECT APPLICATION_ID, DISJUNCTION_ID
from FND_CONC_RELEASE_DISJS
Where OWNER_REQ_ID not in (SELECT request_id from fnd_concurrent_requests))
union
select 'FND_CONC_RELEASE_DISJS' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_RELEASE_DISJS
WHERE OWNER_REQ_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_CONC_REL_DISJ_MEMBERS' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_REL_DISJ_MEMBERS
WHERE OWNER_REQ_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_CONC_REL_CONJ_MEMBERS' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_REL_CONJ_MEMBERS
WHERE OWNER_REQ_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_CONC_PP_ACTIONS' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_PP_ACTIONS
WHERE CONCURRENT_REQUEST_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_RUN_REQ_PP_ACTIONS' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_RUN_REQ_PP_ACTIONS
WHERE PARENT_REQUEST_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_FILE_TEMP' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_FILE_TEMP
WHERE REQUEST_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FROM FND_CONC_REQ_OUTPUTS' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from FND_CONC_REQ_OUTPUTS
WHERE CONCURRENT_REQUEST_ID not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_RUN_REQUESTS' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from fnd_run_requests
WHERE parent_request_id not in (SELECT request_id from fnd_concurrent_requests)
union
select 'FND_CONC_REQUEST_ARGUMENTS' TABLE_NAME,
count(*) "COUNT OF ORPHANED REQUEST_IDS" from fnd_conc_request_arguments
WHERE request_id not in (SELECT request_id from fnd_concurrent_requests)
Number of rows:
18 rows selected
Elapsed time:
.396 Sec
TABLE_NAME
COUNT OF ORPHANED REQUEST_IDS
FND_CONC_PP_ACTIONS
35785
FND_CONC_RELEASE_CLASSES
7
FND_CONC_RELEASE_CLASSES_TL
14
FND_CONC_RELEASE_DISJS
0
FND_CONC_RELEASE_DISJS_TL
0
FND_CONC_RELEASE_PERIODS
0
FND_CONC_RELEASE_PERIODS_TL
0
FND_CONC_RELEASE_STATES
0
FND_CONC_RELEASE_STATES_TL
0
FND_CONC_REL_CONJ_MEMBERS
6
FND_CONC_REL_DISJ_MEMBERS
0
FND_CONC_REQUEST_ARGUMENTS
27122
FND_FILE_TEMP
0
FND_RUN_REQUESTS
291
FND_RUN_REQ_LANGUAGES
0
FND_RUN_REQ_PP_ACTIONS
0
FND_TEMP_FILES
10
FROM FND_CONC_REQ_OUTPUTS
2937
Warning: There are orphaned concurrent request records found that will not be purged using the concurrent program (FNDCPPUR) - Purge Concurrent Request and/or Manager Data.
Findings and Recommendations:
This orphaned data should be analyzed closer to determine why it occured.
Several reasons could include :
Manually purging CP data, but not including all the associated tables.
A bug.
A process activity being skipped or aborted.
It is recommended to log a Service Request (SR) with Oracle Support to detect the root cause of these orphaned records.
For more details, please review : Doc ID 104282.1 - Concurrent Processing - Purge Concurrent Request and/or Manager Data Program (FNDCPPUR)
SELECT r.REQUEST_ID, u.user_name, r.PHASE_CODE, r.ACTUAL_START_DATE,
c.CONCURRENT_PROGRAM_NAME, p.USER_CONCURRENT_PROGRAM_NAME, r.ARGUMENT_TEXT,
r.RESUBMIT_INTERVAL, r.RESUBMIT_INTERVAL_UNIT_CODE, r.RESUBMIT_END_DATE
FROM fnd_concurrent_requests r, FND_CONCURRENT_PROGRAMS_TL p, fnd_concurrent_programs c, fnd_user u
WHERE r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID and r.requested_by = u.user_id
and p.CONCURRENT_PROGRAM_ID = c.CONCURRENT_PROGRAM_ID
and c.CONCURRENT_PROGRAM_NAME = 'FNDCPPUR'
AND p.language = 'US'
and r.ACTUAL_COMPLETION_DATE is null and r.PHASE_CODE in ('P','R')
order by c.CONCURRENT_PROGRAM_NAME, r.ARGUMENT_TEXT
Number of rows:
0 rows selected
Elapsed time:
.007 Sec
Warning: There are a total of 163,272 records in FND_CONCURRENT_REQUESTS that are completed, but no "Purge Concurrent Request and/or Manager Data" program (FNDCPPUR) scheduled or running
Findings and Recommendations:
Please Review Concurrent Processing purging status with your team.
Run the concurrent program "Purge Concurrent Request and/or Manager Data" (FNDCPPUR) with "Entity" parameter as "ALL" for all requests, or for specific requests that have large volumes of purge eligible data as seen above. The last purge of Concurrent Request data completed on No Date info available for VISION.
FNDCPPUR should be scheduled and run on a regular basis to avoid performance issues. Run the query behind the SQL SCRIPT button to get the complete list of purge eligible concurrent request data.
Additionally, the following are very good methods to follow for optimizing the process:
Run the job during hours of low workload. Doing this after hours will lessen the contention on the tables from running against your daily processing.
To get the requests under control, run the FNDCPPUR program with Age=20 or Age=18 would be a good method. That means, all requests older than 18 or 20 days will be purged.
Once the requests are under control, run the FNDCPPUR program with Age=7 to maintain an efficient process. This would solely depend on the level of processing that is performed at your site
For more information please review: Doc ID 1057802.1 - Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite Doc ID 1095625.1 - Health Check Alert: Purge the eligible records from the FND_CONCURRENT_REQUESTS table
Note: This section is only looking at the scheduled jobs in FND_CONCURRENT_REQUESTS table. Jobs scheduled using other tools (DBMS_JOBS, CONSUB, or PL/SQL, etc) are not reflected here, so keep this in mind.
select p.USER_CONCURRENT_PROGRAM_NAME, decode(r.phase_code,'C','Complete') STATUS,
'<div align="right">'||to_char(count(r.request_id),'999,999,999,999')||'</div>' "COUNT"
FROM fnd_concurrent_requests r, FND_CONCURRENT_PROGRAMS_TL p
WHERE r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
and r.phase_code='C'
and p.language = 'US'
group by p.USER_CONCURRENT_PROGRAM_NAME, r.phase_code
order by count(r.request_id) desc
Number of rows:
The resultset is limited to 30 rows. For a complete list of records, please run the query directly in the database.
Elapsed time:
.847 Sec
USER_CONCURRENT_PROGRAM_NAME
STATUS
COUNT
Autoinvoice Import Program
Complete
35,924
Prepayments Matching Program
Complete
25,538
OAM Applications Dashboard Collection
Complete
21,688
Workflow Background Process
Complete
13,559
New Convergence - FOT002 Send notification to sentinel
Complete
7,310
XXEXP - Extraction de chaine de nuit
Complete
6,580
XXEXP - Execution Engine
Complete
4,832
Journal Import
Complete
4,502
DQM Serial Sync Index Program
Complete
4,364
Accounting Program
Complete
2,942
Posting: Single Ledger
Complete
1,770
SCP AR 3071 Applications Uploader
Complete
1,658
XXNCV - XF016 - Banks Interface - Workers
Complete
1,426
XXNCV_REP012_013 - Customers Loading Worker
Complete
1,285
XXNCV - Invoice interface - Workers
Complete
1,254
XXNCV_FOT010d : $U - Select and Run loading program
Warning: There are a total of 163,272 records in FND_CONCURRENT_REQUESTS that are completed, and eligible for purging.
Findings and Recommendations:
Review Concurrent Processing purging status with your team.
Run the concurrent program "Purge Concurrent Request and/or Manager Data" (FNDCPPUR) with "Entity" parameter as "ALL" for all requests, or for specific requests that have large volumes of purge eligible data as seen above. The last purge of Concurrent Request data completed on No Date info available for VISION.
FNDCPPUR should be scheduled and run on a regular basis to avoid performance issues. Run the query behind the SQL SCRIPT button to get the complete list of purge eligible concurrent request data.
For more information please review: Doc ID 1057802.1 - Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite Doc ID 1095625.1 - Health Check Alert: Purge the eligible records from the FND_CONCURRENT_REQUESTS table
Note: This section is only looking at the scheduled jobs in FND_CONCURRENT_REQUESTS table. Jobs scheduled using other tools (DBMS_JOBS, CONSUB, or PL/SQL, etc) are not reflected here, so keep this in mind.
SELECT name, value
from v$parameter
where upper(name) in ('AQ_TM_PROCESSES','JOB_QUEUE_PROCESSES','JOB_QUEUE_INTERVAL',
'UTL_FILE_DIR','NLS_LANGUAGE','NLS_TERRITORY',
'CPU_COUNT','PARALLEL_THREADS_PER_CPU')
UNION
select parameter, value from v$nls_parameters where parameter in ('NLS_CHARACTERSET')
Number of rows:
8 rows selected
Elapsed time:
.012 Sec
NAME
VALUE
NLS_CHARACTERSET
AL32UTF8
aq_tm_processes
6
cpu_count
208
job_queue_processes
20
nls_language
AMERICAN
nls_territory
AMERICA
parallel_threads_per_cpu
2
utl_file_dir
/usr/tmp/OP11ZNCE
Information:
For more information refer to Doc ID 396009.1 - Database Initialization Parameters for Oracle E-Business Suite Release 12
select VARIABLE_NAME, replace(value,':',chr(10)) "VALUE"
from FND_ENV_CONTEXT
where CONCURRENT_PROCESS_ID = (select max(p.CONCURRENT_PROCESS_ID)
from fnd_concurrent_processes p, fnd_concurrent_queues u
WHERE p.concurrent_queue_id = u.concurrent_queue_id
AND p.queue_application_id = u.application_id
AND u.concurrent_queue_name = 'FNDICM')
and VARIABLE_NAME in ('APPLLKOR','AF_CLASSPATH','AF_JLIB','AF_JRE_TOP',
'AF_LD_LIBRARY_PATH','AFJSMARG','AFJVAPRG','APPLCSF','APPLDCP',
'APPLFENV','APPLLOG','APPLORB','APPLORC','APPLOUT','APPLPTMP',
'APPLTMP','APPS_JDBC_URL','CA_FILEIO_64','CLASSPATH',
'CONTEXT_FILE','CONTEXT_NAME','DE_DISABLE_PLS_512','DISPLAY',
'EVENT_10932','FND_TOP','FNDNAM','FNDSM_SCRIPT','HOSTNAME',
'LD_LIBRARY_PATH','NLS_LANG','NLS_NUMERIC_CHARACTERS',
'ORACLE_HOME','PLATFORM','PRINTER','REPORTS60_PATH',
'REPORTS_CLASSPATH','REPORTS_NO_DUMMY_PRINTER','REPORTS_PATH',
'REPORTS_POST','REPORTS_PRE','REPORTS_TMP','TK_PRINT_STATUS',
'TK_PRINTER','TNS_ADMIN','TWO_TASK','XDO_TOP','XENVIRONMENT')
order by VARIABLE_NAME
Number of rows:
43 rows selected
Elapsed time:
.005 Sec
VARIABLE_NAME
VALUE
AFJSMARG
-server -Xmx384m -XX
NewRatio=2 -XX
+UseSerialGC -Doracle.apps.fnd.common.Pool.leak.mode=stderr
off -verbose
gc
The Internal Concurrent Manager (ICM) for OP11ZAS4 is "Active", and has been running since 13-FEB-19.
The current Concurrent Manager logs are found :
/data/flf/P11Z/nce/work/logoa/OP11ZNCE_0213.mgr.
Refer to Doc ID 1355735.1 - Difference between APPLPTMP and APPLTMP Directories in EBS
select d.owner,
'<div align="right">'||to_char(count(d.status),'999,999,999,999')||'</div>' "COUNT"
from dba_objects d
where d.status = 'INVALID'
and d.owner in ('APPS','APPLSYS','APPLSYSPUB','CTXSYS','PUBLIC','SYS','SYSTEM')
group by d.owner
Number of rows:
1 rows selected
Elapsed time:
.108 Sec
OWNER
COUNT
APPS
9
Warning: There are 9 Invalid Objects found in your instance schemas.
Findings and Recommendations:
The above list summarizes quickly what schemas the INVALID objects belong to.
It is recommended that you drill down into each schema and re-compile these in order to make them VALID again.
We also check if any belong to FND, as this control Concurrent Processing in particular.
Consider running ADADMIN and compiling the entire APPS Schema, or just for the Product in question.
In order to avoid dependencies issues and errors, EBS customers should always use the adamin tool instead of manually compiling EBS / Apps database objects.
Please follow Troubleshooting Guide - invalid objects in the E-Business Suite Environment 11i and 12 Doc ID 1325394.1 for more information.
select d.owner, d.object_type, substr(d.object_name,0,3)||'%...' "OBJECT_NAME", '<div align="right">'||to_char(count(d.status),'999,999,999,999')||'</div>' "COUNT"
from dba_objects d
where d.status = 'INVALID'
and d.owner in ('APPS','APPLSYS','APPLSYSPUB','CTXSYS','PUBLIC','SYS','SYSTEM')
group by d.owner, d.object_type, substr(d.object_name,0,3)
order by 4 desc
Number of rows:
9 rows selected
Elapsed time:
.106 Sec
OWNER
OBJECT_TYPE
OBJECT_NAME
COUNT
APPS
MATERIALIZED VIEW
PJI%...
1
APPS
MATERIALIZED VIEW
POA%...
1
APPS
MATERIALIZED VIEW
GL_%...
1
APPS
PACKAGE BODY
SCJ%...
1
APPS
MATERIALIZED VIEW
OE_%...
1
APPS
MATERIALIZED VIEW
LNS%...
1
APPS
MATERIALIZED VIEW
MRP%...
1
APPS
PACKAGE BODY
SCP%...
1
APPS
MATERIALIZED VIEW
OZF%...
1
Warning: There are 9 Invalid Objects found in your instance.
Findings and Recommendations:
The above list summarizes quickly what products the INVALID objects belong to.
It is recommended that you re-compile these in order to make them VALID again.
We next check if any belong to FND, as this control Concurrent Processing in particular.
Consider running ADADMIN and compiling the entire APPS Schema, or just for the Product in question.
In order to avoid dependencies issues and errors, EBS customers should always use the adamin tool instead of manually compiling EBS / Apps database objects.
Please review the following :
EBS Invalid Object Utility Doc ID 2214169.1 and
Troubleshooting Guide - invalid objects in the E-Business Suite Environment 11i and 12 Doc ID 1325394.1 for more information.
select d.owner, d.object_name, d.object_type, d.status
from dba_objects d
where d.status = 'INVALID'
and d.owner in ('APPS','APPLSYS')
and d.object_name like 'FND%'
Number of rows:
0 rows selected
Elapsed time:
.009 Sec
All checks passed.
Nice Work!!
There are no Invalid FND Objects found on your instance.
Warning: Please check if any of the recommended E-Business Suite Technology Stack patches were not applied in this 12.1.3 instance
Findings and Recommendations:
To get a current accurate list of recommended EBS product patches that are applied/not applied to your instance, please run Patch Wizard.
See Doc ID 976188.1 - Patch Wizard Utility, Doc ID 976688.2 FAQ, or Doc ID 1077813.1 Videos for more information.
Above is a short list of recommended product patches as of the date this script was written to show you what may or may not be applied on this OP11ZNCE1 instance.
Please review the list above and schedule to apply any unappplied patches as soon as possible
Refer to the note indicated for more information about each patch
ADADMIN: Maintain Snapshot Information was executed more than 30 days ago. It is recommended that AD Utilities (Adadmin) "Maintain Snapshot Information" is run periodically as key tools (Patch Wizard, ADPatch,etc) rely on this information being accurate and up-to-date.
Select Bugs.Bug_Number as PATCH,
Decode(Bugs.Bug_Number,
22644544, '22644544 - Oracle 12.1.3+ E-Business Suite Recommended Patch Collection 5 [RPC5]') as "EBS CPU Patches",
decode(Ad_Patch.Is_Patch_Applied('R12',-1,bugs.bug_Number),'EXPLICIT','APPLIED','NOT APPLIED') as "APPLIED"
From
(select '22644544' as bug_number From Dual) Bugs
Warning: Please check if any of the recommended E-Business Suite CP and Technology Stack patches were not applied in this 12.1.3 instance OP11ZNCE1.
Findings and Recommendations: To get a current accurate list of recommended EBS CP and Techstack patches that are applied/not applied to your instance, please run Patch Wizard.
See Doc ID 976188.1 - Patch Wizard Utility, Doc ID 976688.2 FAQ, or Doc ID 1077813.1 Videos for more information.
Above is a short list of recommended CP and Technology Stack patches as of the date this script was written to show you what may or may not be applied on this OP11ZNCE1 instance.
Please review the list above and schedule to apply any unapplied patches as soon as possible
Refer to the note indicated for more information about each patch
ADADMIN: Maintain Snapshot Information was executed more than 30 days ago. It is recommended that AD Utilities (Adadmin) "Maintain Snapshot Information" is run periodically as key tools (Patch Wizard, ADPatch,etc) rely on this information being accurate and up-to-date.
SELECT p.user_concurrent_program_name program_name, count(r.request_id),
to_char(round(avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 1440), 2),'999,999,999,999.99') "avg_run_time|mins",
to_char(round(min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 1440), 2),'999,999,999,999.99') "min_run_time|mins",
to_char(round(max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 1440), 2),'999,999,999,999.99') "max_run_time|mins"
from fnd_concurrent_requests r, fnd_concurrent_processes c, fnd_concurrent_queues q,
fnd_concurrent_programs_vl p
where p.concurrent_program_id = r.concurrent_program_id and p.application_id = r.program_application_id
and c.concurrent_process_id = r.controlling_manager and q.concurrent_queue_id = c.concurrent_queue_id
and q.concurrent_queue_name <> 'HIGH_IMPACT'and p.application_id >= 20000 and r.actual_start_date >= sysdate-31
and r.status_code = 'C' and r.phase_code in ('C','G')
and (nvl(r.actual_completion_date,r.actual_start_date) - r.actual_start_date) * 24 * 60 > 30
and p.user_concurrent_program_name not like 'Gather%Statistics%'
and ((nvl(r.actual_completion_date,r.actual_start_date) - r.actual_start_date) * 24 > 16
or (r.actual_start_date-trunc(r.actual_start_date)) * 24 between 9 and 17
or (r.actual_completion_date-trunc(r.actual_completion_date)) * 24 between 9 and 17)
group by p.user_concurrent_program_name
order by avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 1440) desc
Number of rows:
5 rows selected
Elapsed time:
.255 Sec
PROGRAM_NAME
COUNT(R.REQUEST_ID)
avg_run_time mins
min_run_time mins
max_run_time mins
XXNCV: Update template informations
2
124.27
109.52
139.02
XXNCV: XMLGDL4S Reconcile - General Ledger on 4 segments
2
124.22
109.50
138.93
XXFRA - Monthly Flows by Nature
2
44.39
41.25
47.53
XXNCV - Automatic Receipts Creation Program (API)
4
38.78
35.33
41.88
XXEXP - Execution Engine
1
33.00
33.00
33.00
Information: You have Long Running Reports During Business Hours
Findings and Recommendations:
Review the requests listed and confirm if they are intended to run for longer amounts of time.
If the wrong date range is used or a large volume of data exists for the request, a longer run time can be expected.
Monthly, Quarterly, and Yearly requests would typically run longer.
Visit the Concurrent Processing Product Information Center (PIC) Doc ID 1304305.1 for additional performance and setup documentation.
SELECT f.application_short_name "APPLICATION", substr(p.user_concurrent_program_name,1,55) "DESCRIPTION",
substr(p.concurrent_program_name,1,20) "PROGRAM", r.priority "PRIORITY",
'<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "#TIMESRUN",
'<div align="right">'||to_char(round(sum(actual_completion_date - actual_start_date) * 1440, 2),'999,999,999,999.99')||'</div>' "TOTAL|MINS",
'<div align="right">'||to_char(round(avg(actual_completion_date - actual_start_date) * 1440, 2),'999,999,999,999.99')||'</div>' "AVG|MINS",
'<div align="right">'||to_char(round(max(actual_completion_date - actual_start_date) * 1440, 2),'999,999,999,999.99')||'</div>' "MAX|MINS",
'<div align="right">'||to_char(round(min(actual_completion_date - actual_start_date) * 1440, 2),'999,999,999,999.99')||'</div>' "MIN|MINS",
'<div align="right">'||to_char(round(stddev(actual_completion_date - actual_start_date) * 1440, 2),'999,999,999,999.99')||'</div>' "RUN|STHDEV MINS",
'<div align="right">'||to_char(round(stddev(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 1440, 2),'999,999,999,999.99')||'</div>' "WAIT|STHDEV MINS",
'<div align="right">'||to_char(round(sum(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 1440, 2),'999,999,999,999.99')||'</div>' "#WAITED|MINS",
'<div align="right">'||to_char(round(avg(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 1440, 2),'999,999,999,999.99')||'</div>' "AVG|WAIT MINS",
c.request_class_name "TYPE"
from fnd_concurrent_request_class c, fnd_application f, fnd_concurrent_programs_vl p,
fnd_concurrent_requests r
where r.program_application_id = p.application_id and r.concurrent_program_id = p.concurrent_program_id
and r.status_code in ('C','G') and r.phase_code = 'C' and p.application_id = f.application_id
and r.program_application_id = f.application_id and r.request_class_application_id = c.application_id(+)
and r.concurrent_request_class_id = c.request_class_id(+)
group by c.request_class_name, f.application_short_name, p.concurrent_program_name, p.user_concurrent_program_name, r.priority
order by count(*)
Number of rows:
The resultset is limited to 30 rows. For a complete list of records, please run the query directly in the database.
Elapsed time:
1.481 Sec
APPLICATION
DESCRIPTION
PROGRAM
PRIORITY
#TIMESRUN
TOTAL MINS
AVG MINS
MAX MINS
MIN MINS
RUN STHDEV MINS
WAIT STHDEV MINS
#WAITED MINS
AVG WAIT MINS
TYPE
SQLGL
Program - Revalue Balances
GLCRVL
50
1
4.27
4.27
4.27
4.27
.00
.00
.43
.43
XXNCV
3 Soldes sur cpt clients cred
XXNCV_AR_070_MASS_3_
50
1
13.12
13.12
13.12
13.12
.00
.00
.02
.02
AR
Customer Profile Copy
ARBCPC
50
1
.03
.03
.03
.03
.00
.00
.17
.17
SCP
SCP FND 2011 Flex Values Uploader
SCFD2VAL
50
1
6.40
6.40
6.40
6.40
.00
.00
.02
.02
OZF
Migrate Accruals for Multi Currency Changes
OZFMIGUTLREC
50
1
.00
.00
.00
.00
.00
.00
158.97
158.97
IEX
IEX: Synchronize Collector Work
IEX_SYNC_OWNERSHIP
50
1
.00
.00
.00
.00
.00
.00
.17
.17
SCP
SCP GL 1041 Sources Uploader
SCGL1SOU
50
1
.02
.02
.02
.02
.00
.00
.00
.00
XXNCV
XXNCV - Full Revaluation Process
XXNCV_GL008_01
50
1
7.27
7.27
7.27
7.27
.00
.00
.00
.00
XXNCV
XXNCV - Full Revaluation Process for all revaluation te
XXNCV_GL008_05
50
1
8.00
8.00
8.00
8.00
.00
.00
.00
.00
XXNCV
XXNCV - Update Flow and Origin Segments Revaluation
XXNCV_GL008_03
50
1
.18
.18
.18
.18
.00
.00
.02
.02
XXNCV
2 Soldes sur cptes clients deb
XXNCV_AR_070_MASS_2_
50
1
24.93
24.93
24.93
24.93
.00
.00
.00
.00
WMS
Generate All Rules
WMSGRULE
50
1
.12
.12
.12
.12
.00
.00
158.98
158.98
AR
Applied Receipts Journal
ARXAPPJR
50
1
.27
.27
.27
.27
.00
.00
.00
.00
XXNCV
XXNCV_REP032 - Mapping of DISE and COME Customers
XXNCV_REP032_01
50
1
.10
.10
.10
.10
.00
.00
.25
.25
FND
Deactivate
DEACTIVATE
0
1
FND
Gather Table Statistics
FNDGTST
50
1
.63
.63
.63
.63
.00
.00
.00
.00
FND
Rebuild Help Search Index
AFLOBBLD
50
1
6.15
6.15
6.15
6.15
.00
.00
89.58
89.58
BOM
Build Workday Calendar
BMCPCL
50
1
.07
.07
.07
.07
.00
.00
.13
.13
AR
Customer Listing - Detail
RAXCUSLR
50
1
.13
.13
.13
.13
.00
.00
.35
.35
INV
Items Data Scripts Execution
INVIDSEP
50
1
.00
.00
.00
.00
.00
.00
496.12
496.12
ONT
Salesperson Order Summary Report
OEXOESOS
50
1
.05
.05
.05
.05
.00
.00
.02
.02
SQLAP
Cancel Payment Process Request
APXCNCLB
50
1
.00
.00
.00
.00
.00
.00
.00
.00
XXFRA
XXFRA - GL Extract to SMIS
XXFRA_GL005_01
50
1
.77
.77
.77
.77
.00
.00
.02
.02
FND
Cross-Validation Rules Listing
FNDFFCVL
50
1
.03
.03
.03
.03
.00
.00
.02
.02
FND
Purge Obsolete Workflow Runtime Data
FNDWFPR
50
1
.47
.47
.47
.47
.00
.00
198.32
198.32
ONT
Validate OM Workflow
OEXVWF
50
1
.00
.00
.00
.00
.00
.00
.05
.05
XXNCV
XXNCV Mass extract for loss and profit transfer analysi
XXNCV_AR_070_MASS_EX
50
1
25.05
25.05
25.05
25.05
.00
.00
.02
.02
AR
DQM Index Optimization Program
ARHDQOPT
50
2
.03
.02
.02
.02
.00
.72
1.72
.86
XXNCV
XXNCV - CFI ERP GL Extraction Program
XXNCV_CFI_ERP_GL01
50
2
41.53
20.77
21.85
19.68
1.53
.00
1.03
.52
QP
ADDITEMS
ADDITEMS
50
2
.05
.03
.03
.02
.01
.01
.02
.01
Information: This section identifies the total time duration for recently completed requests.
Findings and Recommendations:
The output produced can be cross referenced with the enabled managers and defined workshifts outputs,
for better allocation of requests across the existing managers/workshifts.
For example you can consider assigning quick requests to one manager and/or workshift, and assigning slow requests to another manager and/or workshift.
Requests with varying runtimes can also be moved to their own manager, or remain with the standard manager queue.
SELECT w.seconds_in_wait "Secondswait", w.event "waitEvent", w.p1||chr(10)||w.p2||chr(10)||w.p3 "Session Wait",
p.spid||chr(10)||s.process "ServerClient", s.sid||chr(10)||s.serial#||chr(10)||s.sql_hash_value "SidSerialSQLHash",
u.user_name||chr(10)||PHASE_CODE||' '||STATUS_CODE||chr(10)||s.status "DBPhaseStatusCODEUser",
Request_id||chr(10)||priority_request_id||chr(10)||Parent_request_id "Request_id",
concurrent_program_name, user_concurrent_program_name,
to_char(requested_start_Date,'DD-MON-RR HH24:MI:SS') "Requested Start Date",
ARGUMENT_TEXT, CONCURRENT_QUEUE_ID, QUEUE_DESCRIPTION
FROM FND_CONCURRENT_WORKER_REQUESTS, fnd_user u, v$session s, v$process p, v$session_wait w
WHERE (Phase_Code='R')and hold_flag != 'Y'and Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND STATUS_CODE IN ('I', 'Q')))and '1' in (0,1,4)
and requested_by=u.user_id and s.paddr=p.addr and s.sid=w.sid and oracle_process_id = p.spid
and oracle_session_id = s.audsid
order by requested_start_date
Number of rows:
0 rows selected
Elapsed time:
.001 Sec
Information: There are no Concurrent Requests currently Running on this instance.
Findings and Recommendations:
This table reflects a summary for all concurrent requests running on the instance with their current state.
SELECT decode(phase_code, 'P', 'Pending requests','R', 'Running requests','C', 'Completed requests') "PHASE", '<div align="right">'||to_char(count(request_id),'999,999,999,999')||'</div>' "# OF REQUESTS"
FROM fnd_concurrent_requests
GROUP BY phase_code
Number of rows:
2 rows selected
Elapsed time:
.057 Sec
PHASE
# OF REQUESTS
Completed requests
163,273
Pending requests
22
Information: Provides a count of concurrent requests in a state of: Pending, Running, or Completed.
Findings and Recommendations:
The output provided is for review and confirmation by your teams, and serves as a baseline of how often you are purging Concurrent Request tables.
If the total records are too large performance issues can result and FNDCPPUR should be run, otherwise there is no immediate action required.
SELECT request_id "REQUEST ID", nvl(meaning, 'UNKNOWN') "STATUS", user_concurrent_program_name "PROGRAM NAME",
to_char(actual_start_date, 'DD-MON-RR HH24:MI:SS') "STARTED", decode(run_alone_flag, 'Y', 'Yes', 'No') "RUN ALONE"
FROM fnd_concurrent_requests fcr, fnd_lookups fl, fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'R' AND LOOKUP_TYPE = 'CP_STATUS_CODE' AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id AND fcr.program_application_id = fcpv.application_id
ORDER BY actual_start_date, request_id
Number of rows:
0 rows selected
Elapsed time:
.001 Sec
Information: There are no concurrent requests currently running on this OP11ZNCE1 instance.
Findings and Recommendations:
The output provided is for review and confirmation by your teams, and serves as a baseline of whats currently running on the system.
Otherwise there is no immediate action required.
SELECT 'Pending' "PHASE", meaning "STATUS", '<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "# REQUESTS"
FROM fnd_concurrent_requests, fnd_lookups
WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' AND lookup_code = status_code AND phase_code = 'P'
GROUP BY meaning
Number of rows:
2 rows selected
Elapsed time:
.001 Sec
PHASE
STATUS
# REQUESTS
Pending
Standby
11
Pending
Normal
11
Information:
The output provided is for review and confirmation by your teams, and serves as a baseline of whats currently pending on the system.
Otherwise there is no immediate action required.
select u.user_name, cp.CONCURRENT_PROGRAM_ID "CP ID", cp.CONCURRENT_PROGRAM_NAME "SHORT NAME", cp.USER_CONCURRENT_PROGRAM_NAME "PROGRAM NAME",
'<div align="right">'||to_char(count(r.request_id),'999,999,999,999')||'</div>' "COUNT"
from fnd_concurrent_requests r, fnd_user u, fnd_concurrent_programs_vl cp
where r.requested_by = u.user_id
and r.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
and u.user_name in (select user_name from (
select u.user_name, count(r.request_id)
from fnd_concurrent_requests r, fnd_user u
where r.requested_by = u.user_id
group by u.user_name
order by 2 desc)
where rownum < 11)
group by u.user_name, cp.CONCURRENT_PROGRAM_ID, cp.CONCURRENT_PROGRAM_NAME, cp.USER_CONCURRENT_PROGRAM_NAME
order by 5 desc
Number of rows:
308 rows selected
Elapsed time:
1.435 Sec
USER_NAME
CP ID
SHORT NAME
PROGRAM NAME
COUNT
QSWQ3755
20428
RAXTRX
Autoinvoice Import Program
35,018
QSWQ3755
44196
ARPREMAT
Prepayments Matching Program
24,721
SYSADMIN
42852
FNDOAMCOL
OAM Applications Dashboard Collection
21,689
ZNFS2692
36888
FNDWFBG
Workflow Background Process
13,560
RCDV8508
56346
XXEXP_ATOS01_03
XXEXP - Extraction de chaine de nuit
6,581
EXP_M_FTA001
55344
XXNCV_FOT002_06
New Convergence - FOT002 Send notification to sentinel
XXNCV - Update Flow and Origin Segments Revaluation
1
EXP_M_FTA001
55459
XXNCV_AR069_01
XXNCV - Transfer to Profit of receipts
1
SYSADMIN
41590
WMSGRULE
Generate All Rules
1
SYSADMIN
42118
ARHDQOPT
DQM Index Optimization Program
1
EXP_L_0050_GL
55366
XXNCV_GL008_05
XXNCV - Full Revaluation Process for all revaluation templates
1
QCZQ2004
58367
SCGL1SOU
SCP GL 1041 Sources Uploader
1
SYSADMIN
53500
OZFMIGUTLREC
Migrate Accruals for Multi Currency Changes
1
QSWQ3755
58537
SCFD2VAL
SCP FND 2011 Flex Values Uploader
1
Information:
List of top 10 users that are running/scheduling Concurrent Requests and the Concurrent Programs they are running. This list is for performance review.
Requests Submitted by End-Dated Users
Item Name
Item Value
SQL
select fcr.REQUEST_ID, fu.USER_NAME REQUESTED_BY,
decode(fcr.PHASE_CODE, 'C','Completed', 'I','I=Inactive', 'P','P=Pending', 'R','R=Running') PHASE,
p.USER_CONCURRENT_PROGRAM_NAME, fcr.ARGUMENT_TEXT "Arguments", fcr.RESUBMIT_INTERVAL EVERY,
fcr.RESUBMIT_INTERVAL_UNIT_CODE SO_OFTEN, fcr.RESUBMIT_END_DATE
FROM fnd_concurrent_requests fcr, FND_CONCURRENT_PROGRAMS_TL p, fnd_user fu
WHERE fcr.REQUESTED_BY = fu.USER_ID
and fcr.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
and p.USER_CONCURRENT_PROGRAM_NAME LIKE 'Active%User%'
AND p.LANGUAGE = 'US'
and fcr.ACTUAL_COMPLETION_DATE is null
and fcr.PHASE_CODE <> 'C'
and fcr.REQUESTED_BY in (select fui.user_id from fnd_user fui where nvl(fui.END_DATE, sysdate) < sysdate )
All checks passed.
Success !!!
No Concurrent Requests found scheduled by users or responsibilities that have been end-dated.
SELECT 'Pending Requests Regularly Scheduled:' "PENDING REQUESTS",
'<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNTS"
from fnd_concurrent_requests
WHERE (requested_start_date > sysdate OR status_code = 'P') AND phase_code = 'P'
union
SELECT 'Pending Requests Non Regularly Scheduled:' "Pending Requests",
'<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT"
from fnd_concurrent_requests
WHERE requested_start_date <= sysdate AND status_code != 'P' AND phase_code = 'P'
union
SELECT 'Pending Requests On Hold:' "Pending Requests",
'<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT"
from fnd_concurrent_requests
WHERE hold_flag = 'Y' AND phase_code = 'P'
union
SELECT 'Pending Requests Not On Hold:' "Pending Requests",
'<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT"
from fnd_concurrent_requests
WHERE hold_flag != 'Y' AND phase_code = 'P'
SELECT request_id REQ_ID, fu.user_name REQUESTED_BY, nvl(meaning, 'UNKNOWN') status, user_concurrent_program_name PROGRAM_NAME,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') SUBMITTED, to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') START_DATE
FROM fnd_concurrent_requests fcr, fnd_lookups fl, fnd_concurrent_programs_vl fcpv, fnd_user fu
WHERE fcr.requested_by = fu.user_id
and phase_code = 'P' AND (fcr.requested_start_date >= sysdate OR status_code = 'P')
AND LOOKUP_TYPE = 'CP_STATUS_CODE' AND lookup_code = status_code AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY requested_start_date
Number of rows:
22 rows selected
Elapsed time:
.001 Sec
REQ_ID
REQUESTED_BY
STATUS
PROGRAM_NAME
SUBMITTED
START_DATE
35158214
ZNFS2692
Normal
Workflow Background Process
28-FEB-19 17:05:01
28-FEB-19 17:10:00
35158213
RCDV8508
Standby
XXEXP - Extraction de chaine de nuit
28-FEB-19 17:00:17
28-FEB-19 17:10:00
35158206
SYSADMIN
Standby
OAM Applications Dashboard Collection
28-FEB-19 16:53:19
28-FEB-19 17:23:19
35143383
ZNFS2692
Normal
Workflow Background Process
27-FEB-19 18:00:03
28-FEB-19 18:00:00
35143386
RCDV8508
Standby
XXEXP - Compte-rendu de chaine de nuit
27-FEB-19 18:04:21
28-FEB-19 18:02:00
35143391
SYSADMIN
Normal
Workflow Mailer Statistics Concurrent Program
27-FEB-19 18:15:05
28-FEB-19 18:15:05
35143395
SYSADMIN
Standby
Purge Logs and Closed System Alerts
27-FEB-19 18:26:43
28-FEB-19 18:25:18
35143400
SYSADMIN
Normal
Workflow Agent Activity Statistics Concurrent Program
27-FEB-19 18:39:58
28-FEB-19 18:39:58
35143424
SYSADMIN
Normal
Workflow Work Items Statistics Concurrent Program
27-FEB-19 19:46:46
28-FEB-19 19:46:46
35157942
SYSADMIN
Normal
Workflow Control Queue Cleanup
28-FEB-19 11:01:43
28-FEB-19 23:01:43
35144972
ZNFS2692
Normal
Purge Inactive Sessions
28-FEB-19 02:00:02
01-MAR-19 02:00:00
35148841
RCDV8508
Standby
XXEXP - Compte-rendu de chaine de nuit
28-FEB-19 06:04:00
01-MAR-19 06:02:00
35148865
RCDV8508
Standby
XXEXP - Compte-rendu de chaine de nuit
28-FEB-19 06:33:56
01-MAR-19 06:32:00
35148911
RCDV8508
Standby
XXEXP - Compte-rendu de chaine de nuit
28-FEB-19 07:33:54
01-MAR-19 07:32:00
35148950
RCDV8508
Standby
XXEXP - Compte-rendu de chaine de nuit
28-FEB-19 08:03:57
01-MAR-19 08:02:00
35158212
ZYJC3125
Normal
IEX: Promise Reconciliation
28-FEB-19 17:00:08
01-MAR-19 17:00:00
35094417
ZYJC3125
Standby
Refresh AR Transactions Summary Tables
23-FEB-19 17:03:05
02-MAR-19 17:00:00
35094415
ZYJC3125
Standby
Refresh AR Transactions Summary Tables
23-FEB-19 17:01:32
02-MAR-19 17:00:00
35094485
ZNFS2692
Normal
Purge Obsolete SOA Monitor Data
23-FEB-19 20:00:27
02-MAR-19 20:00:00
35094597
ZNFS2692
Normal
XXNCV - calcule les statistiques sur le schema - mainprog
23-FEB-19 23:32:46
02-MAR-19 23:00:00
35094627
ZYJC3125
Normal
IEX: Strategy Management
24-FEB-19 01:00:01
03-MAR-19 01:00:00
35094850
SYSADMIN
Standby
XXNCV_FOT013 : Datafix Automatization Main Program
24-FEB-19 12:00:35
03-MAR-19 12:00:00
Information:
The output provided is for review and confirmation by your teams, and serves as a baseline of whats currently scheduled on the system.
Otherwise there is no immediate action required.
For more information refer to Doc ID 213021.1 - Concurrent Processing (CP) / APPS Reporting Scripts
SELECT request_id "REQ ID", nvl(meaning, 'UNKNOWN') "STATUS", user_concurrent_program_name "PROGRAM NAME",
to_char(request_date, 'DD-MON-RR HH24:MI:SS') "SUBMITTED"
FROM fnd_concurrent_requests fcr, fnd_lookups fl, fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P' AND hold_flag = 'Y' AND fcr.requested_start_date <= sysdate
AND status_code != 'P' AND LOOKUP_TYPE = 'CP_STATUS_CODE' AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id AND fcr.program_application_id = fcpv.application_id
ORDER BY request_date, request_id
Number of rows:
0 rows selected
Elapsed time:
0 Sec
Information: There are no Pending Requests on Hold and wating to be run
Findings and Recommendations:
The output provided is for review and confirmation by your teams, and serves as a baseline of whats currently scheduled on the system.
Otherwise there is no immediate action required.
To get a complete list of Pending Requests on Hold including the Request ID, run the query behind the SQL SCRIPT button.
SELECT request_id id, nvl(meaning, 'UNKNOWN') status, user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd, to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') requestd
FROM fnd_concurrent_requests fcr, fnd_lookups fl, fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P' AND hold_flag = 'N' AND fcr.requested_start_date <= sysdate
AND status_code != 'P' AND LOOKUP_TYPE = 'CP_STATUS_CODE' AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id AND fcr.program_application_id = fcpv.application_id
ORDER BY request_date, request_id
Number of rows:
0 rows selected
Elapsed time:
.001 Sec
Information: There are not scheduled requests waiting to run that are currently not on hold.
Findings and Recommendations:
The output provided is for review and confirmation by your teams, and serves as a baseline of whats currently scheduled on the system.
Otherwise there is no immediate action required.
SELECT trunc(REQUESTED_START_DATE) "REQUESTED START DATE", '<div align="right">'||to_char(count(request_id),'999,999,999,999')||'</div>' "COUNTS"
FROM FND_CONCURRENT_REQUESTS
WHERE REQUESTED_START_DATE BETWEEN sysdate-30 AND sysdate
group by rollup(trunc(REQUESTED_START_DATE))
Number of rows:
The resultset is limited to 30 rows. For a complete list of records, please run the query directly in the database.
Elapsed time:
.08 Sec
REQUESTED START DATE
COUNTS
29-JAN-19
773
30-JAN-19
1,301
31-JAN-19
1,289
01-FEB-19
1,577
02-FEB-19
801
03-FEB-19
1,029
04-FEB-19
2,539
05-FEB-19
1,879
06-FEB-19
1,717
07-FEB-19
1,489
08-FEB-19
1,477
09-FEB-19
776
10-FEB-19
1,202
11-FEB-19
1,289
12-FEB-19
1,569
13-FEB-19
1,510
14-FEB-19
1,073
15-FEB-19
1,528
16-FEB-19
768
17-FEB-19
1,225
18-FEB-19
1,959
19-FEB-19
1,528
20-FEB-19
1,404
21-FEB-19
1,468
22-FEB-19
1,997
23-FEB-19
767
24-FEB-19
901
25-FEB-19
1,405
26-FEB-19
1,622
27-FEB-19
1,198
Information:
The output provided is for review and confirmation by your teams, and serves as a baseline of your average monthly throughput, and identify any spikes or drops.
Otherwise there is no immediate action required.
SELECT CONCURRENT_PROGRAM_NAME "SHORT NAME", USER_CONCURRENT_PROGRAM_NAME "PROGRAM NAME", RUN_ALONE_FLAG "RUN ALONE", ENABLED_FLAG "ENABLED", DESCRIPTION
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE (RUN_ALONE_FLAG='Y')
order by RUN_ALONE_FLAG, ENABLED_FLAG desc
Number of rows:
11 rows selected
Elapsed time:
.009 Sec
SHORT NAME
PROGRAM NAME
RUN ALONE
ENABLED
DESCRIPTION
POSSJAVA
POS Supplier Hub Java Concurrent Program
Y
Y
POS Supplier Hub Java Concurrent Program
RRSCREATEPL
RRS : Create Property Locations For Sites
Y
Y
RRS : Create Property Locations For Sites
FVRDIEFR
Delinquent Invoices Eligible for Referral Report
Y
Y
Delinquent Invoices Eligiblefor Referral Report
JLZZPRAD
Argentine/Colombian Payables Remittance Advice
Y
Y
Argentine/Colombian Payables Remittance Advice
MRCBLC
MRP BOM/Wip Loop Checking Program
Y
Y
ICX_ITEM_DIAGNOSTICS
Run Item Search Diagnostics
Y
Y
This program diagnoses the missing records in item search and corrects them.
CN_ODI_CALL
Incentive Compensation Analytics - ODI
Y
Y
Incentive Compensation Analytics - ODI
RRSCREATEASSETS
RRS : Create Asset Instances For Sites
Y
Y
RRS : Create Asset Instances For Sites
ARHAPREL
Account to Party Relationships Migration Program
Y
N
Account relationship migration to Party relationship
FVFBWTIM
Fund Balance with Treasury Reconciliation Import Process
Y
N
Fund Balance with Treasury Reconciliation Imoprt Process
JLZZPRAD
Argentine/Colombian Payables Remittance Advice
Y
N
Argentine/Colombian Payables Remittance Advice
Information:
The output provided is for review and confirmation by your teams, and is intended to identify any concurrent program definitions causing Pending/Standby Requests which may require review.
SELECT SEGMENT_NAME "TABLE NAME",
'<div align="right">'||to_char(sum(BLOCKS),'999,999,999,999')||'</div>' "TOTAL BLOCKS",
'<div align="right">'||to_char(sum(bytes/1024/1024),'999,999,999,999')||'</div>' "SIZE IN MB"
from dba_segments
where segment_name in ('FND_CONCURRENT_REQUESTS','FND_CONCURRENT_PROCESSES','FND_CONCURRENT_QUEUES',
'FND_ENV_CONTEXT','FND_EVENTS','FND_EVENT_TOKENS')
group by segment_name
order by 2
Number of rows:
6 rows selected
Elapsed time:
.858 Sec
TABLE NAME
TOTAL BLOCKS
SIZE IN MB
FND_EVENT_TOKENS
16
0
FND_EVENTS
16
0
FND_CONCURRENT_QUEUES
16
0
FND_CONCURRENT_PROCESSES
384
3
FND_ENV_CONTEXT
10,528
82
FND_CONCURRENT_REQUESTS
16,128
126
Information:
The output provided is for review and confirmation by your teams, and serves as a baseline regarding your tablespace disk overhead.
You can cross reference the collected information with exisiting notes on tablespace sizing and defragmentation best practices
SELECT table_name "TABLE NAME", '<div align="right">'||to_char(blocks,'999,999,999,999')||'</div>' "BLOCKS",
'<div align="right">'||to_char(empty_blocks,'999,999,999,999')||'</div>' "EMPTY BLOCKS",
'<div align="right">'||to_char(num_rows,'999,999,999,999')||'</div>' "NUM_ROWS",
'<div align="center">'||last_analyzed||'</div>' "LAST ANALYZED",
'<div align="right">'||to_char(sample_size,'999,999,999,999')||'</div>' "SAMPLE SIZE"
FROM all_tables
WHERE table_name in ('FND_CONCURRENT_REQUESTS','FND_CONCURRENT_PROCESSES',
'FND_CONCURRENT_QUEUES','FND_ENV_CONTEXT','FND_EVENTS','FND_EVENT_TOKENS')
Number of rows:
6 rows selected
Elapsed time:
.096 Sec
TABLE NAME
BLOCKS
EMPTY BLOCKS
NUM_ROWS
LAST ANALYZED
SAMPLE SIZE
FND_CONCURRENT_PROCESSES
370
0
8,557
23-FEB-19
8,557
FND_CONCURRENT_QUEUES
13
0
70
23-FEB-19
70
FND_CONCURRENT_REQUESTS
15,959
0
166,650
23-FEB-19
16,665
FND_ENV_CONTEXT
10,382
0
808,740
16-FEB-19
80,874
FND_EVENTS
0
0
0
23-FEB-19
0
FND_EVENT_TOKENS
0
0
0
23-FEB-19
0
Information:
The output provided is for review and confirmation by your teams and serves as a baseline regarding your tablespace disk overhead.
You can cross reference the collected information with existing notes on tablespace sizing and defragmentation best practices.
Please review Doc ID 1057802.1 - Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite.
SELECT q.CONCURRENT_QUEUE_NAME "QUEUE NAME", q.USER_CONCURRENT_QUEUE_NAME "USER QUEUE NAME",
a.application_short_name "MODULE", q.cache_size "CACHE", p.concurrent_time_period_name "TIME PERIOD",
qs.min_processes "MIN PROCESSES", qs.max_processes "MAX PROCESSES", qs.sleep_seconds "SLEEP SECS"
from fnd_concurrent_queues_vl q, fnd_product_installations i, fnd_application_vl a,
fnd_concurrent_time_periods p, fnd_concurrent_queue_size qs
where i.application_id = q.application_id
and a.application_id = q.application_id
and qs.queue_application_id = q.application_id
and qs.concurrent_queue_id = q.concurrent_queue_id
and qs.period_application_id = p.application_id
and qs.concurrent_time_period_id = p.concurrent_time_period_id
and q.enabled_flag = 'Y'
and nvl(q.control_code,'X') <> 'E'
order by q.concurrent_queue_name, p.concurrent_time_period_id
Number of rows:
22 rows selected
Elapsed time:
.004 Sec
QUEUE NAME
USER QUEUE NAME
MODULE
CACHE
TIME PERIOD
MIN PROCESSES
MAX PROCESSES
SLEEP SECS
FNDCPOPP
Output Post Processor
FND
Standard
2
2
30
FNDCPOPP2
Post-traitement de sortie 2
FND
Standard
2
30
FNDCPOPP3
Post-traitement de sortie 3
FND
Standard
2
30
FNDCRM
Conflict Resolution Manager
FND
Standard
1
1
30
FNDIM_OP11ZAS1
Internal Monitor: OP11ZAS1
FND
Standard
1
60
FNDIM_OP11ZAS2
Internal Monitor: OP11ZAS2
FND
Standard
1
60
FNDIM_OP11ZAS3
Internal Monitor: OP11ZAS3
FND
Standard
1
60
FNDIM_OP11ZAS4
Internal Monitor: OP11ZAS4
FND
Standard
1
60
FNDIM_OP11ZAS5
Internal Monitor: OP11ZAS5
FND
Standard
1
60
FNDIM_OP11ZAS6
Internal Monitor: OP11ZAS6
FND
Standard
1
60
FNDSCH
Scheduler/Prereleaser Manager
FND
Standard
1
1
30
OAMCOLMGR
OAM Metrics Collection Manager
FND
1
Standard
1
1
60
STANDARD
Standard Manager
FND
2
Standard
50
50
15
STANDARD2
Gestionnaire standard 2
FND
2
Standard
50
15
STANDARD3
Gestionnaire standard 3
FND
2
Standard
50
15
WFALSNRSVC
Workflow Agent Listener Service
FND
Standard
1
1
30
WFMLRSVC
Workflow Mailer Service
FND
Standard
1
1
30
WFWSSVC
Workflow Document Web Services Service
FND
Standard
1
1
30
XXNCV_AR006_RAXTRX_01
Chargement des factures 1
XXNCV
2
Standard
50
60
XXNCV_AR006_RAXTRX_02
Chargement des factures 2
XXNCV
2
Standard
50
60
XXNCV_FOT011_02
Notification Sentinel
XXNCV
Standard
5
60
XXNCV_FOT10B_01
Chargement des fichiers (FOT10B)
XXNCV
2
Standard
8
15
All checks passed.
This section collects the Concurrent Managers that are currently Active and Enabled to process data, and associated with a specific Workshift, and establishes a baseline list of managers defined on your system.
The Workshifts are created to define specific times when a Manager can run requests.
The resulting data is for review and confirmation by your teams, and serves as a baseline for comparison with later outputs above.
Otherwise there is no immediate action required.
For more information refer to Doc ID 1373727.1 - FAQ: EBS Concurrent processing Performance and Best Practices.
SELECT q.CONCURRENT_QUEUE_NAME "QUEUE NAME", p.concurrent_time_period_name "TIME PERIOD", qs.min_processes "MIN PROCESSES"
from fnd_concurrent_queues_vl q, fnd_product_installations i, fnd_application_vl a,
fnd_concurrent_time_periods p, fnd_concurrent_queue_size qs
where i.application_id = q.application_id and a.application_id = q.application_id
and qs.queue_application_id = q.application_id and qs.concurrent_queue_id = q.concurrent_queue_id
and qs.period_application_id = p.application_id and qs.concurrent_time_period_id = p.concurrent_time_period_id
and q.enabled_flag = 'Y' and nvl(q.control_code,'X') <> 'E' and qs.min_processes >0 and i.status <> 'I'
order by q.concurrent_queue_name, p.concurrent_time_period_id
Number of rows:
4 rows selected
Elapsed time:
.005 Sec
QUEUE NAME
TIME PERIOD
MIN PROCESSES
XXNCV_AR006_RAXTRX_01
Standard
50
XXNCV_AR006_RAXTRX_02
Standard
50
XXNCV_FOT011_02
Standard
5
XXNCV_FOT10B_01
Standard
8
Warning: There are Concurrent Managers that are active for Application modules not Installed or Used.
Findings and Recommendations:
These unused managers can impact performance, and deactivating them can reduce current application overhead on the instance.
SELECT q.CONCURRENT_QUEUE_NAME "QUEUE NAME", q.max_processes "MAX PROCESSES", q.running_processes "RUNNING", q.node_name "NODE1", q.node_name2 "NODE2",
p.concurrent_time_period_name "TIME PERIOD", qs.min_processes "MIN PROCESSES"
from fnd_concurrent_queues_vl q, fnd_product_installations i, fnd_application_vl a,
fnd_concurrent_time_periods p, fnd_concurrent_queue_size qs
where i.application_id = q.application_id and a.application_id = q.application_id
and qs.queue_application_id = q.application_id and qs.concurrent_queue_id = q.concurrent_queue_id
and qs.period_application_id = p.application_id and qs.concurrent_time_period_id = p.concurrent_time_period_id
and q.enabled_flag = 'Y' and nvl(q.control_code,'X') <> 'E' and qs.min_processes >0 and q.manager_type = 1
and p.concurrent_time_period_name not in ('Weekend','Off-Peak AM','Off-Peak PM')
order by qs.min_processes desc,q.concurrent_queue_name
Number of rows:
8 rows selected
Elapsed time:
.002 Sec
QUEUE NAME
MAX PROCESSES
RUNNING
NODE1
NODE2
TIME PERIOD
MIN PROCESSES
STANDARD
50
50
OP11ZAS4
Standard
50
STANDARD2
50
50
OP11ZAS5
Standard
50
STANDARD3
50
50
OP11ZAS6
Standard
50
XXNCV_AR006_RAXTRX_01
50
50
OP11ZAS5
OP11ZAS4
Standard
50
XXNCV_AR006_RAXTRX_02
50
50
OP11ZAS6
OP11ZAS4
Standard
50
XXNCV_FOT10B_01
8
8
OP11ZAS6
OP11ZAS5
Standard
8
XXNCV_FOT011_02
5
5
OP11ZAS5
OP11ZAS6
Standard
5
OAMCOLMGR
1
1
OP11ZAS4
OP11ZAS5
Standard
1
Information: Total Target Processes for Request Managers Excluding Off-Hours
This identifies the total number of processes that can be run for a given concurrent manager.
The greater the number of processes defined can impact increased Concurrent Processing loads.
Findings and Recommendations:
The resulting data is for review and confirmation by your teams, and serves as a baseline for comparison with later outputs above.
Otherwise there is no immediate action required.
SELECT q.CONCURRENT_QUEUE_NAME, q.USER_CONCURRENT_QUEUE_NAME, q.cache_size, qs.MAX_PROCESSES "ACTUAL", qs.min_processes "TARGET"
from fnd_concurrent_queues_vl q, fnd_product_installations i, fnd_application_vl a,
fnd_concurrent_time_periods p, fnd_concurrent_queue_size qs
where i.application_id = q.application_id and a.application_id = q.application_id
and qs.queue_application_id = q.application_id and qs.concurrent_queue_id = q.concurrent_queue_id
and qs.period_application_id = p.application_id and qs.concurrent_time_period_id = p.concurrent_time_period_id
and q.enabled_flag = 'Y' and nvl(q.control_code,'X') <> 'E' and qs.min_processes >0 and q.manager_type = 1
group by q.CONCURRENT_QUEUE_NAME, q.USER_CONCURRENT_QUEUE_NAME, q.cache_size, qs.min_processes, qs.MAX_PROCESSES
having decode(max(qs.min_processes),1,2,max(qs.min_processes)) > nvl(q.cache_size,1)
order by q.concurrent_queue_name
Number of rows:
8 rows selected
Elapsed time:
.001 Sec
CONCURRENT_QUEUE_NAME
USER_CONCURRENT_QUEUE_NAME
CACHE_SIZE
ACTUAL
TARGET
OAMCOLMGR
OAM Metrics Collection Manager
1
1
1
STANDARD
Standard Manager
2
50
50
STANDARD2
Gestionnaire standard 2
2
50
STANDARD3
Gestionnaire standard 3
2
50
XXNCV_AR006_RAXTRX_01
Chargement des factures 1
2
50
XXNCV_AR006_RAXTRX_02
Chargement des factures 2
2
50
XXNCV_FOT011_02
Notification Sentinel
5
XXNCV_FOT10B_01
Chargement des fichiers (FOT10B)
2
8
Information:
Please review the list of Concurrent Managers for correct Cache Size and Actual & Target Processes.
A Managers cache size reflects the number of requests a manager adds to its queue, each time it reads available requests to run.
For example, if a manager has 1 target process and a cache value of 3, it will read 3 requests and run those requests before returning to cache additional requests.
Tip: Enter a value of 1 when defining a manager that runs long, time-consuming jobs, and a value of 3 or 4 for managers that run small, quick jobs.
For more information refer to Doc ID 1373727.1 - FAQ: EBS Concurrent processing Performance and Best Practices
SELECT q.concurrent_queue_name "CONCURRENT QUEUE NAME",
'<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT",
'<div align="right">'||to_char(round(sum(r.actual_completion_date - r.actual_start_date) * 24, 2),'999,999,999,999.99')||'</div>' "ELAPSED",
'<div align="right">'||to_char(round(avg(r.actual_completion_date - r.actual_start_date) * 24, 2),'999,999,999,999.99')||'</div>' "AVG",
'<div align="right">'||to_char(round(stddev(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24, 2),'999,999,999,999.99')||'</div>' "WSTDDEV",
'<div align="right">'||to_char(round(sum(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24, 2),'999,999,999,999.99')||'</div>' "WAITED",
'<div align="right">'||to_char(round(avg(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24, 2),'999,999,999,999.99')||'</div>' "AVGWAIT"
from fnd_concurrent_programs p, fnd_concurrent_requests r, fnd_concurrent_queues q,
fnd_concurrent_processes p
where r.program_application_id = p.application_id and r.concurrent_program_id = p.concurrent_program_id
and r.phase_code='C' -- completed and r.status_code in ('C','G') -- completed normal or with warning
and r.controlling_manager=p.concurrent_process_id and q.concurrent_queue_id=p.concurrent_queue_id
and r.concurrent_program_id=p.concurrent_program_id
group by q.concurrent_queue_name
Number of rows:
8 rows selected
Elapsed time:
.827 Sec
CONCURRENT QUEUE NAME
COUNT
ELAPSED
AVG
WSTDDEV
WAITED
AVGWAIT
OAMCOLMGR
138
.16
.00
.71
10.05
.07
XXNCV_FOT10B_01
332
22.63
.07
.00
.52
.00
XXNCV_AR006_RAXTRX_02
17,270
1,541.12
.09
.04
611.85
.04
STANDARD2
42,283
4,019.35
.10
1.21
4,027.52
.10
XXNCV_AR006_RAXTRX_01
17,086
1,568.27
.09
.04
614.53
.04
STANDARD
35,789
3,784.27
.11
2.57
6,416.78
.18
XXNCV_FOT011_02
130
1.80
.01
.10
9.80
.08
STANDARD3
19,995
697.25
.03
.06
263.67
.01
Information: These are the concurrent managers being used, and can be compared with the actual concurrent managers allocated at startup.
This only considers requests with completion status of normal/warning.
Findings and Recommendations:
Please consider deactivation of any managers which are consistently not being used, and are listed as Active/Enabled above.
SELECT a.CONCURRENT_QUEUE_ID "Queue ID", a.QUEUE_APPLICATION_ID "Apps ID",
b.user_CONCURRENT_QUEUE_NAME "Concurrent Manager", decode(a.PHASE_CODE, 'P','PENDING','R','Running') Phase,count(1)
FROM FND_CONCURRENT_WORKER_REQUESTS a, fnd_concurrent_queues_vl b
WHERE (a.Phase_Code = 'P' or a.Phase_Code = 'R') and a.hold_flag != 'Y' and a.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND a.PHASE_CODE = 'R' AND a.STATUS_CODE IN ('I', 'Q'))) and '1' in (0,1,4)
And a.concurrent_queue_id=b.concurrent_queue_id
group by a.CONCURRENT_QUEUE_ID, a.QUEUE_APPLICATION_ID, b.user_CONCURRENT_QUEUE_NAME, a.PHASE_CODE
order by 1
Number of rows:
0 rows selected
Elapsed time:
.002 Sec
Information:
There are no concurrent requests that are in a Pending state.
Information: OPP is currently configured, identifying the: Service ID, Service Handle, and Parameters used.
Findings and Recommendations:
The output provided is for review and confirmation by your teams, and serves as a baseline regarding your current OPP configuration.
You can cross reference the collected information with existing notes on OPP best practices : Doc ID 1399454.1 - Tuning Output Post Processor (OPP) to Improve Performance Doc ID 1057802.1 - Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite
select 'AQ$_FND_CP_GSM_OPP_AQTBL' "NAME", '<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT" from applsys.FND_CP_GSM_OPP_AQTBL
union
select 'AQ$_FND_CP_GSM_OPP_AQTBL_S' "NAME", '<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT" from applsys.AQ$_FND_CP_GSM_OPP_AQTBL_S
union
select 'AQ$_FND_CP_GSM_OPP_AQTBL_T' "NAME", '<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT" from applsys.AQ$_FND_CP_GSM_OPP_AQTBL_T
union
select 'AQ$_FND_CP_GSM_OPP_AQTBL_T' "NAME", '<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT" from applsys.AQ$_FND_CP_GSM_OPP_AQTBL_T
union
select 'AQ$_FND_CP_GSM_OPP_AQTBL_H' "NAME", '<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT" from applsys.AQ$_FND_CP_GSM_OPP_AQTBL_H
union
select 'AQ$_FND_CP_GSM_OPP_AQTBL_I' "NAME", '<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT" from applsys.AQ$_FND_CP_GSM_OPP_AQTBL_I
union
select 'AQ$_FND_CP_GSM_OPP_AQTBL_I' "NAME", '<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT" from applsys.AQ$_FND_CP_GSM_OPP_AQTBL_I
union
select 'AQ$_FND_CP_GSM_OPP_AQTBL_G' "NAME", '<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT" from applsys.AQ$_FND_CP_GSM_OPP_AQTBL_G
union
select 'AQ$_FND_CP_GSM_OPP_AQTBL_L' "NAME", '<div align="right">'||to_char(count(*),'999,999,999,999')||'</div>' "COUNT" from applsys.AQ$_FND_CP_GSM_OPP_AQTBL_L
Number of rows:
7 rows selected
Elapsed time:
.024 Sec
NAME
COUNT
AQ$_FND_CP_GSM_OPP_AQTBL
6
AQ$_FND_CP_GSM_OPP_AQTBL_G
0
AQ$_FND_CP_GSM_OPP_AQTBL_H
6
AQ$_FND_CP_GSM_OPP_AQTBL_I
6
AQ$_FND_CP_GSM_OPP_AQTBL_L
0
AQ$_FND_CP_GSM_OPP_AQTBL_S
12
AQ$_FND_CP_GSM_OPP_AQTBL_T
4,727
Warning: The AQ$_FND_CP_GSM_OPP_AQTBL table has many rows
AQs tables are used to look for "subscriptions" by FNDSMs. That is, when ICM calls for FNDSM to start, they "subscribe" to this queue to identify its status. The time taking for the process cleanup prior to the ICM starting up the regular CMs is correlated to the number of processes that were not stopped cleanly. In case of un-clean shutdown, the process to restart will be longer as manager spends extra cycles to perform housekeeping tasks.
Findings and Recommendations:
It is highly recommended to always ensure the clean shutdown of the concurrent managers.
The Purge Concurrent Request and/or Manager Data Program request should be run periodically, however, it does not Purge AQ Tables.
For maintaining a healthy level of records in FND_CONCURRENT_REQUESTS instead of running 'Purge Concurrent Program' with the same parameters for all the applications, try running it for different applications depending on the number of days the data should be kept for.
It is recommended to schedule a cron job to query records in APPLSYS.FND_CP_GSM_OPP_AQTBL to monitor it and use DBMS_AQADM.PURGE_QUEUE_TABLE to purge the table as needed.
Follow the steps outlined in Doc ID 1156523.1 - How To Purge FND_AQ Tables on how to purge the FND_CP_GSM_OPP_AQTBL table manually to clean up the associated tables and indexes.
select
b.user_profile_option_name profile_name
, a.profile_option_name profile_short_name
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') Level_type
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') level_value
, c.PROFILE_OPTION_VALUE profile_value
from
fnd_profile_options a
, FND_PROFILE_OPTIONS_VL b
, FND_PROFILE_OPTION_VALUES c
, FND_USER d
, FND_USER e
, FND_RESPONSIBILITY_VL g
, FND_APPLICATION h
where
b.user_profile_option_name like '%Concurrent%Force%Output%'
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by
b.user_profile_option_name, c.level_id,
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown')
Number of rows:
1 rows selected
Elapsed time:
.126 Sec
PROFILE_NAME
PROFILE_SHORT_NAME
LEVEL_TYPE
LEVEL_VALUE
PROFILE_VALUE
Concurrent: Force Local Output File Mode
CONC_FORCE_LOCAL_OUTPUT_FILE_MODE
Site
Site
N
All checks passed.
Concurrent: Force Local Output File Mode is set as expected
select REQUEST_ID, PHASE_CODE, STATUS_CODE
from fnd_concurrent_requests
where request_id in (select concurrent_request_id
from fnd_conc_pp_actions
where action_type >= 6
and processor_id is null)
and PHASE_CODE != 'C'
Number of rows:
1 rows selected
Elapsed time:
.066 Sec
REQUEST_ID
PHASE_CODE
STATUS_CODE
35094627
P
I
Warning: There are pending OPP Requests
Findings and Recommendations:
Review the solutions offered in Doc ID 1399454.1 - Tuning Output Post Processor (OPP) to Improve Performance
for pending OPP Requests.
Note : In general "Administer Concurrent Manager Screen" shows pending and running requests against each manager, but it does not show pending requests against OPP manager. The OPP uses 'Advanced Queue' to find the pending requests that it needs to process, hence it becomes difficult to configure (or) do sizing of OPP without knowing the workload. For example, "Administrator Screen" shows how many requests are pending (or) running against "Standard Managers" using this information, we can size (Increase Process) Standard Manager accordingly.
However, when the OPP begins to process a concurrent request, it will update the processor_id column of fnd_conc_pp_actions with it's concurrent_process_id. We can use the following query to find pending requests against OPP:
select REQUEST_ID, PHASE_CODE, STATUS_CODE
from fnd_concurrent_requests
where request_id in (select concurrent_request_id
from fnd_conc_pp_actions
where action_type >= 6
and processor_id is null)
and PHASE_CODE != 'C';
Most of the time, Requests stuck in Running/Normal as the requests wait at OPP. Because they do not know the pending queue size (OPP), customer inadvertently increases the Standard Manager process to speed up which goes in vain. That results in performance issue in the concurrent manager layer.
We have created an ER to display the "Pending Requests" in Concurrent Administer Screen for OPP.
Bug 24626221 - CONCURRENT ADMINISTER SCREEN TO DISPLAY PENDING REQUESTS
You can cross reference the collected information with existing notes on OPP best practices : Doc ID 1399454.1 - Tuning Output Post Processor (OPP) to Improve Performance for pending OPP Requests. Doc ID 1057802.1 - Concurrent Processing - Best Practices for Performance for Concurrent Managers in E-Business Suite
SELECT q.USER_CONCURRENT_QUEUE_NAME "Manager", a.application_name,
p.concurrent_time_period_name "Work Shift", p.description "Description",
qs.min_processes "Processes", qs.service_parameters "Parameter", qs.sleep_seconds "Sleep Seconds"
from fnd_concurrent_queues_vl q, fnd_application_vl a,
fnd_concurrent_time_periods p, fnd_concurrent_queue_size qs
where a.application_id = q.application_id
and qs.queue_application_id = q.application_id
and qs.concurrent_queue_id = q.concurrent_queue_id
and qs.period_application_id = p.application_id
and qs.concurrent_time_period_id = p.concurrent_time_period_id
and q.enabled_flag = 'Y'
and nvl(q.control_code,'X') <> 'E'
and q.CONCURRENT_QUEUE_NAME = 'STANDARD'
order by q.concurrent_queue_name, p.concurrent_time_period_id
SELECT q.USER_CONCURRENT_QUEUE_NAME "MANAGER", q.CONCURRENT_QUEUE_NAME "SHORT NAME", q.enabled_flag "ENABLED",
a.application_name "APPLICATION", q.description "DESCRIPTION", decode(q.manager_type,'0','Internal Concurrent Manager','1','Concurrent Manager', '2','Internal Monitor', '3','Transaction Manager', '4','Conflict Resolution Manager','5','Scheduler/Prereleaser Manager', '6','Service Manager') "TYPE", q.DATA_GROUP_ID "DATA GROUP", q.RESOURCE_CONSUMER_GROUP "CONSUMER GROUP",
q.cache_size "CACHE SIZE", q.node_name "PRIMARY NODE", q.os_queue "PRIMARY SYSTEM QUEUE",
q.node_name2 "2ND NODE", q.os_queue2 "2ND SYSTEM QUEUE"
from fnd_concurrent_queues_vl q, fnd_product_installations i, fnd_application_vl a,
fnd_concurrent_time_periods p, fnd_concurrent_queue_size qs
where i.application_id = q.application_id
and a.application_id = q.application_id
and qs.queue_application_id = q.application_id
and qs.concurrent_queue_id = q.concurrent_queue_id
and qs.period_application_id = p.application_id
and qs.concurrent_time_period_id = p.concurrent_time_period_id
and nvl(q.control_code,'X') <> 'E'
and q.CONCURRENT_QUEUE_NAME = 'STANDARD'
order by q.concurrent_queue_name, p.concurrent_time_period_id
Number of rows:
1 rows selected
Elapsed time:
.001 Sec
MANAGER
SHORT NAME
ENABLED
APPLICATION
DESCRIPTION
TYPE
DATA GROUP
CONSUMER GROUP
CACHE SIZE
PRIMARY NODE
PRIMARY SYSTEM QUEUE
2ND NODE
2ND SYSTEM QUEUE
Standard Manager
STANDARD
Y
Application Object Library
Standard queue for handling requests
Concurrent Manager
2
OP11ZAS4
All checks passed.
Standard Manager Cache Size is set to 2.
According to the "Oracle Applications System Administrator's Guide - Configuration" Defining Concurrent Managers section :
Release 12 (Part No. B31453-04)
Release 11i (Part No. B13925-06)
"Cache Size (Concurrent Manager only)
Enter the number of requests your manager remembers each time it reads which requests to run. For example, if a manager's workshift has 1 target process and a cache value of 3, it will read three requests and wait until these three requests have been run before reading new requests.
In reading requests, the manager will only put requests it is allowed to run into its cache. For example, if you have defined your manager to run only Order Entry reports then the manager will put only Order Entry requests into its cache.
If you enter 1, the concurrent manager must look at its requests list each time it is ready to process another request.
There are no Run Alone Programs defined in this ::VAR::g_rep_info('Instance'):: instance.
It is possible to define a concurrent program to be run-alone or to be incompatible with specific
concurrent programs by editing the concurrent program's definition using the
Concurrent Programs window.
Program incompatibility and run-alone program definitions are enforced by the Conflict Resolution Manager (CRM).
For more details see: Oracle® E-Business Suite System Administrator's Guide - Configuration - Concurrent Programs, page 6-65.
Home
Execution Details
Parameters
Feedback
Data View
Full View
