Enterprise Resource Planning and Integrations BlogSpot

Skip to content
Long running Concurrent Requests
by Yogesh Pachpute April 24, 2017 Concurrent Processing in R12
Source: http://shaikoracleappsdba.blogspot.in/2013/09/long-running-concurrent-
requests.html

1.How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100

select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = ‘&conc_reqid’;
=========================================================================

Long Running Concurrent Request In Oracle :

Looking on how to check long running concurrent request in Oracle Apps 11i or R12?
Here’s the overview of the SQL query script to detect the session information of
each program.

First you need to get the listing of running concurrent request in Oracle Apps 11i
or R12. You can use the SQL query script as below to obtain the list of running
request.

SELECT a.request_id
,a.oracle_process_id “SPID”
,frt.responsibility_name
,c.concurrent_program_name || ‘: ‘ || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440
avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || ” = ‘C’) avg_mins
,round((actual_completion_date – requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = ‘R’
AND a.status_code = ‘R’
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC

You can see the request id and other relevant information from the result.

Based on the SPID associated to each running request, query the v$session
orv$session_longops table to see what is the request id doing in the backend.

SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,

b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state,
c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = ‘ACTIVE’
AND a.spid = ‘11696’
ORDER BY a.spid, c.piece

Replace v$session with gv$session if the database is running on RAC environment.

Enable or set trace if you wish to know more details on the session.

====================================================================

2.Concurrent manager status for a given sid?

col MODULE for a20

col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username,
s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid =
‘&oracle_sid’;

3. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests

where
ORACLE_PROCESS_ID=’&a’;

4.To find sid,serial# for a given concurrent request id?

set lines 200

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = ‘R’;

5.To find concurrent program name,phase code,status code for a given request id?
SELECT request_id, user_concurrent_program_name,
DECODE(phase_code,’C’,’Completed’,phase_code)
phase_code, DECODE(status_code,’D’, ‘Cancelled’ ,
‘E’, ‘Error’ , ‘G’, ‘Warning’, ‘H’,’On Hold’ , ‘T’, ‘Terminating’, ‘M’, ‘No
Manager’ , ‘X’,
‘Terminated’, ‘C’, ‘Normal’, status_code) status_code,
to_char(actual_start_date,’dd-mon-
yy:hh24:mi:ss’) Start_Date, to_char(actual_completion_date,’dd-mon-yy:hh24:mi:ss’),
completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = ‘&req_id’ ORDER
BY 6 DESC;

6.To find the sql query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where

ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid=’&oracle_sid’
/

7. To find child requests

set lines 200

col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name,
DECODE
(sum.phase_code,’C’,’Completed’,sum.phase_code) phase_code,
DECODE(sum.status_code,’D’,
‘Cancelled’ ,
‘E’, ‘Error’ , ‘G’, ‘Warning’, ‘H’,’On Hold’ , ‘T’, ‘Terminating’, ‘M’, ‘No
Manager’ , ‘X’,
‘Terminated’, ‘C’, ‘Normal’, sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v
sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and
req.PARENT_REQUEST_ID =
‘&parent_concurrent_request_id’;

8. Cancelling Concurrent request :

update fnd_concurrent_requests
set status_code=’D’, phase_code=’C’
where request_id=&req_id;

9. Kill sessions program wise

select ‘ALTER SYSTEM KILL SESSION ”’||sid||’,’||serial#||”’ immediate;’ from

v$session where
MODULE like ”;

10 .Concurrent Request running by SID

SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;

11. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests

where
ORACLE_PROCESS_ID=’&a’;

12. Oracle Concurrent Request Error Script (requests which were error ed out)
SELECT a.request_id “Req Id”
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ‘: ‘ || ctl.user_concurrent_program_name “program”
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = ‘E’
AND a.phase_code = ‘C’
AND actual_start_date > sysdate – 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = ‘US’
ORDER BY 5 DESC;

13. Request submitted by User

SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = ‘&user’
AND actual_start_date > sysdate – 1
ORDER BY REQUEST_DATE Asc;

14.Concurrent Program enable with trace

col User_Program_Name for a40

col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME “Program_Name”,
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) “User_Program_Name”,
SUBSTR(B.USER_NAME,1,15) “Last_Updated_By”,
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE=’Y’
AND A.LAST_UPDATED_BY=B.USER_ID

15.Checking which manager is going to execute a program

The below query identifies the manager which will be executing a given program.
This query is based on the specialization rules set for the managers.

SELECT user_concurrent_program_name, user_concurrent_queue_name

FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = ‘P’
AND cqc.include_flag(+) = ‘I’
AND cp.LANGUAGE = ‘US’
AND cp.user_concurrent_program_name = ‘&USER_CONCURRENT_PROGRAM_NAME’ AND NVL
(cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = ‘US’

16.To see all the pending / Running requests per each manager wise

SELECT request_id, phase_code, status_code, user_name,

user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = ‘P’ OR cwr.phase_code = ‘R’)
AND cwr.hold_flag != ‘Y’
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5

Note: The same information can be seen in Administer Concurrent Manager form for
each manager.

17 .Checking the incompatibilities between the programs

The below query can be used to find all incompatibilities in an application
instance.

SELECT a2.application_name, a1.user_concurrent_program_name,

DECODE (running_type,
‘P’, ‘Program’,
‘S’, ‘Request set’,
‘UNKNOWN’
) “Type”,
b2.application_name “Incompatible App”,
b1.user_concurrent_program_name “Incompatible_Prog”,
DECODE (to_run_type,
‘P’, ‘Program’,
‘S’, ‘Request set’,
‘UNKNOWN’
) incompatible_type
FROM apps.fnd_concurrent_program_serial cps,
apps.fnd_concurrent_programs_tl a1,
apps.fnd_concurrent_programs_tl b1,
apps.fnd_application_tl a2,
apps.fnd_application_tl b2
WHERE a1.application_id = cps.running_application_id
AND a1.concurrent_program_id = cps.running_concurrent_program_id
AND a2.application_id = cps.running_application_id
AND b1.application_id = cps.to_run_application_id
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id
AND b2.application_id = cps.to_run_application_id
AND a1.language = ‘US’
AND a2.language = ‘US’
AND b1.language = ‘US’
AND b2.language = ‘US’

The table apps.fnd_concurrent_program_serial has the information about

incompatibilities.

18 .How to find if module is installed or not in Oracle Apps

We have 3 ways to find out if a module is installed in oracle apps

1 We can run the following script

cd $AD_TOP/sql/adutconf.sql
In this script, there is a section->”Product Installation Status, Version Info and
Patch Level”
In this, status of installed means the product is installed.

2 Through OAM
In oracle apps, navigate to
OAM>Site Map>/License Manager>Reports>Licensed Products
Here filter the products by using “Licensed”. These are the licensed and installed
products in oracle apps.

3 Using a sql query->

We can use the following query

SELECT a.application_name,a.product_code,
DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status,
patch_level
FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id
and b.status=’I’
order by product_code asc;

19.How to Find out Product Installations and Patch Set level in Oracle Applications
11i ?

Use Following Query to find out Product Installation

SELECT application_name “Application Name”,

SUBSTR (application_short_name, 1, 10) “Short Name”,
RPAD (DECODE (fpi.status, ‘I’, ‘Installed’, ‘S’, ‘Shared Install’, ‘N’, ‘Not
Installed’ ), 14, ‘ ‘ ) “Install Status”, SUBSTR (patch_level, 1, 12) “Patch
Level”, fa.BASEPATH “Basepath”
FROM fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fat
WHERE fa.application_id = fpi.application_id
AND fa.application_id = fat.application_id
ORDER BY fpi.application_id;

SELECT application_name,SUBSTR (application_short_name, 1, 10) “Short Name”,SUBSTR

(patch_level, 1, 12) “Patch Level”, fa.BASEPATH “Basepath” FROM
fnd_product_installations fpi,fnd_application fa,fnd_application_tl fat
WHERE fa.application_id = fpi.application_id AND fa.application_id =
fat.application_id order by fa.BASEPATH;

Select product_version,patch_level from fnd_product_installations where patch_level

like ‘%AP%’;

select BASEPATH from fnd_application order by BASEPATH;

20. How to Find Environment Variables from database table – Oracle E-Business Suite
Are you running Oracle E-Business Suite (EBS) / Applications and want to get an
operating system level environment variable value from a database table, for
example for use in PL/SQL? Or perhaps to default a concurrent program parameter?
Didn’t think environment variables were stored in the database?
Try out out this query that shows you $FND_TOP:

select value from fnd_env_context where variable_name = ‘FND_TOP’ and

concurrent_process_id = ( select max(concurrent_process_id) from fnd_env_context );
VALUE ——————————————————————————– /d01/oracle/VIS/apps/apps_st/appl/fnd/12.0.0
——————————————————————————- Or

Did you want to find out the Product “TOP” directories e.g the full directory path
values from fnd_appl_tops under APPL_TOP?

col variable_name format a15 col value format a64 select variable_name, value from
fnd_env_context where variable_name like ‘%\_TOP’ escape ‘\’ and
concurrent_process_id = ( select max(concurrent_process_id) from fnd_env_context )
order by 1; VARIABLE_NAME VALUE ————— —————————————————————- AD_TOP
/d01/oracle/VIS/apps/apps_st/appl/ad/12.0.0 AF_JRE_TOP
/d01/oracle/VIS/apps/tech_st/10.1.3/appsutil/jdk/jre AHL_TOP
/d01/oracle/VIS/apps/apps_st/appl/ahl/12.0.0 AK_TOP
/d01/oracle/VIS/apps/apps_st/appl/ak/12.0.0 ALR_TOP
/d01/oracle/VIS/apps/apps_st/appl/alr/12.0.0 AME_TOP
/d01/oracle/VIS/apps/apps_st/appl/ame/12.0.0 AMS_TOP
/d01/oracle/VIS/apps/apps_st/appl/ams/12.0.0 AMV_TOP
/d01/oracle/VIS/apps/apps_st/appl/amv/12.0.0 AMW_TOP
/d01/oracle/VIS/apps/apps_st/appl/amw/12.0.0 APPL_TOP
/d01/oracle/VIS/apps/apps_st/appl AP_TOP
/d01/oracle/VIS/apps/apps_st/appl/ap/12.0.0 AR_TOP
/d01/oracle/VIS/apps/apps_st/appl/ar/12.0.0 … 21.How to find the full directory
path to $APPLTMP?

select value from fnd_env_context where variable_name = ‘APPLTMP’ and

concurrent_process_id = ( select max(concurrent_process_id) from fnd_env_context );
VALUE ——————————————————————————– /d01/oracle/VIS/inst/apps/VIS_demo/appltmp NB:
These queries assume your concurrent managers are running!

22. Script to Get OS user name with terminal name

REM: Script to Get Os user name with terminal name
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this
script.
REM:*****************************************

SELECT
DBA_USERS.USERNAME USERNAME,
DECODE(V$SESSION.USERNAME, NULL, ‘NOT CONNECTED’, ‘CONNECTED’) STATUS,
NVL(OSUSER, ‘-‘) OSUSER,
NVL(TERMINAL,’-‘) TERMINAL,
SUM(DECODE(V$SESSION.USERNAME, NULL, 0,1)) SESSIONS
FROM
DBA_USERS, V$SESSION
WHERE DBA_USERS.USERNAME = V$SESSION.USERNAME (+)
GROUP BY
DBA_USERS.USERNAME,
DECODE(V$SESSION.USERNAME, NULL, ‘NOT CONNECTED’, ‘CONNECTED’),
OSUSER,
TERMINAL
ORDER BY 1 ;

23 .Query to Identify the Concurrent Program for Long time…………………

Step1 : Run the first query , this will list all the programs that currently
running in Application. Take the SID and use it in the second query.

SELECT
f.user_name
,a.request_id “Req Id”
,a.concurrent_program_id “Prg Id”
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id “OS”
,vs.sid
,vs.serial# “Serial#”
,vp.spid
,TO_CHAR(request_date,’DD-MON-YY hh24:mi:ss’) request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 “Time”
,c.concurrent_program_name||’ – ‘||c2.user_concurrent_program_name “Program”
FROM APPLSYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
,v$session vs
,v$process vp
WHERE
a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN (‘I’,’P’,’R’,’T’)
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = ‘US’
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query
will show the currently running SQL in the DB, as your concurrent is submitted and
running. You can now find out the exact query ( select / insert / update ) which
is actually taking time in your concurrent program.

SELECT sql_text FROM v$sqltext t,v$session s

WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE
Step 3 :
————
The following query finds total run-time (in minutes) for a concurrent program.
Thus, with a little modification to this query, you can track which concurrent
programs take (very) long time to complete, and may need performance tuning.

Change the concurrent program name (tl.user_concurrent_program_name, see below)

according to your search criteria. In this example, my concurrent program is
“Autoinvoice Import Program”. You can also uncomment the “&Start_Date” line to get
the list for a specific date.

——————————————————————————-

— Query to find runtime for a concurrent program

——————————————————————————-

SELECT /*+ rule */

rq.parent_request_id “Parent Req. ID”,

rq.request_id “Req. ID”,

tl.user_concurrent_program_name “Program Name”,

rq.actual_start_date “Start Date”,

rq.actual_completion_date “Completion Date”,

ROUND((rq.actual_completion_date –

rq.actual_start_date) * 1440, 2) “Runtime (in Minutes)”

FROM applsys.fnd_concurrent_programs_tl tl,

applsys.fnd_concurrent_requests rq

WHERE tl.application_id = rq.program_application_id

AND tl.concurrent_program_id = rq.concurrent_program_id

AND tl.LANGUAGE = USERENV(‘LANG’)

AND rq.actual_start_date IS NOT NULL

AND rq.actual_completion_date IS NOT NULL

AND tl.user_concurrent_program_name = ‘Autoinvoice Import Program’ —

— AND TRUNC(rq.actual_start_date) = ‘&start_date’ — uncomment this for a specific

date

ORDER BY rq.request_id DESC;

*************************************************************

24 .Script to reset Oracle Apps front end User ID Password

Here is a sample anonymous PL/SQL block which will reset the Oracle Apps frontend
password for a given user from backend

DECLARE
flag_value BOOLEAN;
BEGIN
flag_value :=
fnd_user_pkg.changepassword(username=> ‘NKUMAR2’
,newpassword => ‘welcome1’);
IF flag_value
THEN
DBMS_OUTPU.PUT_LINE(‘The password reset successfully’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘The password reset has failed’);
END IF;

END;
/
COMMIT;

25 .Checking the duplicated schedules of the same program with the same arguments

The below query can be used to check the duplicated schedule of the same program
with the same arguments. This can be used to alert the users to cancel these
duplicated schedules.

Note: This query will return even though the request was submitted using a
different responsibility.

SELECT request_id, NAME, argument_text, user_name

FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
‘Report Set’, ‘Report Set:’ || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = ‘P’
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = ‘US’
AND fu.user_name NOT LIKE ‘PPG%’) t1
WHERE EXISTS (
SELECT 1
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
‘Report Set’, ‘Report Set:’
|| cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = ‘P’
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = ‘US’
AND fu.user_name NOT LIKE ‘PPG%’) t2
WHERE t1.NAME = t2.NAME
AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME;

26.Average pending time per request

This is a very useful query to check the performance of the concurrent managers.
Average pending time for a request is calculated like below:
(“Highest of Requested_start_date or Date_submitted” – Actual_start_date ) / Total
requests

A Request can be in Pending state for variety of reasons like conflict with other
requests, improperly tuned managers (sleep seconds / cache size / number of
managers etc)

We can schedule this script to gather data regularly for historical analysis as we
normally purge the concurrent requests regularly.

SELECT TO_CHAR (actual_start_date, ‘DD-MON-YYYY’) DAY,

concurrent_queue_name,
(SUM ( ( actual_start_date
– (CASE
WHEN requested_start_date > request_date
THEN requested_start_date
ELSE request_date
END
)
)
* 24
* 60
* 60
)
)
/ COUNT (*) “Wait_Time_per_Req_in_Secs”
FROM apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_processes fcp,
apps.fnd_concurrent_queues fcq
WHERE cr.phase_code = ‘C’
AND cr.actual_start_date IS NOT NULL
AND cr.requested_start_date IS NOT NULL
AND cr.controlling_manager = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
GROUP BY TO_CHAR (actual_start_date, ‘DD-MON-YYYY’), concurrent_queue_name
ORDER BY 2;

Note: Depending on the purging schedules some requests might miss if the
corresponding data in fnd_concurrent_processes is purged.

27.Script to Monitor Concurrent requests average run time

Do you need a daily report which would give an average run time of all concurrent
requests in your environment?

So you would know which concurrent request is taking longer time and on which day:
The below script gives an average run time for all concurrent requests for the
current day, previous day, day before yesterday and for the whole week:

set pagesize 500

set echo off
set feedback off
set linesize 200
col USER_CONCURRENT_PROGRAM_NAME for a75
col wkly_Time Heading ‘Weekly Run | Time | Avg |(In Minutes) ‘
col dbydy_Tim Heading ‘Day Before|Yesterday | Run Time | Avg |(In Minutes) ‘
col ydy_Tim Heading ‘Yesterday | Run Time | Avg |(In Minutes) ‘
col tdy_Tim Heading ‘Today | Run Time | Avg |(In Minutes) ‘
col tdy_Tim Heading &_DATE
select wkly.user_concurrent_program_name, wkly.wkly_time ,
to_char(nvl(dbydy.dbydy_time,’ Not Run’)) Dbydy_tim,to_char(nvl(ydy.ydy_time,’
Not Run’)) ydy_tim,to_char(nvl(tdy.tdy_time,’ Not Run’)) tdy_tim
FROM
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date-
actual_start_date)*24*60)),’99999999.99′) wkly_time
FROM apps.fnd_conc_req_summary_v
WHERE nvl(actual_start_date,sysdate) >= (sysdate-7)
AND phase_code=’C’ and status_code in (‘C’,’G’)
group by user_concurrent_program_name) wkly,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date-
actual_start_date)*24*60)),’99999999.99′) dbydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-2))
AND phase_code=’C’ and status_code in (‘C’,’G’)
group by user_concurrent_program_name ) dbydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date-
actual_start_date)*24*60)),’99999999.99′) ydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-1))
AND phase_code=’C’ and status_code in (‘C’,’G’)
group by user_concurrent_program_name ) ydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date-
actual_start_date)*24*60)),’99999999.99′) tdy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate))
AND phase_code=’C’ and status_code in (‘C’,’G’)
group by user_concurrent_program_name ) tdy
WHERE wkly.user_concurrent_program_name =dbydy.user_concurrent_program_name (+)
AND dbydy.user_concurrent_program_name = ydy.user_concurrent_program_name (+)
AND ydy.user_concurrent_program_name = tdy.user_concurrent_program_name (+)
order by wkly_time desc;

