Check parallel queries detail in Oracle

Check the parallel parameter at Instance level

select name, value from v$parameter where name like '%parallel%' ;


Check the Parallel Queries

col username for a9
col sid for a8
set lines 299
select s.inst_id,
decode(px.qcinst_id,NULL,s.username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP", p.spid
from gv$px_session px, gv$session s, gv$process p
where px.sid=s.sid (+) and px.serial#=s.serial# and
px.inst_id = s.inst_id and p.inst_id = s.inst_id
and p.addr=s.paddr
order by 5 , 1 desc;


Check the parallel queries running in Oracle

SELECT
P.SID,
DECODE(SERVER_SET, NULL, 'COORDINATOR', 1, ' CONSUMER',
' PRODUCER') ROLE,
DECODE(SW.WAIT_TIME, 0, SW.EVENT, 'CPU') ACTION,
SQ.SQL_TEXT
FROM V$PX_SESSION P, V$SESSION_WAIT SW, V$SQL SQ, V$SESSION S, AUDIT_ACTIONS AA
WHERE
P.SID = SW.SID AND
S.SID = P.SID AND
S.SQL_ADDRESS = SQ.ADDRESS (+) AND
AA.ACTION = S.COMMAND
ORDER BY P.QCSID, NVL(P.SERVER#, 0), P.SERVER_SET;


Check the progress of parallel queries worked completed

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)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(px.inst_id) "Slave INST",
substr(opname,1,30) operation_name,
substr(target,1,30) target,
sofar,
totalwork,
units,
start_time,
timestamp,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST"
from gv$px_session px,
gv$px_process pp,
gv$session_longops s
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#(+)
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;