Oracle: V$PX_SESSION

v$px_session shows information about sessions that are executing an SQL statement parallelly.

select
  count(*),
  pxs.qcsid,
  spx.osuser,
  spx.username,
  max(case when spx.sid = pxs.qcsid then spx.program end) program,
  spx.module,
  round((sysdate - min(spx.sql_exec_start)) * 24*60*60) sql_start_s_ago,
  round(max(spx.wait_time_micro) / 1000/1000, 2) max_wait_s,
  round(min(spx.wait_time_micro) / 1000/1000, 2) min_wait_s,
  max(spx.event) keep (dense_rank first order by spx.wait_time_micro desc) event_max_waited,
  sql.sql_text,
  max(pxs.qcserial#)   qcserial#,
  count(         spx.taddr)     cnt_txn,
  count(distinct spx.taddr)     cnt_dist_txn
from
  v$px_session  pxs                                    join
  v$session     spx on pxs.sid     = spx.sid and
                       pxs.serial# = spx.serial#  left join
  v$sqlarea     sql on spx.sql_id  = sql.sql_id
where
  spx.osuser = 'rnyffenegger'
group by
  spx.osuser,
  spx.username,
  spx.module,
  sql.sql_text,
  pxs.qcsid
--pxs.qcserial#
order by
  count(*) desc;

select
   pxs.qcsid,
-- spx.osuser,
-- spx.username,
-- spx.program,
-- spx.module,
   round((sysdate -         spx.sql_exec_start                    ) * 24*60*60) sql_start_s_ago,
   round((sysdate - to_date(trx.start_time, 'mm/dd/rr hh24:mi:ss')) * 24*60*60) trx_start_s_ago,
   round(spx.wait_time_micro / 1000/1000, 2) max_wait_s,
   trx.used_ublk,
   trx.used_urec,
   spx.event,
   -----------------------
   slo.opname               slo_opname,
   slo.target               slo_target,
   slo.sofar                slo_sofar,
   slo.totalwork            slo_totalwork,
   slo.elapsed_seconds      slo_elapsed_sconds,
-- round((sysdate -         slo.start_time                        ) * 24*60*60) slo_start_s_ago,
   slo.time_remaining       slo_time_remaining,
   -----------------------
   sql.sql_text,
   pxs.qcserial#   qcserial#
from
   v$px_session      pxs                                         join
   v$session         spx on pxs.sid     = spx.sid      and
                            pxs.serial# = spx.serial#       left join
   v$sqlarea         sql on spx.sql_id  = sql.sql_id        left join
   v$transaction     trx on spx.taddr   = trx.addr          left join
   v$session_longops slo on spx.sid     = slo.sid      and
                            spx.serial# = slo.serial#
where
-- spx.osuser = 'rnyffenegger'
   pxs.qcsid  =  1661
order by
    pxs.qcsid,
    slo.time_remaining desc
;