28. Find currently spooling temp file from request

col outfile format a30 col logfile format a30 select cp.plsql_dir || ‘/’ ||
cp.plsql_out outfile , cp.plsql_dir || ‘/’ || cp.plsql_log logfile from
apps.fnd_concurrent_requests cr , apps.fnd_concurrent_processes cp where
cp.concurrent_process_id = cr.controlling_manager and cr.request_id = &request_id;
OUTFILE LOGFILE —————————— —————————— /usr/tmp/PROD/o0068190.tmp
/usr/tmp/PROD/l0068190.tmp REM Now tail log file on database node to see where it
is at, near realtime REM tail -f /usr/tmp/l0068190.tmp

29.Currently held locks per concurrent request

set lines 150 col object_name format a32 col mode_held format a15 select /*+
ordered */ fcr.request_id , object_name , object_type , decode( l.block , 0, ‘Not
Blocking’ , 1, ‘Blocking’ , 2, ‘Global’ ) status , decode( v.locked_mode , 0,
‘None’ , 1, ‘Null’ , 2, ‘Row-S (SS)’ , 3, ‘Row-X (SX)’ , 4, ‘Share’ , 5, ‘S/Row-X
(SSX)’ , 6, ‘Exclusive’ , to_char(lmode) ) mode_held from
apps.fnd_concurrent_requests fcr , gv$process pro , gv$session sess ,
gv$locked_object v , gv$lock l , dba_objects d where fcr.phase_code = ‘R’ and
fcr.oracle_process_id = pro.spid (+) and pro.addr = sess.paddr (+) and sess.sid =
v.session_id (+) and v.object_id = d.object_id (+) and v.object_id = l.id1 (+) ;
REQUEST_ID OBJECT_NAME OBJECT_TYPE STATUS MODE_HELD ———- ——————————– ——————-
———— ————— 1070780 VIRTUATE_GL_OLAP_REFRESH TABLE Not Blocking Exclusive

