Queries : Concurrent Managers/Processes

Running sql statements

select addr from v$process where spid='8419'
select * from v$session where PADDR='00000003B29F9388'

select a.sid Current_SID, a.last_call_et ,b.sql_text
from v$session a
,v$sqltext b
where a.sid = 14
and a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by a.last_call_et,a.sid,b.piece;

Showing what sql statement is doing

select a.sid, a.value session_cpu, c.physical_reads,
c.consistent_gets,d.event,
d.seconds_in_wait
from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d
where a.sid= 14
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid; 

Check all active processes, the latest SQL, and the SQL hit ratio

select a.status, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
and a.status = 'ACTIVE'
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;

Monitoring Oracle processes

select p.spid "Thread ID", b.name "Background Process", s.username
"User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr and b.paddr(+) = p.addr
order by s.status,1;

Displays concurrent requests that have run times longer than one hour (3600 seconds)
SELECT REQUEST_ID,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') starttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,
OUTCOME_CODE,phase_code,status_code,
printer,print_style,description,
SUBSTR(completion_text,1,20) compl_txt
FROM fnd_concurrent_requests
WHERE to_date(ACTUAL_START_DATE,'DD-MON-RRRR') = to_date(sysdate,'DD-
MON-RRRR')
ORDER BY 2 desc;


This script will map concurrent manager process information about current concurrent managers.

SELECT proc.concurrent_process_id concproc,
SUBSTR(proc.os_process_id,1,6) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(vproc.spid,1,10) svrproc,
DECODE(proc.process_status_code,'A','Active',
proc.process_status_code) cstat,
SUBSTR(concq.concurrent_queue_name,1,30) qnam,
-- SUBSTR(proc.logfile_name,1,20) lnam,
SUBSTR(proc.node_name,1,10) nnam,
SUBSTR(proc.db_name,1,8) dbnam,
SUBSTR(proc.db_instance,1,8) dbinst,
SUBSTR(vsess.username,1,10) dbuser
FROM fnd_concurrent_processes proc,
fnd_concurrent_queues concq,
v$process vproc,
v$session vsess
WHERE proc.process_status_code = 'A'
AND proc.queue_application_id = concq.application_id
AND proc.concurrent_queue_id = concq.concurrent_queue_id
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+)
ORDER BY proc.queue_application_id,
proc.concurrent_queue_id ;


Show currently running concurrent requests

SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
SUBSTR(vproc.spid,1,10) svrproc,
vsess.sid sid,
vsess.serial# serial#
FROM fnd_concurrent_requests req,
fnd_concurrent_processes proc,
fnd_lookups look,
fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+);


FIND THE SORTING DETAILS

SELECT a.sid,a.value,b.name from
V$SESSTAT a, V$STATNAME b
WHERE a.statistic#=b.statistic#
AND b.name LIKE 'sort%'
ORDER BY 1;


find the CPU consumption

select ss.sid,w.event,command,ss.value CPU ,se.username,se.program, wait_time, w.seq#, q.sql_text,command
from
v$sesstat ss, v$session se,v$session_wait w,v$process p, v$sqlarea q
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
and se.paddr=p.addr
and se.sql_address=q.address
order by ss.value desc,ss.sid ;