Yuan Yao, An Oracle ACE's Blog

-- This query retrieves information about active sessions and the SQL statements they are executing, 
-- along with details about their wait events, helping you diagnose and troubleshoot performance 
-- issues in real time.
-- Dated: July 2024
-- Author: Yuan Yao
col inst_sid heading "INST_ID|:SID" format a7
col username format a10
col machine format a12
col sql_exec_start heading "SQL|START|D HH:MM:SS" format a11
col sql_id format a13
col sql_text format a40
col event format a33
col wait_sec heading "WAIT|(SEC)" format 99999
set linesize 200
set pagesize 200

select ses.inst_id || chr(58) || ses.sid as inst_sid,
       username,
       (sysdate - sql_exec_start) day(1) to second(0) as sql_exec_start,
       ses.sql_id,
       substr(sql.sql_text, 1, 40) sql_text,
       substr(case time_since_last_wait_micro
                when 0 then (case wait_class when 'Idle' then 'IDLE: ' || event else event end)
                else 'ON CPU'
              end, 1, 33) event,
       (case time_since_last_wait_micro
          when 0 then wait_time_micro
          else time_since_last_wait_micro
        end) / 1000000 wait_secD
  from gv$session ses,
       gv$sqlstats sql
 where ses.inst_id || chr(58) || ses.sid <> sys_context('USERENV', 'INSTANCE') || chr(58) || sys_context('USERENV', 'SID')
   and username is not null
   and status = 'ACTIVE'
   and ses.sql_id = sql.sql_id(+)
 order by sql_exec_start,
          username,
          ses.sid,
          ses.sql_id;