——————————————————————————-

— Query to find concurrent request status related information

——————————————————————————-

SELECT fu.user_name “User ID”,

frt.responsibility_name “Responsibility Used”,

fcr.request_id “Request ID”,

fcpt.user_concurrent_program_name “Concurrent Program Name”,

DECODE(fcr.phase_code,
‘C’, ‘Completed’,

‘P’, ‘Pending’,

‘R’, ‘Running’,

‘I’, ‘Inactive’,

fcr.phase_code) “Phase”,

DECODE(fcr.status_code,

‘A’, ‘Waiting’,

‘B’, ‘Resuming’,

‘C’, ‘Normal’,

‘D’, ‘Cancelled’,

‘E’, ‘Error’,

‘F’, ‘Scheduled’,

‘G’, ‘Warning’,

‘H’, ‘On Hold’,

‘I’, ‘Normal’,

‘M’, ‘No Manager’,

‘Q’, ‘Standby’,

‘R’, ‘Normal’,

‘S’, ‘Suspended’,

‘T’, ‘Terminating’,

‘U’, ‘Disabled’,

‘W’, ‘Paused’,

‘X’, ‘Terminated’,

‘Z’, ‘Waiting’,

fcr.status_code) “Status”,

fcr.request_date “Request Date”,

fcr.requested_start_date “Request Start Date”,

