Useful Application DBA Performance Scripts:
Checking Middle Tier Sessions:
===============================
/* ckmt.sql
check middle tiers session
*/
col umachine format a20 trunc head UserSrvr
col totact format 999999 head ACTIVE
col totinact format 999999 head INACTIVE
col totsess format 999999 head TOTAL
col earliest_logon format a12 head Earliest
col latest_logon format a12 head Latest
break on report
compute sum of totact on report
compute sum of totinact on report
compute sum of totsess on report
select replace(s.machine,'GEIPS-AMER\',null) umachine,
sum(decode(s.status,'ACTIVE',1,0)) totact,
sum(decode(s.status,'INACTIVE',1,0)) totinact,
count(*) totsess,
min(to_char(s.logon_time,'mm/dd hh24:mi')) earliest_logon,
max(to_char(s.logon_time,'mm/dd hh24:mi')) latest_logon
from v$session s
where s.type = 'USER'
group by s.machine
order by 4,1
/
How to get total memory being consumed by forms process:
===================================================================
#!/bin/sh
ALLPROCS=`ps -ef|grep "f60webmx webfile=" | grep -v grep | tr -s " " | cut -f3 -d" "`
LOOPCTR=1
for EACHPROC in $ALLPROCS
do
if [ $LOOPCTR -eq 1 ]
then PROCLIST="${EACHPROC}"
LOOPCTR=2
else PROCLIST="${PROCLIST},${EACHPROC}"
fi
done
# echo $PROCLIST
# ps -lf -p $PROCLIST -o rss,vsz,osz > f60procinfo.out
TOTMEM=0
PROCCTR=0
PROCMEM=`ps -lf -p $PROCLIST -o rss,vsz,osz | tail +2 | cut -f1 -d" "`
for EACHMEM in $PROCMEM
do
TOTMEM=`expr $TOTMEM + $EACHMEM`
PROCCTR=`expr $PROCCTR + 1`
done
echo "The total memory for the ${PROCCTR} f60 forms processes is ${TOTMEM} KB"
Get concurrent manager status and avg response time.
======================================================================================
set verify off
set linesize 150
col qname head "Activated Concurrent Queue - DEST NODE" format a50
col actual head "Actual" format 999999
col target head "Target" format 999999
col running head "Running" format 9999999
col pending head "Pending" format 9999999
col paused head "Paused" format 9999999
col influx head "InFlux" format 9999999
col avgqtime head "AvgQtime" format 99999.99
break on report
compute sum of actual on report
compute sum of target on report
compute sum of running on report
compute sum of pending on report
accept dispmethod char default n prompt 'Display only queues with running jobs [y/n] <n> : '
rem select q.concurrent_queue_name qname,
select t.USER_CONCURRENT_QUEUE_NAME || ' - ' || target_node qname,
q.running_processes actual,
q.max_processes target,
sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,
sum(decode(r.phase_code,'P',1,0)) pending,
nvl(sum(decode(r.status_code,'W',1,'H',1,'S',1,'A',1,'Z',1,0)),0) paused,nvl(sum(decode(r.status_code,'F',1,'Q',1,'B',1,'T',0)),0) influx,
avg((nvl(r.actual_start_date,r.requested_start_date) - r.requested_start_date)*1440) avgqtime from applsys.fnd_concurrent_requests r,applsys.fnd_concurrent_processes p,applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_queues_tl t
where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and q.application_id = t.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.concurrent_queue_id = t.concurrent_queue_id
and q.max_processes > 0
and ((r.phase_code in ('R','P','I') and upper('&dispmethod') = 'Y') or
upper('&dispmethod') != 'Y')
group by t.USER_CONCURRENT_QUEUE_NAME || ' - ' || target_node, q.running_processes, q.max_processes,t.USER_CONCURRENT_QUEUE_NAME;
=======================================================================================================================================
/* cmlong.sql
Monitor script to list the ccmgr jobs running longer than 10 minutes
and exceeding the average historical runtime for this particular
job
*/
============================
col program form A35 trunc head "Program Full Name"
col intprog format a20 trunc head "Internal Name"
col time form 9999.99
col "Req Id" form 9999999
col qname head "Concurrent Manager Queue" format a25 trunc
rem select q.concurrent_queue_name qname
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,a.phase_code,a.status_code
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name ||
' (' || to_char(c.concurrent_program_id) || ')' intprog,
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.phase_code in ('I','P','R','T')
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'
and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 60
and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >
( select avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440
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'
)
order by 5 desc;
============================================================================================
/* ckactmt.sql
check server tiers active sessions
note: state of WAITING means session is currently waiting
*/
col usersrvr format a12 trunc head ServerName
col logonat format a12 head LoggedOn
col osuser format a8 trunc
col event head Current_Wait_Event format a25 trunc
col state format a7 trunc
col sessprog format a12 head SessProg trunc
col spid format a8 head DBProc
col LastCallET format a11
col holdinglatch format a20 trunc head HoldingLatch
col sid format 99999
break on usersrvr skip 1 on report
compute count of sid on usersrvr
compute count of sid on report
select replace(s.machine,'HEADLANDS\',null) usersrvr, s.sid,
to_char(s.logon_time,'mm/dd hh24:mi') logonat,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.osuser, nvl(s.module,p.program) sessprog, w.event, w.state, p.spid,
decode(h.sid,null,'None',h.name) holdinglatch
from v$session s, v$session_wait w, v$process p, v$latchholder h
where type = 'USER'
and status = 'ACTIVE'
and s.sid = w.sid
and s.paddr = p.addr
and s.osuser is not null
and s.sid = h.sid (+)
order by 1,4
/
=================================================================================
/* cmpending.sql
find pending jobs pending in a queue but should be running
*/
set lines 132
set pages 30
col program format a45 trunc
col phase_code format a1 trunc
col statustxt format a15 trunc
col parent format a8 trunc
col reqstarthide noprint
col minlate format 99999.90 head MinLate
set verify off
prompt Note: Minutes Late shown in decimal minutes.....
select a.requested_start_date reqstarthide,
to_char(a.requested_start_date,'mm/dd/yy hh24:mi') reqstart,
(sysdate-requested_start_date)*1440 minlate,
a.request_id "Req Id",
decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent",
a.concurrent_program_id "Prg Id",
a.phase_code,
a.status_code || ' - ' ||l1.meaning statustxt,
c.concurrent_program_name||' - '|| c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,
APPLSYS.fnd_concurrent_programs_tl c2,
APPLSYS.fnd_concurrent_programs c,
applsys.fnd_lookup_values l1
where 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.language = 'US'
and c2.application_id = c.application_id
and a.actual_start_date is null
and a.status_code in ('A','H','I','M','P','Q','R')
and a.phase_code in ('P','I')
and sysdate - a.requested_start_date < 2
and a.requested_start_date < sysdate
and l1.lookup_type = 'CP_STATUS_CODE'
and l1.lookup_code = a.status_code
and l1.language = 'US'
and l1.enabled_flag = 'Y'
and (l1.start_date_active <= sysdate and l1.start_date_active is not null)
and (l1.end_date_active > sysdate or l1.end_date_active is null)
order by 1;
===========================================================================================
/* top10jobs.sql
see the top10 jobs based on total runtime for today
*/
col program form A35 trunc head "Program Full Name"
col intprog format a35 trunc head "Internal Name"
col TotTime form 99999
col AvgTime form 99999.90
col qname head "Queue" format a15 trunc
select * from (
select q.concurrent_queue_name qname,
c.concurrent_program_name ||
' (' || to_char(c.concurrent_program_id) || ')' intprog,
ctl.user_concurrent_program_name "program",
sum((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "TotTime",
count(*),
avg((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "AvgTime"
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 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'
and trunc(actual_completion_date) = trunc(sysdate)
and actual_start_date is not null
and actual_completion_date is not null
group by q.concurrent_queue_name,
c.concurrent_program_name || ' (' || to_char(c.concurrent_program_id) || ')' ,
ctl.user_concurrent_program_name
order by 4 desc)
where rownum < 11;
Find out Inactive Form sessions for X hours?
=================================================
set lines 200 pages 200
col event for a30
select a.sid,a.serial#,a.action,a.status,a.event,round(a.last_call_Et/60/60) LSTCALL_Hrs,
round(a.seconds_in_wait/60/60) SCNDINWAIT_Hrs,
a.process, b.spid
from v$session a, v$process b
where a.action like 'FRM%'
and a.paddr = b.addr
and a.last_call_Et/60/60 > &&No_Of_Hours_Old order by 6,5;
Find SQL from SID:
=========================
SELECT s.sid, s.serial#, st.sql_text sql_text
FROM v$session s, v$sql st, v$process p
WHERE s.sql_hash_value = st.hash_value
AND s.sql_address = st.address
AND s.paddr = p.addr
and ( s.sid='&which_sid' )
To the full SQL Text:
select x.sql_text from v$session s,v$sqltext x
where s.sql_address=x.address and s.sid = &which_sid order by sid, piece asc;
Find the Session details from the Process ID :
=====================================================
SELECT s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (&OS_PROCESS_ID);
To set the Time to in a required format:
alter session set nls_date_format='DD:MON:YYYY:HH24:MI:SS';
To Monitor the Progress of RMAN Backup:
==============================================
SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN:%'
AND opname NOT LIKE 'RMAN: aggregate%';
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';
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';
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';
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';
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;
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'
/
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';
Cancelling Concurrent request:
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;
Kill session’s program wise:
================================
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where MODULE like '';
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;
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';
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;
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;
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;
How to check Size of all tablespaces in the instance:
========================================================
set linesize 200
set pagesize 200
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
Concurrent_scripts:
=================
Scheduled concurrent requests:
Lot of times we need to find out the concurrent programs scheduled. Users can schedule the concurrent requests in three ways
(To run once at a specified time / To run periodically / To run on specific days of the month or week).
The below query will return all the concurrent requests which are scheduled using any of the above methods:
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
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 fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;
Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week.
Positions 1 through 31: Specific day of the month.
Position 32: Last day of the month
Positions 33 through 39: Sunday through Saturday
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
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.
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'
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.
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.
Purging con-requests:
====================
goal: How to run the Purge Concurrent Request and/or Manager Data program
and which tables does it purge?
fix:
To run the Purge Concurrent Request and/or Manager Data program:
1. Log in to Application as System Administrator responsibility.
2. Navigate to Request> Run> Single Request
3. Query up Purge Concurrent Requests.
4. For values to be entered in the parameter screen refer to the Oracle
Administration System Administration Guide
.
The following tables will be purged:
.
FND_CONCURRENT_REQUESTS
This table contains a complete history of all concurrent requests.
.
FND_RUN_REQUESTS
When a user submits a report set, this table stores information about the
reports in the report set and the parameter values for each report.
.
FND_CONC_REQUEST_ARGUMENTS
This table records arguments passed by the concurrent manager to each program
it starts running.
.
FND_DUAL
This table records when requests do not update database tables.
.
FND_CONCURRENT_PROCESSES
This table records information about Oracle Applications and operating system
processes.
.
FND_CONC_STAT_LIST
This table collects runtime performance statistics for concurrent requests.
.
FND_CONC_STAT_SUMMARY
This table contains the concurrent program performance statistics generated by
the Purge Concurrent Request and/or Manager Data program. The Purge Concurrent
Request and/or Manager Data program uses the data in FND_CONC_STAT_LIST to
compute these statistics.
FND_CONC_PP_ACTIONS
Stores the post request processing actions(e.g., print, notify) for each
submitted request. There's a concurrent_request_id here for each request_id
in the FND_CONCURRENT_REQUESTS.
FND_RUN_REQ_PP_ACTIONS
Stores the post request processing actions(e.g., print, notify) for
submitted request set programs that are stored in FND_RUN_REQUESTS
FND_ENV_CONTEXT
This table stores information about environment name and value for each of the
concurrent process
==================================================================================================================================================================================
Conc -status:
==============
select REQUEST_ID , REQUESTED_BY , decode(phase_Code,'P','Pending','R','Running','C','Completed Normal') PHASE,
decode(status_code,'C',' Completed','I',' Inactive ') STATUS from APPS.fnd_concurrent_requests where phase_code='R';
select
REQUEST_ID,requestor,
to_char(actual_start_date,'DD-MON-RR HH24:MI:SS') actual_start,
to_char(actual_completion_date,'DD-MON-RR HH24:MI:SS') actual_completion,
to_char(round(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)+(((actual_completion_date-actual_start_date)
*24*60*60)-(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)*60))/100,2)) execution_time
from fnd_conc_req_summary_v
where USER_CONCURRENT_PROGRAM_NAME='XWII Integration Worker'
and to_char(round(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)+
(((actual_completion_date-actual_start_date)*24*60*60)-(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)*60))/100,2))>10
update fnd_concurrent_requests
set phase_code='C',status_code='X'
where REQUEST_ID=5503613
Mailer Status:
===============
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.USER_CONCURRENT_QUEUE_NAME like '%Mail%';
Workflow Components:
=================
select COMPONENT_NAME,COMPONENT_STATUS from fnd_svc_components;
select fsc.COMPONENT_NAME,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.USER_CONCURRENT_QUEUE_NAME like '%Mail%';
Dataguard_status:
==================
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#))
APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
Workflow status:
=================Workflow components status:
=================
set linesize 150
set pagesize 9999
col COMPONENT_NAME format a50
col COMPONENT_STATUS format a50
select SC.COMPONENT_TYPE, SC.COMPONENT_NAME, FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS from FND_SVC_COMPONENTS SC order by 1, 2;
workflow mailer status:
===========================
set linesize 100
col Component format a40
SELECT component_name as Component, component_status as Status FROM fnd_svc_components WHERE component_type = 'WF_MAILER';
Total Size of the Database?
====================
SELECT (dsize.data_size + tsize.temp_size + rsize.redo_size)/1024/1024 "total_size"
FROM (SELECT sum(bytes) data_size
FROM dba_data_files ) dsize,
(SELECT nvl(sum(bytes),0) temp_size
FROM dba_temp_files ) tsize,
(SELECT sum(bytes) redo_size
FROM sys.v_$log ) rsize;
How to know which modules have been installed in my Oracle E-Biz environment ?
================================================================
SELECT substr(APPLICATION_SHORT_NAME,1,8) product,
substr(PRODUCT_VERSION,1,14) version, \
substr(PATCH_LEVEL,1,11) patch_level,
FROM fnd_application a, fnd_product_installations p
WHERE a.application_id = p.application_id
ORDER BY application_short_name;
Or
cd $AD_TOP/sql/
sqlplus apps/<password> @adutconf.sql
How to find what are all the languages installed or not:
Select distinct NLS_LANGUAGE, LANGUAGE_CODE,NLS_TERRITORY,INSTALLED_FLAG
from fnd_languages
where INSTALLED_FLAG = 'I' or INSTALLED_FLAG = 'B'
ORDER BY NLS_LANGUAGE
Select distinct NLS_LANGUAGE, LANGUAGE_CODE,NLS_TERRITORY,INSTALLED_FLAG
from fnd_languages
ORDER BY NLS_LANGUAGE
5.From Whom Do we need to get an approval to bring down the Database.
6.How to start and Stop the application Services and Database.
7.What are all the Day to Day Health Checks do we need to perform?
8.What are all the weekly/Monthly Health Checks do we need to perform?
9.Known issues of Current Environment.
10.Any Management/Client Special Instructions.
Database Health Checks:
===================
http://rafioracledba.blogspot.in/2010/08/database-health-check.html
Long running Concurrent Requests:
=========================
http://oracle4ryou.blogspot.in/2012/10/long-running-concurrent-requests.html
Changing Passwords:
==============
FNDCPASS apps/apps 0 Y system/system SYSTEM APPLSYS
Checking Invalid Objects:
===================
COLUMN object_name FORMAT A30
SELECT owner,object_type,object_name,status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
Manual Approach:
===============
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Blocking sessions:
=============
SELECT S.SID, S.SQL_ID, S.USERNAME, S.MACHINE from
V$SESSION S WHERE S.SID = (SELECT blocker from
(select a.sid blocker, 'is blocking the session ', b.sid blockee from v$lock a, v$lock b
where a.block =1 and b.request > 0 and a.id1=b.id1 and a.id2=b.id2));
Performance Monitoring Scripts:
=======================
http://samiora.blogspot.in/2009/01/performance-monitoring-scripts.html
Concurrent Manager Scripts:
====================
http://allappsdba.blogspot.in/2012/03/various-scripts-used-with-concurrent.html
Scripts related to TEMP TABLESPACE:
============================
http://allappsdba.blogspot.in/2012/04/scripts-related-to-temp-tablespace.html
Oracle Tablespace Maintenance scripts excluding Undo and Temp tablespaces:
========================================================
http://allappsdba.blogspot.in/2012/04/oracle-tablespace-maintenance-scripts.html
Compiling Forms:
============
Ensure that the $FORMS_PATH includes $AU_TOP/resource and $AU_TOP/resource/stub,
for example
echo $FORMS_PATH
frmcmp_batch.sh module=/u01/oracle/TEST/apps/apps_st/appl/au/12.0.0/forms/US/
XXX.fmb userid=APPS/APPS output_file=/u01/oracle/TEST/apps/apps_st/appl/inv/
12.0.0/forms/US/XXX.fmx module_type=form compile_all=special
Oracle Application R12 Logs:
=====================
http://www.reachdba.com/showthread.php?29-Oracle-Applications-R12-Log-Files
Post Clone Checks:
==============
http://oraclehub.wordpress.com/2012/10/06/post-clone-checksprocedures-for-11i-r12/
Patch Scripts:
================AD / Patches
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS
/* To find the latest application version */
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL
/* Query to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application*/
select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = ''
/* To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id */
select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3;
/* to find the base application version */
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y'
/* To find all available application version */
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES
/* To get file version of any application file which is changed through patch application */
select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME
/* To get information related to how many time driver file is applied for bugs */
select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = ''
/* To find latest patchset level for module installed */
select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME
/* To find what is being done by the patch */
select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE
/* To find Merged patch Information from database in Oracle Applications */
select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) );
/* Second Query to know, what all has been done during application of PATCH */
Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR,
D.FILENAME, E.ACTION_CODE
/* Script to find out Patch level of mini Pack */
Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%';
Usefil Commands:
========================
How to find a "word" or pattern in all files in a directory & subdirectories
===================================================
find . -name "*" -exec grep -l {} \; -print
for example I want to search for word oracle
find . -name "*" -exec grep -l oracle {} \; -print
How to delete files older than N number of days , Useful to delete old log files
=======================================================
find . -name '*.*' -mtime + -exec rm {} \;
for example if I want to delete all files older than 7 days
find . -name '*.*' -mtime +7 -exec rm {} \;
*Check carefully & run it first from logs or dump directory
How to find a class inside a set of Jar files
=================================
for i in 'find .-name *.jar'
do
if test 'jar -tvf $i|grep QualityObject.class'
then
ls $i
fi
done
How to find the symbolic links that point to the old path in your oracle_home and appl_top.
=================================================================
This command is useful in cloning after restore from source to target that symbolic link are not pointing to source.
ls -al `find . -type l` | grep $OLD_PATH
To find all the text files that contains the old path at the UNIX level.
====================================================
find . -type f -print|grep -v ":"|xargs file|grep "text"|awk '{FS=":"}{print $1}'|xargs grep -il $OLD_PATH
How to Sort files based on Size of file in a Directory
=====================================
Useful in finding out spaces issues
ls -l | sort -nrk 5 | more
How to check if a Port is listening for any Service
====================================
netstat -an | grep $PORTNO
