Parallel Query Processes - Parent / Child Details
Posted by Superadmin on January 03 2016 12:11:52

---

Parallel Query Processes - Parent / Child Details
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