fcr.hold_flag “Hold Flag”,

fcr.printer “Printer Name”,

fcr.parent_request_id “Parent Request ID”

— fcr.number_of_arguments,

— fcr.argument_text,

— fcr.logfile_name,

— fcr.outfile_name

FROM fnd_user fu,

fnd_responsibility_tl frt,

fnd_concurrent_requests fcr,

fnd_concurrent_programs_tl fcpt

WHERE fu.user_id = fcr.requested_by

AND fcr.concurrent_program_id = fcpt.concurrent_program_id

AND fcr.responsibility_id = frt.responsibility_id

AND frt.LANGUAGE = USERENV(‘LANG’)

AND fcpt.LANGUAGE = USERENV(‘LANG’)

— AND fcr.request_id = 7137350 —

AND fcpt.user_concurrent_program_name = ‘Autoinvoice Import Program’ —

ORDER BY fcr.request_date DESC;

30) CONCURRENT REQUEST MONITORING QUERIES

SQL TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:
**********************************************************
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id “REQ_ID”,a.oracle_process_id “OPID”,a.os_process_id
“PPID”,
e.user_concurrent_program_name “CNAME”,
f.user_name,a.status_code “ST_CD”,a.phase_code “PH_CD”, b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,’MON-DD-HH-MI-SS’) START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,’MON-DD-HH-MI-SS’) COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
c.program,d.spid from v$session c, v$process d
where c.paddr=d.addr) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
v$session_wait g
where a.oracle_process_id=b.spid
and a.concurrent_program_id=e.concurrent_program_id
and e.language=’US’
and a.requested_by=f.user_id
and b.sid=g.sid
and a.status_code=’R’
and a.phase_code=’R’;
********************************************************

