PERFORMACE QUERIES

Find Connected users:
================
set pagesize 100 
set linesize 150 
column sid format 999999 
column username format a15 truncated 
column status format a1 truncated 
column osuser format a10 truncated 
column machine format a15 truncated 
column program format a15 truncated 
column type format a6 
column logon_time heading 'LOGON|TIME' format a19 
column last_call_et heading 'IDLE|TIME|(M)' format 99999 
select sid,username,status,osuser, 
machine,program,type, 
to_char(logon_time,'mm-dd-yyyy hh24:mi:ss') "logon_time", 
round(last_call_et/60,0) "last_call_et" 
from v$session 
where type='USER' 
and username is not null 
order by status ;


Currently running SQLs: 
==================

SELECT se.username,se.program,se.sid,se.serial#,se.machine,sq.sql_text,sq.sql_id 
FROM v$session se, v$sql sq 
WHERE se.sql_address = sq.address;


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

/* 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"   ;



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

/* 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
*/
set linesize 500
set pagesize 500
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;