Detailed Session Overview

The query below shows all sessions together with the current SQL and transaction information. It includes parallel execution servers which are being used.

select px.qcsid "QC SID"
,      s.sid "SID"
,      s.serial# "Serial"
,      s.logon_time "Logon Time"
,      decode(pp.server_name
                ,null,s.username
                     ,' - '||trim(pp.server_name)
             ) "User"
--,      s.schemaname "Schema"
--,      s.osuser "O/S User" 
--,      s.machine "Machine"
--,      s.process "Process Id"
--,      s.program "Program"
--,      s.module "Module"
--,      s.action "Action"
--,      s.client_info "Client Info"
,      s.status "Status"
,      s.lockwait "Lock Wait"
,      px.server_group "Grp"
,      px.server_set "Set"
,      s.state "Wait State"
,      case s.state 
         when 'WAITING' then s.event
         else null 
       end "Wait Event"
,      s.seconds_in_wait "Wait Sec"
,      px.req_degree "Req. DOP"
,      px.degree "Actual DOP"
,      s.pdml_status "PDML Status"
,      t.start_time "TRX Start"
,      t.log_io "Log I/O"
,      t.phy_io "Phy I/O"
,      t.used_ublk "UNDO Blks"
,      t.used_urec "UNDO Recs"
,      s.sql_id "SQL Id"
,      st.sql_text "SQL Text"
,      st.sql_fulltext "Full SQL"
,      st.executions "Executions"
,      st.disk_reads "Disk Reads"
,      st.sorts "Sorts"
,      st.buffer_gets "Buffer Gets"
,      st.sharable_mem "Shar Mem"
,      st.persistent_mem "Pers Mem"
,      st.runtime_mem "Run Mem" 
from   gv$px_session px
,      gv$session s 
,      gv$px_process pp
,      gv$transaction t
,      gv$sql st
where  s.sid = px.sid(+)
and    s.serial# = px.serial#(+)
and    s.inst_id = px.inst_id(+)
and    px.sid = pp.sid (+)
and    px.serial# = pp.serial#(+)
and    px.inst_id = pp.inst_id (+)
and    s.taddr = t.addr (+)
and    s.sql_address = st.address(+) 
and    s.sql_child_number = st.child_number(+)
-- restrictions
and    s.type != 'BACKGROUND'
--and    nvl(px.qcsid,s.sid) = 1630
--and    nvl(px.qcserial#,s.serial#) = 68
order by px.qcsid
,        px.server_group nulls first
,        px.server_set
;