-- Check for tables not partitioned and query only execute serially

SELECT u.name, o.name 
FROM obj$ o, tab$ t, user$ u
WHERE o.obj# = t.obj# AND o.owner# = u.user#
AND bitand(t.property,536870912) != 536870912
;

-- Parallel process status details

SELECT inst_id, status, COUNT(*) px_servers
FROM  gv$px_process
GROUP BY  inst_id, status;

-- Parallel session details

SELECT
  qcsid, sid, inst_id, server_group , server_set, degree, req_degree
FROM gv$px_session
ORDER BY qcsid, inst_id, server_group , server_set
;

-- Parallel execution session and query (SQL text)
WITH parallel_xsn AS (
   SELECT 
      qcsid, qcserial#, 
      MAX(degree) max_degree,
      MAX(req_degree) max_req_degree,
      COUNT(*) process_cnt
   FROM v$px_session
   GROUP BY qcsid, qcserial#
)
SELECT 
   s.sid, s.username, 
   px.max_degree, px.max_req_degree, px.process_cnt, 
   vq.sql_text
FROM  v$session s 
JOIN  parallel_xsn px 
  ON (s.sid = px.qcsid AND  s.serial# = px.qcserial#)
JOIN  v$sql vq 
  ON (vq.sql_id = s.sql_id AND vq.child_number = s.sql_child_number) 
;

SELECT dfo_number, tq_id, server_type, process, num_rows
FROM v$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server__type, process
;


SELECT
  pxsn.qcsid, pxsn.sid, pxsn.inst_id, pxsn.server_group,
  pxsn.server_set, pxsn.name stat_name, sn.value 
FROM gv$px_sesstat pxsn, v$statname sn
WHERE pxsn.statistic# = sn.statistic#
AND  INSTR(UPPER(sn.name), 'PHYSICAL READS')>0
AND  sn.value > 0 
ORDER BY
  pxsn.qcsid, pxsn.inst_id, pxsn.server_group, pxsn.server_set
;



SELECT px.sid, p.pid, p.spid, px.qcsid, px.qcinst_id, px.inst_id, 
       px.server_group, px.server_set,
       px.degree, px.req_degree, w.event "Wait Event"
FROM gv$session s,
     gv$px_session px,
     gv$processS p,
     gv$session_wait w
WHERE px.sid = s.sid (+)
AND   px.inst_id = s.inst_id (+)
AND   s.sid = w.sid (+) 
AND   s.inst_id = w.inst_id (+) 
AND   s.paddr = p.addr (+) 
AND   s.inst_id = p.inst_id (+)
ORDER BY
  --DECODE(px.qcinst_id, NULL, px.inst_id, px.qcinst_id),
  --DECODE(px.server_group, NULL, 0, px.server_group),
  NVL(px.qcinst_id,px.inst_id),
  px.qcsid, 
  NVL(px.server_group,0),
  px.server_set,
  px.inst_id
;