Concurrent Processing - CP Analyzer Sample SQL Statements Including Date Range Parameters from Doc
1411723.1- Concurrent Processing -
CP Analyzer for E-Business Suite (Doc ID 1499538.1)/:
===========================================================
Click to add to Favorites Concurrent Processing - CP Analyzer Sample SQL Statements Including Date Range Parameters from Doc 1411723.1- Concurrent Processing - CP Analyzer for E-Business Suite (Doc ID 1499538.1) To BottomTo Bottom
In this Document
Purpose
Troubleshooting Steps
Script Provides Daily Concurrent Request Counts Completed by Hour
Script Provides the Concurrent Requests Counts, Time, and Range
Script Provides the Count by Name and Date
Script Provides the Count of Requests Completed and Canceled
Script Provides the Count of Requests Completed with Error
Script Provides the Count of Requests Pending and Scheduled
Script Provides the Count of Requests Terminated by Date
Script Provides the Count of Requests by Date
Quick Sql Statement to check Average Monthly Requests
Script Identifying Concurrent Managers Used and Compare
Elapsed Time History for All Requests Run for a Given Time Period
Select for Providing Frequency/Volume Analysis Program Name, Queue Name, and Count by Program Name and Date Range
Request Volume Per Day Select which can be Modified with Additions to the Where Clause
Select Providing the Volume of Concurrent Requests Per Node
Select reflecting All Requests with Run Times > 30 Minutes During Peak Load in the Past Month by Application_Id
Select Providing Request ID's for Requests that are Currently Running
Session Information Available from an Oracle Unix Process ID - SPID
Tracing Concurrent Processes
Recommended Concurrent Processing Resources
References
APPLIES TO:
Oracle Concurrent Processing - Version 11.5.10.2 to 12.2 [Release 11.5 to 12.2]
Information in this document applies to any platform.
PURPOSE
This document is reference document for Document 1411723.1 the EBS Concurrent Processing Analyzer.
The details in this document provides advanced sql query statements allowing select date ranges or other sql queries for monitoring Concurrent Processing. The scripts are useful for specific Concurrent Processing (monitoring / problem isolation) needs as referenced below.
NOTE: Modification of the scripts for your use made at your own risk. Please feel free modify the scripts for use as needed as they are not controlled by Oracle Support.
TROUBLESHOOTING STEPS
NOTE: Example execution for the scripts referenced below:
SQL>sqlplus apps/<apps_password> @<script.sql>
Script Provides Daily Concurrent Request Counts Completed by Hour
REM #########################################################################
REM ## Purpose: Script provides daily Concurrent Requests counts completed by hour
REM ## Filename: daily_request_count_completed_hourly.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @daily_request_count_completed_hourly.sql
REM ## Output: ConcurrentRequestCount.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column st_dt format date
spool ConcurrentRequestCount.lst
select
p.user_concurrent_program_name Program_name,
to_char(r.actual_start_date,'DD-MON-YYYY HH24') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id = '&appl_id'
and r.status_code = 'C'
and r.phase_code = 'C'
and to_char(r.actual_start_date,'DD-MON-YYYY') between '&begin_date' and '&end_date'
group by p.user_concurrent_program_name,to_char(r.actual_start_date,'DD-MON-YYYY HH24')
order by to_char(r.actual_start_date,'DD-MON-YYYY HH24') asc
/
spool off
Click To Download dlyrequctcomped.sql
Script Provides the Concurrent Requests Counts, Time, and Range
REM #########################################################################
REM ## Purpose: Script provides the Concurrent Requests counts, Time, and Range
REM ## Filename: Node_Request_Count_TimeRange.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @Node_Request_Count_TimeRange.sql
REM ## Output: Node_Request_Count_TimeRange.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
break on Date_of_exec
compute sum of Executions on Date_of_exec
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column Manager_name format a10
column Node_Name format a11
spool NodeConcurrentRequestCountTimeRange.lst
select
p.user_concurrent_program_name Program_name,
q.concurrent_queue_name Manager_name,
q.NODE_NAME Node_Name,
to_char(r.actual_start_date,'DD-MON-YYYY HH24') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id = '&appl_id'
and actual_start_date between to_date('&&begin_date','DD-MON-YYYY HH24') and to_date('&&end_date','DD-MON-YYYY HH24')
and r.status_code = 'C'
and r.phase_code in ('C')
group by p.user_concurrent_program_name,to_char(r.actual_start_date,'DD-MON-YYYY HH24'),q.concurrent_queue_name,q.node_name
order by to_char(r.actual_start_date,'DD-MON-YYYY HH24') asc
/
spool off
Click To Download reqcntimerange.sql
Script Provides the Count by Name and Date
REM #########################################################################
REM ## Purpose: Script provides the count by Name and Date
REM ## Filename: program_queue_count_byname_date.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @program_queue_count_byname_date.sql
REM ## Output: program_queue_count_byname_date.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
spool program_queue_count_byname_date.lst
select n.user_concurrent_program_name Program_name,
q.concurrent_queue_name,
count(*) cnt,
sum(r.actual_completion_date - r.actual_start_date) * 24 elapsed,
avg(r.actual_completion_date - r.actual_start_date) * 24 average,
stddev(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 wstddev,
sum(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 waited,
avg(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 avewait
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_queues q,
apps.fnd_concurrent_processes p,
apps.fnd_concurrent_programs_vl n
where
n.concurrent_program_id = r.concurrent_program_id
and n.application_id = r.program_application_id
and r.concurrent_program_id = p.concurrent_program_id
and r.phase_code='C'
and r.status_code in ('C','G')
and r.controlling_manager=p.concurrent_process_id
and q.concurrent_queue_id=p.concurrent_queue_id
and n.user_concurrent_program_name like '&prog_name'
and to_char(r.actual_completion_date,'DD-MON-YYYY') between '&begin_date' and '&end_date'
group by q.concurrent_queue_name,n.user_concurrent_program_name
order by 4;
spool off
Click To Download prgquect_namdate.sql
Script Provides the Count of Requests Completed and Canceled
REM #########################################################################
REM ## Purpose: Script provides the count of requests completed and canceled.
REM ## Filename: request_count_completedcancelled.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @request_count_completedcancelled.sql
REM ## Output: request_count_completedcancelled.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
break on report
compute sum of Executions on report
set pages 10000
set lines 180
set verify off
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column st_dt format date
spool completedcancelledrequestcount.lst
select
p.user_concurrent_program_name Program_name,
to_date(r.actual_start_date,'DD-MON-YYYY') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id = '&appl_id'
and r.status_code = 'D'
and r.phase_code = 'C'
and to_char(r.actual_start_date,'DD-MON-YYYY')='&begin_date'
group by p.user_concurrent_program_name,to_date(r.actual_start_date,'DD-MON-YYYY')
order by to_date(r.actual_start_date,'DD-MON-YYYY') asc
/
spool off
Click To Download reqcntcomcanc.sql
Script Provides the Count of Requests Completed with Error
REM #########################################################################
REM ## Purpose: Script provides the count of requests completed with error.
REM ## Filename: request_count_completederror.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @request_count_completederror.sql
REM ## Output: completederrorRequestCount.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
break on report
compute sum of Executions on report
set pages 10000
set lines 180
set verify off
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column st_dt format date
spool completederrorRequestCount.lst
select
p.user_concurrent_program_name Program_name,
to_date(r.actual_start_date,'DD-MON-YYYY') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id = '&appl_id'
and r.status_code = 'E'
and r.phase_code = 'C'
and to_char(r.actual_start_date,'DD-MON-YYYY')='&begin_date'
group by p.user_concurrent_program_name,to_date(r.actual_start_date,'DD-MON-YYYY')
order by to_date(r.actual_start_date,'DD-MON-YYYY') asc
/
spool off
Click To Download reqctcomperror.sql
Script Provides the Count of Requests Pending and Scheduled
break on report
compute sum of Executions on report
set pages 10000
set lines 180
set verify off
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column st_dt format date
spool pendingscheduledRequestCount.lst
select
p.user_concurrent_program_name Program_name
to_date(r.actual_start_date,'DD-MON-YYYY') Date_of_exec
count(r.request_id) Executions
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.fnd_concurrent_programs_vl p
where
p.application_id = r.program_application_id
and
r.phase_code = 'P'
/
spool off
Click To Download reqctpenschd.sql
Script Provides the Count of Requests Terminated by Date
REM #########################################################################
REM ## Purpose: Script provides the count of requests terminated by date.
REM ## Filename: running_terminated_requests_bydate.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @running_terminated_requests_bydate.sql
REM ## Output: runningterminatedRequestCount.lst
REM ##
REM #########################################################################
break on report
compute sum of Executions on report
set pages 10000
set lines 180
set verify off
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column st_dt format date
spool runningterminatedRequestCount.lst
select
p.user_concurrent_program_name Program_name,
to_date(r.actual_start_date,'DD-MON-YYYY') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id = '&appl_id'
and r.status_code = 'T'
and r.phase_code = 'R'
and to_char(r.actual_start_date,'DD-MON-YYYY')='&begin_date'
group by p.user_concurrent_program_name,to_date(r.actual_start_date,'DD-MON-YYYY')
order by to_date(r.actual_start_date,'DD-MON-YYYY') asc
/
spool off
Click To Download run_term_reqbydt.sql
Script Provides the Count of Requests by Date
REM #########################################################################
REM ## Purpose: Script provides the count of requests by date.
REM ## Filename: startdate_count_bydate.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @startdate_count_bydate.sql
REM ## Output: startdate_count_bydate.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
spool startdate_count_bydate.lst
select to_char(actual_start_date,'DD-MON-YYYY HH24'),count(request_id)
from apps.fnd_concurrent_requests
where actual_start_date between to_date('&&begin_date','DD-MON-YYYY HH24')
and to_date('&&end_date','DD-MON-YYYY HH24') and status_code = 'C'
and phase_code in ('C')
group by to_char(actual_start_date,'DD-MON-YYYY HH24')
/
spool off
Click To Download strtdat_ct_bydat.sql
Quick Sql Statement to check Average Monthly Requests
set head off
select avg(count(1)) from apps.fnd_concurrent_requests
where trunc(actual_start_date) >trunc(sysdate -30) group by trunc(actual_start_date);
Click To Download avg_mon_reqs.sql
Script Identifying Concurrent Managers Used and Compare
REM cmhist.sql
REM Use this report to tell which concurrent managers are actually being
REM used in an environment and compare to the concurrent managers that
REM are allocated processes at startup. This report only reviews completed
REM normal or completed warning concurrent requests.
REM
REM Deactivate any managers that are not used.
REM
REM It makes no sense to run this request with a date range greater than
REM what is contained in the fnd_concurrent_requests table. Therefore,
REM base dates on the purge frequency.
REM
set verify off
set feedback off
set pagesize 59
set linesize 180
set newpage 02
set recsep off
break on report
compute sum of cnt on report
compute sum of elapsed on report
compute sum of waited on report
rem column today new_value _date noprint;
rem select to_char(SYSDATE,'DD-MON-YY') today from dual;
rem ttitle "Concurrent Manager Request Summary by Manager from: &&begin_date to &&end_date" skip 2;
column waited format 99999999.99 heading 'WAITED|HOURS';
column wstddev format 99999999.99 heading 'WAITED|STDDEV';
column avewait format 9999.99 heading 'AVG.|WAIT';
column concurrent_queue_name format a30 wrap;
column cnt format 999,999 heading 'COUNT';
column elapsed format 99999.99 heading 'TOTAL|HOURS';
column average format 9999.99 heading 'AVG.|HOURS';
set feedback off
select q.concurrent_queue_name,
count(*) cnt,
sum(r.actual_completion_date - r.actual_start_date) * 24 elapsed,
avg(r.actual_completion_date - r.actual_start_date) * 24 average,
stddev(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 wstddev,
sum(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 waited,
avg(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 avewait
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_queues q,
apps.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
and actual_start_date between to_date('&&begin_date') and to_date('&&end_date')
group by q.concurrent_queue_name
order by 4;
set feedback on;
clear breaks
Click To Download cm_useagedtange.sql
Elapsed Time History for All Requests Run for a Given Time Period
REM
REM
REM Elapsed time history for all requests run for a given time period.
REM
REM To help assign requests that always run fast to one queue, and slow requests to a slow queue
REM
REM
rem set timing off
rem set heading off
set verify off
rem set show off
rem set echo off
set feedback off
set pagesize 500
set linesize 250
column elapsed format 999.99 heading 'TOTAL|HOURS';
column average format 99.99 heading 'AVG|HOURS';
column max format 99.99 heading 'MAX|HOURS';
column min format 99.99 heading 'MIN|HOURS';
column waited format 99999.99 heading '#WAITED|HOURS';
column avewait format 9999.99 heading 'AVG|WAIT';
column program format a20 heading 'PROGRAM';
column description format a55;
column cnt format 999,999 heading '#TIMES|RUN';
column app format a6 heading 'APP';
break on report
compute sum of cnt on report
compute sum of elapsed on report
compute sum of waited on report
column stddev format 999.99 heading 'RUN|STDDEV';
column wstddev format 999.99 heading 'WAIT|STDDEV'
column type format a15 heading 'TYPE'
column priority format 999 heading 'PRI'
column concurrent_queue_name format a14 heading 'Concurrent Mgr' trunc
select
f.application_short_name app,
substr(p.user_concurrent_program_name,1,55) description,
substr(p.concurrent_program_name,1,20) program,
r.priority,
count(*) cnt,
sum(actual_completion_date - actual_start_date) * 24 elapsed,
avg(actual_completion_date - actual_start_date) * 24 average,
max(actual_completion_date - actual_start_date) * 24 max,
min(actual_completion_date - actual_start_date) * 24 min,
stddev(actual_completion_date - actual_start_date) * 24 stddev,
stddev(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 wstddev,
sum(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 waited,
avg(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 avewait,
c.request_class_name type
from apps.fnd_concurrent_request_class c,
apps.fnd_application f,
apps.fnd_concurrent_programs_vl p,
apps.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') -- maybe 'E' also
and r.phase_code = 'C'
and actual_completion_date between to_date('&&begindate') and to_date('$$enddate')
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
/
clear breaks
Click To Download reqelaptimhist.sql
Select for Providing Frequency/Volume Analysis Program Name, Queue Name, and Count by Program Name and Date Range
REM select statement to pull Program Name, Queue Name, and Count by program name and date range for frequency/volume analysis
REM
select n.user_concurrent_program_name Program_name,
q.concurrent_queue_name,
count(*) cnt,
sum(r.actual_completion_date - r.actual_start_date) * 24 elapsed,
avg(r.actual_completion_date - r.actual_start_date) * 24 average,
stddev(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 wstddev,
sum(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 waited,
avg(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 avewait
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_queues q,
apps.fnd_concurrent_processes p,
apps.fnd_concurrent_programs_vl n
where
n.concurrent_program_id = r.concurrent_program_id
and n.application_id = r.program_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 n.user_concurrent_program_name like '&1'
and to_char(r.actual_completion_date,'DD-MON-YYYY') between '&2' and '&3'
group by q.concurrent_queue_name,n.user_concurrent_program_name
order by 4;
set feedback on;
clear breaks
Click To Download reqnamdatprog.sql
Request Volume Per Day Select which can be Modified with Additions to the Where Clause
REM Request Volume Per Day Select which can be modified with additions to the where clause
REM
set pages 1000
set lines 180
select trunc(actual_start_date),count(*)
from apps.fnd_concurrent_requests
where trunc(Actual_start_date) >trunc(sysdate -61)
group by trunc(actual_start_date);
Click To Download req_vol_perday.sql
Select Providing the Volume of Concurrent Requests Per Node
REM Concurrent Requests per node select statement
REM
set head off
Break on trunc(actual_start_date)
compute SUM of count(1) on trunc(actual_start_date);
select count(1),trunc(actual_start_date) ,logfile_node_name from apps.fnd_concurrent_requests
where trunc(actual_start_date) >trunc(sysdate -30) group by trunc(actual_start_date),logfile_node_name order by trunc(actual_start_date),count(1);
Click To Download req_by_node.sql
Select reflecting All Requests with Run Times > 30 Minutes During Peak Load in the Past Month by Application_Id
rem Script: Requests_Over_30Min_by_App_Id.sql
rem All requests with run times > 30 minutes during peak load in the past month by Application_Id.
undefine start_date
undefine end_date
set pages 10000
set verify off
column request_id format 99999999 heading 'REQUEST'
column user_name format a17
column phase format a10
column status format a12
column start_date format a5
column completion_date format a5 heading 'END'
column avg_run_time format 9999 heading 'AVG TIME'
column min_run_time format 9999 heading 'MIN TIME'
column max_run_time format 9999 heading 'MAX TIME'
column program_name format a50
select
p.user_concurrent_program_name program_name,
count(r.request_id),
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id >= &&ApplicationId
and r.actual_start_date >= sysdate-31
and r.status_code = 'C'
and r.phase_code in ('C')
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
/
Click To Download reqs_over_30min.sql
Select Providing Request ID's for Requests that are Currently Running
alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
SEt linesize 200
set pagesize 2000
col "Secondswait" format 9999 wrap heading 'SWait' print
col "WaitEvent" format a12 wrap heading 'Waiting|On Event' print
col "Session Wait" format a12 wrap heading 'file#|block#|id(Reason)' print
Column Manager Format A12 trunc heading 'Manager|Queue' print
col "ServerClient" format a7 heading 'Server|Client' print
col "SidSerialSQLHash" format a10 heading 'Sid|Serial#|SQLHash' print
col "DBPhaseStatusCODEUser" format a10 heading 'UserName|CP PhSCode|Db Status' print
col Request_id Format a10 heading 'RequestID|PriorityID|ParentID' print wrap
col "RequestStartDate" Format a10 heading 'ReqStart|Minutes' print
column concurrent_program_name format a25 heading 'CCM Short|Name' noprint
column user_concurrent_program_name format a20 wrap heading 'Concurrent|Program Name' print
column ARGUMENT_TEXT format a15 heading 'CCM Arguments' print
column "SQLHASH" format 9999999999 heading 'SQL|HASH' print
column CONCURRENT_QUEUE_ID format 99999 heading 'CCM |Queue ID' print
column QUEUE_DESCRIPTION format a20 heading 'CCM |Queue Name' noprint
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,
requested_start_Date||chr(10)||round((sysdate- requested_start_date)*1440, 2)||'M' "RequestStartDate",
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
-- nvl(request_type, 'X') != 'S' and
-- (request_id = &&request_id)
(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
;
--and request_id=&&request_id;
Click To Download req_run_by_reqid.sql
Session Information Available from an Oracle Unix Process ID - SPID
REM FILENAME spid_session_k.sql
REM DESCRIPTION
REM Session information availabe from an Oracle unix process id - SPID
set verify off
set echo off
set head off
set pages 1000
PROMPT Enter SPID :
ACCEPT 1
spool spid&1
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT Details of SPID &1
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select 'SID............ : ' || a.sid || chr(10) ||
'SERIAL#........ : ' || a.serial# || chr(10) ||
'USERNAME....... : ' || a.username || chr(10) ||
'COMMAND........ : ' || a.command || chr(10) ||
'STATUS......... : ' || a.status || chr(10) ||
'Machine........ : ' || a.machine || chr(10) ||
'Terminal....... : ' || a.terminal || chr(10) ||
'Program........ : ' || a.program || chr(10) ||
'Module........ : ' || a.module || chr(10) ||
'SQL Hash Value. : ' || a.sql_hash_value || chr(10) ||
'Logon Time..... : ' || to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS') || chr(10) ||
'Last Call Et... : ' || a.last_call_et || chr(10) ||
'Process ID..... : ' || a.process || chr(10) ||
'SPID........... : ' || b.spid
from v$session a, v$process b
where a.paddr=b.addr and b.spid='&1';
PROMPT
select 'Session Waiting for event...: ' || event
from v$session_wait
where sid in(select sid from v$session a, v$process b where a.paddr=b.addr and b.spid='&1');
PROMPT SQL STATEMENT :
PROMPT ===============
select sql_text
from v$sqltext
where hash_value in(select sql_hash_value from v$session a, v$process b where a.paddr=b.addr and b.spid='&1')
order by piece;
spool off
Click To Download sess_info_spid.sql
Tracing Concurrent Processes
For example the following unix process id was consuming high CPU 25807.
1. Obtain the Oracle process identifier or the Operating System process
identifier (SPID) from v$process:
SQL> sqlplus /nolog
connect / as sysdba
select pid, spid, username from v$process;
PID SPID USERNAME
---- ----- --------
8 25807 oracle
2. Attach to the process using ORADEBUG.
I. Using the Oracle process identifier:
SQL> oradebug setorapid 8
Unix process pid: 25807, image: oracle
II. Using the Operating System process identifier:
SQL> oradebug setospid 25807
Oracle pid: 8, Unix process pid: 25807, image: oracle
3. Turn on SQL Trace for the session and the trace file name as follows:
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug tracefile_name;
4. Turn off the SQL trace for the session.
SQL> oradebug event 10046 trace name context off
5. Format trace file using TKPROF.
tkprof CR2000000.trc CR0000000.out explain=apps/simple4u sort='(prsela,exeela,fchela)' print=10
This listed the sql which was consuming CPU.
Recommended Concurrent Processing Resources
Review best practice recommendations in the Concurrent Processing - Product Information Center (PIC) Document 1304305.1
Collaborate with industry peers in the Core Concurrent Processing Community
Please see Document 1186338.1 for recorded Concurrent Processing Webcasts.
1411723.1- Concurrent Processing -
CP Analyzer for E-Business Suite (Doc ID 1499538.1)/:
===========================================================
Click to add to Favorites Concurrent Processing - CP Analyzer Sample SQL Statements Including Date Range Parameters from Doc 1411723.1- Concurrent Processing - CP Analyzer for E-Business Suite (Doc ID 1499538.1) To BottomTo Bottom
In this Document
Purpose
Troubleshooting Steps
Script Provides Daily Concurrent Request Counts Completed by Hour
Script Provides the Concurrent Requests Counts, Time, and Range
Script Provides the Count by Name and Date
Script Provides the Count of Requests Completed and Canceled
Script Provides the Count of Requests Completed with Error
Script Provides the Count of Requests Pending and Scheduled
Script Provides the Count of Requests Terminated by Date
Script Provides the Count of Requests by Date
Quick Sql Statement to check Average Monthly Requests
Script Identifying Concurrent Managers Used and Compare
Elapsed Time History for All Requests Run for a Given Time Period
Select for Providing Frequency/Volume Analysis Program Name, Queue Name, and Count by Program Name and Date Range
Request Volume Per Day Select which can be Modified with Additions to the Where Clause
Select Providing the Volume of Concurrent Requests Per Node
Select reflecting All Requests with Run Times > 30 Minutes During Peak Load in the Past Month by Application_Id
Select Providing Request ID's for Requests that are Currently Running
Session Information Available from an Oracle Unix Process ID - SPID
Tracing Concurrent Processes
Recommended Concurrent Processing Resources
References
APPLIES TO:
Oracle Concurrent Processing - Version 11.5.10.2 to 12.2 [Release 11.5 to 12.2]
Information in this document applies to any platform.
PURPOSE
This document is reference document for Document 1411723.1 the EBS Concurrent Processing Analyzer.
The details in this document provides advanced sql query statements allowing select date ranges or other sql queries for monitoring Concurrent Processing. The scripts are useful for specific Concurrent Processing (monitoring / problem isolation) needs as referenced below.
NOTE: Modification of the scripts for your use made at your own risk. Please feel free modify the scripts for use as needed as they are not controlled by Oracle Support.
TROUBLESHOOTING STEPS
NOTE: Example execution for the scripts referenced below:
SQL>sqlplus apps/<apps_password> @<script.sql>
Script Provides Daily Concurrent Request Counts Completed by Hour
REM #########################################################################
REM ## Purpose: Script provides daily Concurrent Requests counts completed by hour
REM ## Filename: daily_request_count_completed_hourly.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @daily_request_count_completed_hourly.sql
REM ## Output: ConcurrentRequestCount.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column st_dt format date
spool ConcurrentRequestCount.lst
select
p.user_concurrent_program_name Program_name,
to_char(r.actual_start_date,'DD-MON-YYYY HH24') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id = '&appl_id'
and r.status_code = 'C'
and r.phase_code = 'C'
and to_char(r.actual_start_date,'DD-MON-YYYY') between '&begin_date' and '&end_date'
group by p.user_concurrent_program_name,to_char(r.actual_start_date,'DD-MON-YYYY HH24')
order by to_char(r.actual_start_date,'DD-MON-YYYY HH24') asc
/
spool off
Click To Download dlyrequctcomped.sql
Script Provides the Concurrent Requests Counts, Time, and Range
REM #########################################################################
REM ## Purpose: Script provides the Concurrent Requests counts, Time, and Range
REM ## Filename: Node_Request_Count_TimeRange.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @Node_Request_Count_TimeRange.sql
REM ## Output: Node_Request_Count_TimeRange.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
break on Date_of_exec
compute sum of Executions on Date_of_exec
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column Manager_name format a10
column Node_Name format a11
spool NodeConcurrentRequestCountTimeRange.lst
select
p.user_concurrent_program_name Program_name,
q.concurrent_queue_name Manager_name,
q.NODE_NAME Node_Name,
to_char(r.actual_start_date,'DD-MON-YYYY HH24') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id = '&appl_id'
and actual_start_date between to_date('&&begin_date','DD-MON-YYYY HH24') and to_date('&&end_date','DD-MON-YYYY HH24')
and r.status_code = 'C'
and r.phase_code in ('C')
group by p.user_concurrent_program_name,to_char(r.actual_start_date,'DD-MON-YYYY HH24'),q.concurrent_queue_name,q.node_name
order by to_char(r.actual_start_date,'DD-MON-YYYY HH24') asc
/
spool off
Click To Download reqcntimerange.sql
Script Provides the Count by Name and Date
REM #########################################################################
REM ## Purpose: Script provides the count by Name and Date
REM ## Filename: program_queue_count_byname_date.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @program_queue_count_byname_date.sql
REM ## Output: program_queue_count_byname_date.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
spool program_queue_count_byname_date.lst
select n.user_concurrent_program_name Program_name,
q.concurrent_queue_name,
count(*) cnt,
sum(r.actual_completion_date - r.actual_start_date) * 24 elapsed,
avg(r.actual_completion_date - r.actual_start_date) * 24 average,
stddev(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 wstddev,
sum(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 waited,
avg(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 avewait
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_queues q,
apps.fnd_concurrent_processes p,
apps.fnd_concurrent_programs_vl n
where
n.concurrent_program_id = r.concurrent_program_id
and n.application_id = r.program_application_id
and r.concurrent_program_id = p.concurrent_program_id
and r.phase_code='C'
and r.status_code in ('C','G')
and r.controlling_manager=p.concurrent_process_id
and q.concurrent_queue_id=p.concurrent_queue_id
and n.user_concurrent_program_name like '&prog_name'
and to_char(r.actual_completion_date,'DD-MON-YYYY') between '&begin_date' and '&end_date'
group by q.concurrent_queue_name,n.user_concurrent_program_name
order by 4;
spool off
Click To Download prgquect_namdate.sql
Script Provides the Count of Requests Completed and Canceled
REM #########################################################################
REM ## Purpose: Script provides the count of requests completed and canceled.
REM ## Filename: request_count_completedcancelled.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @request_count_completedcancelled.sql
REM ## Output: request_count_completedcancelled.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
break on report
compute sum of Executions on report
set pages 10000
set lines 180
set verify off
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column st_dt format date
spool completedcancelledrequestcount.lst
select
p.user_concurrent_program_name Program_name,
to_date(r.actual_start_date,'DD-MON-YYYY') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id = '&appl_id'
and r.status_code = 'D'
and r.phase_code = 'C'
and to_char(r.actual_start_date,'DD-MON-YYYY')='&begin_date'
group by p.user_concurrent_program_name,to_date(r.actual_start_date,'DD-MON-YYYY')
order by to_date(r.actual_start_date,'DD-MON-YYYY') asc
/
spool off
Click To Download reqcntcomcanc.sql
Script Provides the Count of Requests Completed with Error
REM #########################################################################
REM ## Purpose: Script provides the count of requests completed with error.
REM ## Filename: request_count_completederror.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @request_count_completederror.sql
REM ## Output: completederrorRequestCount.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
break on report
compute sum of Executions on report
set pages 10000
set lines 180
set verify off
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column st_dt format date
spool completederrorRequestCount.lst
select
p.user_concurrent_program_name Program_name,
to_date(r.actual_start_date,'DD-MON-YYYY') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id = '&appl_id'
and r.status_code = 'E'
and r.phase_code = 'C'
and to_char(r.actual_start_date,'DD-MON-YYYY')='&begin_date'
group by p.user_concurrent_program_name,to_date(r.actual_start_date,'DD-MON-YYYY')
order by to_date(r.actual_start_date,'DD-MON-YYYY') asc
/
spool off
Click To Download reqctcomperror.sql
Script Provides the Count of Requests Pending and Scheduled
break on report
compute sum of Executions on report
set pages 10000
set lines 180
set verify off
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column st_dt format date
spool pendingscheduledRequestCount.lst
select
p.user_concurrent_program_name Program_name
to_date(r.actual_start_date,'DD-MON-YYYY') Date_of_exec
count(r.request_id) Executions
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.fnd_concurrent_programs_vl p
where
p.application_id = r.program_application_id
and
r.phase_code = 'P'
/
spool off
Click To Download reqctpenschd.sql
Script Provides the Count of Requests Terminated by Date
REM #########################################################################
REM ## Purpose: Script provides the count of requests terminated by date.
REM ## Filename: running_terminated_requests_bydate.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @running_terminated_requests_bydate.sql
REM ## Output: runningterminatedRequestCount.lst
REM ##
REM #########################################################################
break on report
compute sum of Executions on report
set pages 10000
set lines 180
set verify off
column program_name format a50
column request_id format 99999999 heading 'REQUEST'
column st_dt format date
spool runningterminatedRequestCount.lst
select
p.user_concurrent_program_name Program_name,
to_date(r.actual_start_date,'DD-MON-YYYY') Date_of_exec,
count(r.request_id) Executions,
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) Max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id = '&appl_id'
and r.status_code = 'T'
and r.phase_code = 'R'
and to_char(r.actual_start_date,'DD-MON-YYYY')='&begin_date'
group by p.user_concurrent_program_name,to_date(r.actual_start_date,'DD-MON-YYYY')
order by to_date(r.actual_start_date,'DD-MON-YYYY') asc
/
spool off
Click To Download run_term_reqbydt.sql
Script Provides the Count of Requests by Date
REM #########################################################################
REM ## Purpose: Script provides the count of requests by date.
REM ## Filename: startdate_count_bydate.sql
REM ## Certified for Application releases 11.5 through 12.x
REM ## Document: <nnnnnn.1>
REM ## Usage: sqlplus apps/<appspwd> @startdate_count_bydate.sql
REM ## Output: startdate_count_bydate.lst
REM ##
REM #########################################################################
set pages 10000
set lines 180
set verify off
spool startdate_count_bydate.lst
select to_char(actual_start_date,'DD-MON-YYYY HH24'),count(request_id)
from apps.fnd_concurrent_requests
where actual_start_date between to_date('&&begin_date','DD-MON-YYYY HH24')
and to_date('&&end_date','DD-MON-YYYY HH24') and status_code = 'C'
and phase_code in ('C')
group by to_char(actual_start_date,'DD-MON-YYYY HH24')
/
spool off
Click To Download strtdat_ct_bydat.sql
Quick Sql Statement to check Average Monthly Requests
set head off
select avg(count(1)) from apps.fnd_concurrent_requests
where trunc(actual_start_date) >trunc(sysdate -30) group by trunc(actual_start_date);
Click To Download avg_mon_reqs.sql
Script Identifying Concurrent Managers Used and Compare
REM cmhist.sql
REM Use this report to tell which concurrent managers are actually being
REM used in an environment and compare to the concurrent managers that
REM are allocated processes at startup. This report only reviews completed
REM normal or completed warning concurrent requests.
REM
REM Deactivate any managers that are not used.
REM
REM It makes no sense to run this request with a date range greater than
REM what is contained in the fnd_concurrent_requests table. Therefore,
REM base dates on the purge frequency.
REM
set verify off
set feedback off
set pagesize 59
set linesize 180
set newpage 02
set recsep off
break on report
compute sum of cnt on report
compute sum of elapsed on report
compute sum of waited on report
rem column today new_value _date noprint;
rem select to_char(SYSDATE,'DD-MON-YY') today from dual;
rem ttitle "Concurrent Manager Request Summary by Manager from: &&begin_date to &&end_date" skip 2;
column waited format 99999999.99 heading 'WAITED|HOURS';
column wstddev format 99999999.99 heading 'WAITED|STDDEV';
column avewait format 9999.99 heading 'AVG.|WAIT';
column concurrent_queue_name format a30 wrap;
column cnt format 999,999 heading 'COUNT';
column elapsed format 99999.99 heading 'TOTAL|HOURS';
column average format 9999.99 heading 'AVG.|HOURS';
set feedback off
select q.concurrent_queue_name,
count(*) cnt,
sum(r.actual_completion_date - r.actual_start_date) * 24 elapsed,
avg(r.actual_completion_date - r.actual_start_date) * 24 average,
stddev(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 wstddev,
sum(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 waited,
avg(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 avewait
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_queues q,
apps.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
and actual_start_date between to_date('&&begin_date') and to_date('&&end_date')
group by q.concurrent_queue_name
order by 4;
set feedback on;
clear breaks
Click To Download cm_useagedtange.sql
Elapsed Time History for All Requests Run for a Given Time Period
REM
REM
REM Elapsed time history for all requests run for a given time period.
REM
REM To help assign requests that always run fast to one queue, and slow requests to a slow queue
REM
REM
rem set timing off
rem set heading off
set verify off
rem set show off
rem set echo off
set feedback off
set pagesize 500
set linesize 250
column elapsed format 999.99 heading 'TOTAL|HOURS';
column average format 99.99 heading 'AVG|HOURS';
column max format 99.99 heading 'MAX|HOURS';
column min format 99.99 heading 'MIN|HOURS';
column waited format 99999.99 heading '#WAITED|HOURS';
column avewait format 9999.99 heading 'AVG|WAIT';
column program format a20 heading 'PROGRAM';
column description format a55;
column cnt format 999,999 heading '#TIMES|RUN';
column app format a6 heading 'APP';
break on report
compute sum of cnt on report
compute sum of elapsed on report
compute sum of waited on report
column stddev format 999.99 heading 'RUN|STDDEV';
column wstddev format 999.99 heading 'WAIT|STDDEV'
column type format a15 heading 'TYPE'
column priority format 999 heading 'PRI'
column concurrent_queue_name format a14 heading 'Concurrent Mgr' trunc
select
f.application_short_name app,
substr(p.user_concurrent_program_name,1,55) description,
substr(p.concurrent_program_name,1,20) program,
r.priority,
count(*) cnt,
sum(actual_completion_date - actual_start_date) * 24 elapsed,
avg(actual_completion_date - actual_start_date) * 24 average,
max(actual_completion_date - actual_start_date) * 24 max,
min(actual_completion_date - actual_start_date) * 24 min,
stddev(actual_completion_date - actual_start_date) * 24 stddev,
stddev(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 wstddev,
sum(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 waited,
avg(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 avewait,
c.request_class_name type
from apps.fnd_concurrent_request_class c,
apps.fnd_application f,
apps.fnd_concurrent_programs_vl p,
apps.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') -- maybe 'E' also
and r.phase_code = 'C'
and actual_completion_date between to_date('&&begindate') and to_date('$$enddate')
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
/
clear breaks
Click To Download reqelaptimhist.sql
Select for Providing Frequency/Volume Analysis Program Name, Queue Name, and Count by Program Name and Date Range
REM select statement to pull Program Name, Queue Name, and Count by program name and date range for frequency/volume analysis
REM
select n.user_concurrent_program_name Program_name,
q.concurrent_queue_name,
count(*) cnt,
sum(r.actual_completion_date - r.actual_start_date) * 24 elapsed,
avg(r.actual_completion_date - r.actual_start_date) * 24 average,
stddev(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 wstddev,
sum(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 waited,
avg(actual_start_date - greatest(r.requested_start_date,r.request_date)) * 24 avewait
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_queues q,
apps.fnd_concurrent_processes p,
apps.fnd_concurrent_programs_vl n
where
n.concurrent_program_id = r.concurrent_program_id
and n.application_id = r.program_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 n.user_concurrent_program_name like '&1'
and to_char(r.actual_completion_date,'DD-MON-YYYY') between '&2' and '&3'
group by q.concurrent_queue_name,n.user_concurrent_program_name
order by 4;
set feedback on;
clear breaks
Click To Download reqnamdatprog.sql
Request Volume Per Day Select which can be Modified with Additions to the Where Clause
REM Request Volume Per Day Select which can be modified with additions to the where clause
REM
set pages 1000
set lines 180
select trunc(actual_start_date),count(*)
from apps.fnd_concurrent_requests
where trunc(Actual_start_date) >trunc(sysdate -61)
group by trunc(actual_start_date);
Click To Download req_vol_perday.sql
Select Providing the Volume of Concurrent Requests Per Node
REM Concurrent Requests per node select statement
REM
set head off
Break on trunc(actual_start_date)
compute SUM of count(1) on trunc(actual_start_date);
select count(1),trunc(actual_start_date) ,logfile_node_name from apps.fnd_concurrent_requests
where trunc(actual_start_date) >trunc(sysdate -30) group by trunc(actual_start_date),logfile_node_name order by trunc(actual_start_date),count(1);
Click To Download req_by_node.sql
Select reflecting All Requests with Run Times > 30 Minutes During Peak Load in the Past Month by Application_Id
rem Script: Requests_Over_30Min_by_App_Id.sql
rem All requests with run times > 30 minutes during peak load in the past month by Application_Id.
undefine start_date
undefine end_date
set pages 10000
set verify off
column request_id format 99999999 heading 'REQUEST'
column user_name format a17
column phase format a10
column status format a12
column start_date format a5
column completion_date format a5 heading 'END'
column avg_run_time format 9999 heading 'AVG TIME'
column min_run_time format 9999 heading 'MIN TIME'
column max_run_time format 9999 heading 'MAX TIME'
column program_name format a50
select
p.user_concurrent_program_name program_name,
count(r.request_id),
avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) avg_run_time,
min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) min_run_time,
max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) max_run_time
from
apps.fnd_concurrent_requests r,
apps.fnd_concurrent_processes c,
apps.fnd_concurrent_queues q,
apps.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 p.application_id >= &&ApplicationId
and r.actual_start_date >= sysdate-31
and r.status_code = 'C'
and r.phase_code in ('C')
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
/
Click To Download reqs_over_30min.sql
Select Providing Request ID's for Requests that are Currently Running
alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
SEt linesize 200
set pagesize 2000
col "Secondswait" format 9999 wrap heading 'SWait' print
col "WaitEvent" format a12 wrap heading 'Waiting|On Event' print
col "Session Wait" format a12 wrap heading 'file#|block#|id(Reason)' print
Column Manager Format A12 trunc heading 'Manager|Queue' print
col "ServerClient" format a7 heading 'Server|Client' print
col "SidSerialSQLHash" format a10 heading 'Sid|Serial#|SQLHash' print
col "DBPhaseStatusCODEUser" format a10 heading 'UserName|CP PhSCode|Db Status' print
col Request_id Format a10 heading 'RequestID|PriorityID|ParentID' print wrap
col "RequestStartDate" Format a10 heading 'ReqStart|Minutes' print
column concurrent_program_name format a25 heading 'CCM Short|Name' noprint
column user_concurrent_program_name format a20 wrap heading 'Concurrent|Program Name' print
column ARGUMENT_TEXT format a15 heading 'CCM Arguments' print
column "SQLHASH" format 9999999999 heading 'SQL|HASH' print
column CONCURRENT_QUEUE_ID format 99999 heading 'CCM |Queue ID' print
column QUEUE_DESCRIPTION format a20 heading 'CCM |Queue Name' noprint
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,
requested_start_Date||chr(10)||round((sysdate- requested_start_date)*1440, 2)||'M' "RequestStartDate",
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
-- nvl(request_type, 'X') != 'S' and
-- (request_id = &&request_id)
(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
;
--and request_id=&&request_id;
Click To Download req_run_by_reqid.sql
Session Information Available from an Oracle Unix Process ID - SPID
REM FILENAME spid_session_k.sql
REM DESCRIPTION
REM Session information availabe from an Oracle unix process id - SPID
set verify off
set echo off
set head off
set pages 1000
PROMPT Enter SPID :
ACCEPT 1
spool spid&1
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT Details of SPID &1
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select 'SID............ : ' || a.sid || chr(10) ||
'SERIAL#........ : ' || a.serial# || chr(10) ||
'USERNAME....... : ' || a.username || chr(10) ||
'COMMAND........ : ' || a.command || chr(10) ||
'STATUS......... : ' || a.status || chr(10) ||
'Machine........ : ' || a.machine || chr(10) ||
'Terminal....... : ' || a.terminal || chr(10) ||
'Program........ : ' || a.program || chr(10) ||
'Module........ : ' || a.module || chr(10) ||
'SQL Hash Value. : ' || a.sql_hash_value || chr(10) ||
'Logon Time..... : ' || to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS') || chr(10) ||
'Last Call Et... : ' || a.last_call_et || chr(10) ||
'Process ID..... : ' || a.process || chr(10) ||
'SPID........... : ' || b.spid
from v$session a, v$process b
where a.paddr=b.addr and b.spid='&1';
PROMPT
select 'Session Waiting for event...: ' || event
from v$session_wait
where sid in(select sid from v$session a, v$process b where a.paddr=b.addr and b.spid='&1');
PROMPT SQL STATEMENT :
PROMPT ===============
select sql_text
from v$sqltext
where hash_value in(select sql_hash_value from v$session a, v$process b where a.paddr=b.addr and b.spid='&1')
order by piece;
spool off
Click To Download sess_info_spid.sql
Tracing Concurrent Processes
For example the following unix process id was consuming high CPU 25807.
1. Obtain the Oracle process identifier or the Operating System process
identifier (SPID) from v$process:
SQL> sqlplus /nolog
connect / as sysdba
select pid, spid, username from v$process;
PID SPID USERNAME
---- ----- --------
8 25807 oracle
2. Attach to the process using ORADEBUG.
I. Using the Oracle process identifier:
SQL> oradebug setorapid 8
Unix process pid: 25807, image: oracle
II. Using the Operating System process identifier:
SQL> oradebug setospid 25807
Oracle pid: 8, Unix process pid: 25807, image: oracle
3. Turn on SQL Trace for the session and the trace file name as follows:
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug tracefile_name;
4. Turn off the SQL trace for the session.
SQL> oradebug event 10046 trace name context off
5. Format trace file using TKPROF.
tkprof CR2000000.trc CR0000000.out explain=apps/simple4u sort='(prsela,exeela,fchela)' print=10
This listed the sql which was consuming CPU.
Recommended Concurrent Processing Resources
Review best practice recommendations in the Concurrent Processing - Product Information Center (PIC) Document 1304305.1
Collaborate with industry peers in the Core Concurrent Processing Community
Please see Document 1186338.1 for recorded Concurrent Processing Webcasts.
No comments:
Post a Comment