Parallel Process Details including Master/Slave relationships
SQL
set lines 200
set pages 100
col username format a10
col qcslave format a10
col slaveset format a8
col program format a30
col sid format a5
col slvinst format a7
col state format a8
col waitevent format a30
col qcsid format a5
col qcinst format a6
col reqdop format 999
col actdop format 999
col secelapsed format 999,999
SELECT DECODE(px.qcinst_id,NULL,username, ' - '||LOWER(SUBSTR(pp.SERVER_NAME,LENGTH(pp.SERVER_NAME)-4,4) ) ) USERNAME,
DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QCSLAVE" ,
TO_CHAR( px.server_set) SLAVESET,
s.program PROGRAM,
TO_CHAR(s.SID) SID,
TO_CHAR(px.inst_id) SLVINST,
DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) STATE,
CASE sw.state WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) ELSE NULL END WAITEVENT ,
DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) QCSID,
TO_CHAR(px.qcinst_id) QCINST,
px.req_degree REQDOP,
px.DEGREE ACTDOP,
DECODE(px.server_set,'',s.last_call_et,'') SECELAPSED
FROM gv$px_session px,
gv$session s,
gv$px_process pp,
gv$session_wait sw
WHERE px.SID=s.SID (+)
AND px.serial#=s.serial#(+)
AND px.inst_id = s.inst_id(+)
AND px.SID = pp.SID (+)
AND px.serial#=pp.serial#(+)
AND sw.SID = s.SID
AND sw.inst_id = s.inst_id
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID
A helpful script to find sessions using large amounts of system memory:
SET LINESIZE 140
SET PAGESIZE 100
COL session HEADING 'SID - User - Client' FORMAT a35
COL current_size HEADING 'Current MB' FORMAT '999,999.99'
COL maximum_size HEADING 'Max MB' FORMAT '999,999.99'
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF current_size ON REPORT
SELECT TO_CHAR(ssn.sid, '9999') || ' - ' ||
NVL(ssn.username, NVL(bgp.name, 'background')) || ' - ' ||
NVL(lower(ssn.machine), ins.host_name) "SESSION",
TO_CHAR(prc.spid, '999999999') "PID/THREAD",
se1.value/1024/1024 current_size,
se2.value/1024/1024 maximum_size
FROM v$sesstat se1,
v$sesstat se2,
v$session ssn,
v$bgprocess bgp,
v$process prc,
v$instance ins,
v$statname stat1,
v$statname stat2
WHERE se1.statistic# = stat1.statistic#
AND stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic#
AND stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+)
-- AND NVL(ssn.username, NVL(bgp.name, 'background')) = '<SCHEMA>'
ORDER BY 4
/
!free
exit
/
Sample output:
SID - User - Client PID/THREAD Current MB Max MB
----------------------------------- ---------- ----------- -----------
cut...
867 - XXXX - server01 9615 3.47 223.60
729 - XXXX - server01 9611 3.47 223.78
469 - XXXX - server01 3479 3.16 223.85
605 - XXXX - server03 4007 10.97 1,373.78
1039 - XXXX - server01 21216 11.16 1,373.97
448 - XXXX - server02 3852 10.22 1,378.91
-----------
Total 980.91
total used free shared buffers cached
Mem: 132085672 125739556 6346116 0 836680 24065240
-/+ buffers/cache: 100837636 31248036
Swap: 8193140 9968 8183172