Who's connected and what are they running?

When the database is slow, the first question is always: "What's happening right now?" These queries give you an immediate picture of current database activity.

Recipe #1 - List all active sessions

SELECT s.sid, s.serial#, s.username, s.status,
       s.osuser, s.machine, s.program,
       s.sql_id, s.last_call_et AS seconds_active
FROM v$session s
WHERE s.type = 'USER'
  AND s.status = 'ACTIVE'
ORDER BY s.last_call_et DESC;

Recipe #2 - See the SQL a session is running

Once you've identified a suspicious session, get the full SQL text:

SELECT s.sid, s.serial#, s.username,
       sq.sql_fulltext, sq.elapsed_time / 1000000 AS elapsed_seconds,
       sq.executions, sq.buffer_gets
FROM v$session s
JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.status = 'ACTIVE'
  AND s.type = 'USER'
ORDER BY sq.elapsed_time DESC;

Recipe #3 - Count sessions by status

Get a quick overview of how many sessions are connected:

SELECT status, COUNT(*) AS session_count
FROM v$session
WHERE type = 'USER'
GROUP BY status;

Recipe #4 - Find sessions consuming the most resources

SELECT s.sid, s.serial#, s.username, s.program,
       io.block_gets + io.consistent_gets AS logical_reads,
       io.physical_reads,
       io.block_changes
FROM v$session s
JOIN v$sess_io io ON s.sid = io.sid
WHERE s.type = 'USER'
ORDER BY logical_reads DESC
FETCH FIRST 10 ROWS ONLY;

Recipe #5 - Kill a session

When you need to terminate a problematic session:

-- Graceful disconnect
ALTER SYSTEM DISCONNECT SESSION '&sid,&serial' POST_TRANSACTION;

-- Immediate kill
ALTER SYSTEM KILL SESSION '&sid,&serial' IMMEDIATE;

Use DISCONNECT with POST_TRANSACTION when possible — it lets the current transaction complete before disconnecting the session. KILL IMMEDIATE is the nuclear option.