Retrieve a list of all responsibilities.

Parameters
None
*//

SELECT
(SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id)
application
, frt.responsibility_id
, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;

//*
2. Menus Listing
Purpose/Description:
To see the Menus associated with a given responsibility
Parameters
responsibility_id that you can retrieve from query nr 1 (Responsibilities Listing)
*//

SELECT DISTINCT
a.responsibility_name
, c.user_menu_name
FROM
apps.fnd_responsibility_tl a
, apps.fnd_responsibility b
, apps.fnd_menus_tl c
, apps.fnd_menus d
, apps.fnd_application_tl e
, apps.fnd_application f
WHERE
a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = 50103
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = ‘US’;
//*
3. Submenu And Function Listing
Purpose/Description:
By using this query you can check function and submenus attached to a specific menu
Parameters
User_menu_name that you can get by running query 2 (Menu Listing)
*//

SELECT
c.prompt
, c.description
FROM
apps.fnd_menus_tl a
, fnd_menu_entries_tl c
WHERE
a.menu_id = c.menu_id
AND a.user_menu_name = ‘Navigator Menu – System Administrator GUI’;

//*
4.User and Assigned Responsibility Listing
Purpose/Description:
You can use this query to check responsibilities assigned to users.
Parameters
None
*//

SELECT UNIQUE
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE
g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY
SUBSTR (user_name, 1, 30)
, SUBSTR (a.application_name, 1, 50)
, SUBSTR (r.responsibility_name, 1, 60);

