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 > 10
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;
Find the status of gather schema statistics:
-----------------------------------------------------
select count(*)
from apps.fnd_concurrent_programs_vl p , apps.fnd_concurrent_requests r
where r.concurrent_program_id = p.concurrent_program_id
and r.program_application_id = p.application_id
and p.user_concurrent_program_name in (
'OnDemand Gather Schema Statistics',
'Gather Schema Statistics',
'Gather Schema Statistics (IT_ANALYZE)'
)
and (r.phase_code = 'C' and r.status_code= 'C' and r.actual_start_date >= sysdate-1);
Find Gather schema statistics request id:
==========================================
set pages 1000
set line 132
set head on
select r.request_id,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date,argument_text from
apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.user_concurrent_program_name like '%Gather Schema Statistics%'
and r.actual_start_date >= sysdate-2 order by r.requested_start_date;