Long running Concurrent Requests
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
or v$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
gv$process a,
gv$session b,
gv$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
errored 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 ) ;
Or
Do 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;
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 ) ;
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,
gV$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.
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 gv$sqltext t, gv$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' -- <change it> -- 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;
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 ( + ) ;
-- 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 -- <change it> AND fcpt.user_concurrent_program_name = 'Autoinvoice Import Program' -- <change it> 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
gv$session c,
gv$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';
http://learnappsdbaskills.blogspot.in/2014/03/scripts-for-troubleshooting-concurrent.html
https://me-dba.com/2008/09/16/concurrent-request-scheduling-explained/
No comments:
Post a Comment