//*
5. Responsibility and assigned request group listing
Purpose/Description:
To find responsibility and assigned request groups.
Every responsibility contains a request group (The request group is basis of
submitting requests)
Parameters
None
*//

SELECT
responsibility_name responsibility
, request_group_name
, frg.description
FROM
fnd_request_groups frg
, fnd_responsibility_vl frv
WHERE
frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

//*
6. Profile option with modification date and user
Purpose/Description:
Query that can be used to audit profile options.
Parameters
None
*//

SELECT
t.user_profile_option_name
, profile_option_value
, v.creation_date
, v.last_update_date
, v.creation_date – v.last_update_date “Change Date”
, (SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) “Created By”
, (SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) “Last Update By”
FROM
fnd_profile_options o
, fnd_profile_option_values v
, fnd_profile_options_tl t
WHERE
o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active = SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;

//*
7. Forms personalization Listing
Purpose/Description:
To get modified profile options.
Personalization is a feature available in 11.5.10.X.
Parameters
None
*//

SELECT
ffft.user_function_name “User Form Name”
, ffcr.SEQUENCE
, ffcr.description
, ffcr.rule_type
, ffcr.enabled
, ffcr.trigger_event
, ffcr.trigger_object
, ffcr.condition
, ffcr.fire_in_enter_query
, (SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) “Created By”
FROM
fnd_form_custom_rules ffcr
, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;

