How to check top wait events for a session in Oracle

V$SESSION_EVENT aggregates every wait the session has done since it connected. Ranking by TIME_WAITED tells you where its time actually went — CPU, I/O, locks, or network.

ACCEPT p_sid NUMBER PROMPT 'Enter SID: '

SELECT sid, event, total_waits, total_timeouts,
       ROUND(time_waited   / 100, 2) AS time_waited_sec,
       ROUND(average_wait  / 100, 2) AS avg_wait_sec,
       wait_class
FROM   v$session_event
WHERE  sid = &&p_sid
ORDER BY time_waited DESC;