//*
8. Patch Level Listing
Purpose/Description:
Query that can be used to view the patch level status of all modules
Parameters
None
*//

SELECT
a.application_name
, DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status
, patch_level
FROM
apps.fnd_application_vl a
, apps.fnd_product_installations b
WHERE
a.application_id = b.application_id;

//*
9. Request attached to responsibility listing
Purpose/Description:
To see all requests attached to a responsibility
Parameters
None
*//

SELECT
responsibility_name
, frg.request_group_name
, fcpv.user_concurrent_program_name
, fcpv.description
FROM
fnd_request_groups frg
, fnd_request_group_units frgu
, fnd_concurrent_programs_vl fcpv
, fnd_responsibility_vl frv
WHERE
frgu.request_unit_type = ‘P’
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;
//*
10. Request listing application wise
Purpose/Description:
View all request types application wise
Parameters
None
*//

SELECT
fa.application_short_name
, fcpv.user_concurrent_program_name
, description
, DECODE (fcpv.execution_method_code
,’B’, ‘Request Set Stage Function’
,’Q’, ‘SQL*Plus’
,’H’, ‘Host’
,’L’, ‘SQL*Loader’
,’A’, ‘Spawned’
,’I’, ‘PL/SQL Stored Procedure’
,’P’, ‘Oracle Reports’
,’S’, ‘Immediate’
,fcpv.execution_method_code) exe_method
, output_file_type
, program_type
, printer_name
, minimum_width
, minimum_length
, concurrent_program_name
, concurrent_program_id
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
ORDER BY description

//*
11. Count Reports per module
Purpose/Description:
To Count Reports
Parameters
None
*//

SELECT
fa.application_short_name
, DECODE (fcpv.execution_method_code
,’B’, ‘Request Set Stage Function’
,’Q’, ‘SQL*Plus’
,’H’, ‘Host’
,’L’, ‘SQL*Loader’
,’A’, ‘Spawned’
,’I’, ‘PL/SQL Stored Procedure’
,’P’, ‘Oracle Reports’
,’S’, ‘Immediate’
,fcpv.execution_method_code) exe_method
, COUNT (concurrent_program_id) COUNT
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
GROUP BY
fa.application_short_name
, fcpv.execution_method_code
ORDER BY 1;

//*
12. Request Status Listing
Purpose/Description:
This query returns report/request processing time
Parameters
None
*//

SELECT
f.request_id
, pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date
, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
‘ HOURS ‘
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) –
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
‘ MINUTES ‘
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) –
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 –
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) –
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60)
))
‘ SECS ‘ time_difference
, DECODE(p.concurrent_program_name
,’ALECDC’
,p.concurrent_program_name’[‘
f.description’]‘
,p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code
,’R’,’Running’
,’C’,’Complete’
,f.phase_code) Phase
, f.status_code
FROM
apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE
f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.language = USERENV(‘Lang’)
AND f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;

//*
13. User and responsibility listing
Purpose/Description:
Check responsibilities assigned to users
Parameters
None
*//

SELECT UNIQUE
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
–AND a.application_name like ‘%Order Man%’
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)

//*
14. Applied Patch Listing
Purpose/Description:
Check Current Applied Patches
Parameters
None
*//

SELECT
patch_name
, patch_type
, maint_pack_level
, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC

15. How to check the System Administarator responsiblity for the users in ORACLE
EBS ?

set lines 2000

set pages 5000
col USER_NAME for a45

Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE

from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and c.responsibility_name=’System Administrator’

at Friday, February 14, 2014

Share this:
TwitterFacebook

Related
How to import Concur Expense (SAE) data into Oracle Application (GL:General Ledger)
==================================================== Oracle (EBusiness Suite
11i/R12) – Concur Expense Integration.
==================================================== Are you using Concur cloud
application for expenses and want to post expense data into your oracle application
General Ledger module? No Problem, we can help you for this automation. Just
follow following steps to import and post Concur…

April 26, 2017

In "iExpense"

How to Analyze Long Running or Pending Concurrent Requests jobs

a) 1.Run the script for any concurrent request which is going into pending/running
status and upload the output to SR. Note 134033.1 ANALYZEPENDING.sql Script for
Analyzing all Pending Requests or Note 134035.1 ANALYZEREQ.SQL - Detailed Analysis
of One Concurrent Request 2. Verify the incompatibilities from the concurrent
program definition screen.…

March 31, 2017

In "Concurrent Processing in R12"

How to configure printers in EBS 11i/R12

How to configure printers in EBS 11i/R12 Oracle E-Business Suite offers two
printing solutions to handle all your printing requirements. For most printing
needs, the Pasta Utility offers quick setup and easy maintenance. For additional
flexibility, Oracle E-Business Suite allows you to define your own printer drivers
and print styles.…

April 19, 2017

In "Payables"

This entry was posted in Concurrent Processing in R12 and tagged DB Locks, Long
Running Concurrent Programs. Bookmark the permalink.
Post navigation
API to create Code Combination and get CCID
How to disabled GL Account and All associated GL Code Combinations.
Leave a Reply
Enter your comment here...
CATEGORIES
AR Reconciliation (8)
BI Publisher (3)
Cash Management (4)
Concurrent Processing in R12 (4)
Conversion (2)
Fixed Asset (23)
General Ledger (19)
GRC (5)
iExpense (21)
Linux Server Security and Maintenance (1)
Microsoft Windows Configurations (6)
Oracle Cloud Fusion Financial (11)
Oracle Database (2)
Oracle Fusion HCM (9)
Oracle Fusion HCM Technical (4)
Oracle Fusion Technical (9)
Oracle Installations (3)
Oracle Inventory (1)
Oracle SQL (4)
Order Management (1)
Payables (47)
Payables Period Close (10)
Payables Reconciliation (10)
Payments (29)
Payroll (1)
Perl and Unix Scripting (1)
Purchasing (4)
Receivables (16)
Receivables Period Close (15)
Receiving Subledger (3)
SubLedger Accounting (25)
Supply Chain Managment (1)
TCA – DQM (1)
Uncategorized (2)
Useful SQL Scripts (4)
Workday (7)
SUBSCRIBE TO BLOG VIA EMAIL
Enter your email address to subscribe to this blog and receive notifications of new
posts by email.

Join 20 other followers

Email Address:
Enter your email address

Subscribe

CALENDAR
CALENDAR
April 2017
M T W T F S S
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
« Mar May »
Blog at WordPress.com.