Runbook9 steps

The database is slow

Work from the outside in: overall load, what sessions are waiting on, who is blocking, and which sessions and statements are burning the most. Read-only and needs no extra license.

Load right now (last 60 seconds)Host CPU, average active sessions, transactions, redo and I/O rates from V$SYSMETRIC. Compare average active sessions with your CPU count to judge whether the…
What active sessions are doing right nowActive user sessions grouped by wait event, with CPU shown separately. A quick live picture before you reach for ASH.
Active user sessionsUser sessions doing work right now on every instance: who, from which OS user and machine, the module, what they're waiting on, the SQL_ID, when they logged on…
Blocking treeThe whole chain as an indented tree, starting from the root blocker. When ten sessions are stuck, the one at the left margin is the one to deal with.
Blocked sessions and their blockersSessions waiting on another session, joined to the blocker. Both sides show as SID,SERIAL#, ready to paste into a kill. On RAC, also check blocking_instance.
Top sessions by CPU usedConnected sessions ranked by total CPU seconds since they logged on. Good for finding the one batch job or report hammering the box.
Top 20 SQL by elapsed timeFrom V$SQLSTATS, which is cheaper to query than V$SQL. Feed the SQL_ID into the execution plan script to see how it runs.
Sessions using temp spaceWho is using temp right now and for what (sort, hash, LOB), largest first. Run it when you see ORA-01652.
Long-running operations in progressFull scans, sorts, RMAN and index builds that Oracle tracks in V$SESSION_LONGOPS, with percent done and estimated seconds remaining.

Open in denrepo

The whole runbook as one file: SQL> @slow

slow.sql

1-- denrepo runbook: The database is slow2-- Work from the outside in: overall load, what sessions are waiting on, who is blocking, and which sessions and statements are burning the most. Read-only and needs no extra license.3-- Source: https://denrepo.com/runbooks/slow/4 5SET ECHO OFF6 7-- Not yet verified8PROMPT9PROMPT ==== 1 of 9: Load right now (last 60 seconds) ====10CLEAR COLUMNS11SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF12COLUMN metric_name HEADING "METRIC" FORMAT A4013COLUMN value       FORMAT 999,999,999,990.914COLUMN metric_unit HEADING "UNIT"   FORMAT A35 TRUNCATE15 16SELECT metric_name, ROUND(value, 1) AS value, metric_unit17FROM v$sysmetric18WHERE group_id = 219  AND metric_name IN ('Host CPU Utilization (%)', 'Average Active Sessions',20                      'Database CPU Time Ratio', 'Executions Per Sec',21                      'User Transaction Per Sec', 'Physical Read Total Bytes Per Sec',22                      'Redo Generated Per Sec', 'Logons Per Sec', 'Current Logons Count')23ORDER BY metric_name;24 25-- Not yet verified26PROMPT27PROMPT ==== 2 of 9: What active sessions are doing right now ====28CLEAR COLUMNS29SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF30COLUMN activity   FORMAT A4531COLUMN wait_class HEADING "WAIT CLASS" FORMAT A1532COLUMN sessions   FORMAT 9,99033 34SELECT CASE WHEN state = 'WAITING' THEN event ELSE 'ON CPU' END AS activity,35       CASE WHEN state = 'WAITING' THEN wait_class ELSE 'CPU' END AS wait_class,36       COUNT(*) AS sessions37FROM v$session38WHERE status = 'ACTIVE'39  AND type = 'USER'40  AND (state <> 'WAITING' OR wait_class <> 'Idle')41GROUP BY CASE WHEN state = 'WAITING' THEN event ELSE 'ON CPU' END,42         CASE WHEN state = 'WAITING' THEN wait_class ELSE 'CPU' END43ORDER BY sessions DESC;44 45-- Not yet verified46PROMPT47PROMPT ==== 3 of 9: Active user sessions ====48CLEAR COLUMNS49SET LINESIZE 250 PAGESIZE 100 TRIMOUT ON TAB OFF50COLUMN inst       HEADING "INST" FORMAT 99051COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A1452COLUMN username   FORMAT A2053COLUMN osuser     FORMAT A15 TRUNCATE54COLUMN machine    FORMAT A25 TRUNCATE55COLUMN module     FORMAT A25 TRUNCATE56COLUMN event      FORMAT A30 TRUNCATE57COLUMN sql_id     FORMAT A1358COLUMN logged_on  HEADING "LOGGED ON" FORMAT A1759COLUMN in_state   HEADING "IN STATE" FORMAT A1060 61SELECT s.inst_id AS inst,62       s.sid || ',' || s.serial# AS sid_serial,63       s.username, s.osuser, s.machine, s.module, s.event, s.sql_id,64       TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI') AS logged_on,65       TO_CHAR(TRUNC(s.last_call_et / 3600), 'FM9900') || ':' ||66       TO_CHAR(TRUNC(MOD(s.last_call_et, 3600) / 60), 'FM00') || ':' ||67       TO_CHAR(MOD(s.last_call_et, 60), 'FM00') AS in_state68FROM gv$session s69WHERE s.type = 'USER'70  AND s.status = 'ACTIVE'71  AND NOT (s.inst_id = TO_NUMBER(SYS_CONTEXT('USERENV', 'INSTANCE'))72           AND s.sid = TO_NUMBER(SYS_CONTEXT('USERENV', 'SID')))73ORDER BY s.last_call_et DESC;74 75-- Not yet verified76PROMPT77PROMPT ==== 4 of 9: Blocking tree ====78CLEAR COLUMNS79SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF80COLUMN tree      HEADING "SID,SERIAL# TREE" FORMAT A2481COLUMN username  FORMAT A1582COLUMN status    FORMAT A883COLUMN event     FORMAT A35 TRUNCATE84COLUMN sql_id    FORMAT A1385COLUMN wait_secs HEADING "WAIT SECS" FORMAT 999,99986 87SELECT LPAD(' ', 2 * (LEVEL - 1)) || s.sid || ',' || s.serial# AS tree,88       s.username, s.status, s.event,89       NVL(s.sql_id, s.prev_sql_id) AS sql_id,90       s.seconds_in_wait AS wait_secs91FROM v$session s92START WITH s.blocking_session IS NULL93       AND s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)94CONNECT BY PRIOR s.sid = s.blocking_session;95 96-- Not yet verified97PROMPT98PROMPT ==== 5 of 9: Blocked sessions and their blockers ====99CLEAR COLUMNS100SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF101COLUMN blocked        HEADING "BLOCKED"      FORMAT A14102COLUMN username       HEADING "USER"         FORMAT A15103COLUMN event          FORMAT A30 TRUNCATE104COLUMN wait_secs      HEADING "WAIT SECS"    FORMAT 999,999105COLUMN blocker        HEADING "BLOCKER"      FORMAT A14106COLUMN blocker_user   HEADING "BLOCKER USER" FORMAT A15107COLUMN blocker_status HEADING "STATUS"       FORMAT A8108COLUMN blocker_sql_id HEADING "BLOCKER SQL"  FORMAT A13109 110SELECT s.sid || ',' || s.serial# AS blocked,111       s.username, s.event,112       s.seconds_in_wait AS wait_secs,113       b.sid || ',' || b.serial# AS blocker,114       b.username AS blocker_user,115       b.status AS blocker_status,116       NVL(b.sql_id, b.prev_sql_id) AS blocker_sql_id117FROM v$session s118JOIN v$session b ON b.sid = s.blocking_session119WHERE s.blocking_session IS NOT NULL120ORDER BY s.seconds_in_wait DESC;121 122-- Not yet verified123PROMPT124PROMPT ==== 6 of 9: Top sessions by CPU used ====125CLEAR COLUMNS126SET LINESIZE 160 PAGESIZE 100 TRIMOUT ON TAB OFF127COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A14128COLUMN username   FORMAT A15129COLUMN program    FORMAT A35 TRUNCATE130COLUMN status     FORMAT A8131COLUMN sql_id     FORMAT A13132COLUMN cpu_sec    HEADING "CPU SEC" FORMAT 999,999,990133 134SELECT * FROM (135  SELECT s.sid || ',' || s.serial# AS sid_serial,136         s.username, s.program, s.status, s.sql_id,137         ROUND(st.value / 100) AS cpu_sec138  FROM v$sesstat st139  JOIN v$statname n ON n.statistic# = st.statistic#140  JOIN v$session s ON s.sid = st.sid141  WHERE n.name = 'CPU used by this session'142    AND s.type = 'USER'143  ORDER BY st.value DESC144) WHERE ROWNUM <= 15;145 146-- Not yet verified147PROMPT148PROMPT ==== 7 of 9: Top 20 SQL by elapsed time ====149CLEAR COLUMNS150SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF151COLUMN sql_id      FORMAT A13152COLUMN executions  HEADING "EXECS"       FORMAT 999,999,999153COLUMN elapsed_sec HEADING "ELAPSED SEC" FORMAT 999,999,990154COLUMN avg_ms      HEADING "AVG MS"      FORMAT 999,999,990.9155COLUMN buffer_gets HEADING "BUFFER GETS" FORMAT 999,999,999,999156COLUMN disk_reads  HEADING "DISK READS"  FORMAT 999,999,999,999157COLUMN sql_text    FORMAT A50 WORD_WRAPPED158 159SELECT * FROM (160  SELECT sql_id, executions,161         ROUND(elapsed_time / 1e6) AS elapsed_sec,162         ROUND(elapsed_time / NULLIF(executions, 0) / 1e3, 1) AS avg_ms,163         buffer_gets, disk_reads,164         SUBSTR(sql_text, 1, 120) AS sql_text165  FROM v$sqlstats166  ORDER BY elapsed_time DESC167) WHERE ROWNUM <= 20;168 169-- Not yet verified170PROMPT171PROMPT ==== 8 of 9: Sessions using temp space ====172CLEAR COLUMNS173SET LINESIZE 160 PAGESIZE 100 TRIMOUT ON TAB OFF174COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A14175COLUMN username   FORMAT A15176COLUMN program    FORMAT A30 TRUNCATE177COLUMN sql_id     FORMAT A13178COLUMN tablespace FORMAT A12179COLUMN segtype    HEADING "USE"     FORMAT A9180COLUMN used_mb    HEADING "USED MB" FORMAT 9,999,990181 182SELECT s.sid || ',' || s.serial# AS sid_serial,183       s.username, s.program, u.sql_id, u.tablespace, u.segtype,184       ROUND(u.blocks * t.block_size / 1048576) AS used_mb185FROM v$tempseg_usage u186JOIN v$session s ON s.saddr = u.session_addr187JOIN dba_tablespaces t ON t.tablespace_name = u.tablespace188ORDER BY u.blocks DESC;189 190-- Not yet verified191PROMPT192PROMPT ==== 9 of 9: Long-running operations in progress ====193CLEAR COLUMNS194SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF195COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A14196COLUMN opname     HEADING "OPERATION"   FORMAT A30 TRUNCATE197COLUMN target     FORMAT A35 TRUNCATE198COLUMN pct_done   HEADING "DONE %"      FORMAT 990.9199COLUMN elapsed_s  HEADING "ELAPSED S"   FORMAT 999,990200COLUMN remain_s   HEADING "REMAINING S" FORMAT 999,990201COLUMN sql_id     FORMAT A13202 203SELECT sid || ',' || serial# AS sid_serial,204       opname, target,205       ROUND(100 * sofar / NULLIF(totalwork, 0), 1) AS pct_done,206       elapsed_seconds AS elapsed_s,207       time_remaining AS remain_s,208       sql_id209FROM v$session_longops210WHERE sofar < totalwork211ORDER BY start_time;212

-- denrepo runbook: The database is slow
-- Work from the outside in: overall load, what sessions are waiting on, who is blocking, and which sessions and statements are burning the most. Read-only and needs no extra license.
-- Source: https://denrepo.com/runbooks/slow/

SET ECHO OFF

-- Not yet verified
PROMPT
PROMPT ==== 1 of 9: Load right now (last 60 seconds) ====
CLEAR COLUMNS
SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN metric_name HEADING "METRIC" FORMAT A40
COLUMN value       FORMAT 999,999,999,990.9
COLUMN metric_unit HEADING "UNIT"   FORMAT A35 TRUNCATE

SELECT metric_name, ROUND(value, 1) AS value, metric_unit
FROM v$sysmetric
WHERE group_id = 2
  AND metric_name IN ('Host CPU Utilization (%)', 'Average Active Sessions',
                      'Database CPU Time Ratio', 'Executions Per Sec',
                      'User Transaction Per Sec', 'Physical Read Total Bytes Per Sec',
                      'Redo Generated Per Sec', 'Logons Per Sec', 'Current Logons Count')
ORDER BY metric_name;

-- Not yet verified
PROMPT
PROMPT ==== 2 of 9: What active sessions are doing right now ====
CLEAR COLUMNS
SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN activity   FORMAT A45
COLUMN wait_class HEADING "WAIT CLASS" FORMAT A15
COLUMN sessions   FORMAT 9,990

SELECT CASE WHEN state = 'WAITING' THEN event ELSE 'ON CPU' END AS activity,
       CASE WHEN state = 'WAITING' THEN wait_class ELSE 'CPU' END AS wait_class,
       COUNT(*) AS sessions
FROM v$session
WHERE status = 'ACTIVE'
  AND type = 'USER'
  AND (state <> 'WAITING' OR wait_class <> 'Idle')
GROUP BY CASE WHEN state = 'WAITING' THEN event ELSE 'ON CPU' END,
         CASE WHEN state = 'WAITING' THEN wait_class ELSE 'CPU' END
ORDER BY sessions DESC;

-- Not yet verified
PROMPT
PROMPT ==== 3 of 9: Active user sessions ====
CLEAR COLUMNS
SET LINESIZE 250 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN inst       HEADING "INST" FORMAT 990
COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A14
COLUMN username   FORMAT A20
COLUMN osuser     FORMAT A15 TRUNCATE
COLUMN machine    FORMAT A25 TRUNCATE
COLUMN module     FORMAT A25 TRUNCATE
COLUMN event      FORMAT A30 TRUNCATE
COLUMN sql_id     FORMAT A13
COLUMN logged_on  HEADING "LOGGED ON" FORMAT A17
COLUMN in_state   HEADING "IN STATE" FORMAT A10

SELECT s.inst_id AS inst,
       s.sid || ',' || s.serial# AS sid_serial,
       s.username, s.osuser, s.machine, s.module, s.event, s.sql_id,
       TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI') AS logged_on,
       TO_CHAR(TRUNC(s.last_call_et / 3600), 'FM9900') || ':' ||
       TO_CHAR(TRUNC(MOD(s.last_call_et, 3600) / 60), 'FM00') || ':' ||
       TO_CHAR(MOD(s.last_call_et, 60), 'FM00') AS in_state
FROM gv$session s
WHERE s.type = 'USER'
  AND s.status = 'ACTIVE'
  AND NOT (s.inst_id = TO_NUMBER(SYS_CONTEXT('USERENV', 'INSTANCE'))
           AND s.sid = TO_NUMBER(SYS_CONTEXT('USERENV', 'SID')))
ORDER BY s.last_call_et DESC;

-- Not yet verified
PROMPT
PROMPT ==== 4 of 9: Blocking tree ====
CLEAR COLUMNS
SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN tree      HEADING "SID,SERIAL# TREE" FORMAT A24
COLUMN username  FORMAT A15
COLUMN status    FORMAT A8
COLUMN event     FORMAT A35 TRUNCATE
COLUMN sql_id    FORMAT A13
COLUMN wait_secs HEADING "WAIT SECS" FORMAT 999,999

SELECT LPAD(' ', 2 * (LEVEL - 1)) || s.sid || ',' || s.serial# AS tree,
       s.username, s.status, s.event,
       NVL(s.sql_id, s.prev_sql_id) AS sql_id,
       s.seconds_in_wait AS wait_secs
FROM v$session s
START WITH s.blocking_session IS NULL
       AND s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
CONNECT BY PRIOR s.sid = s.blocking_session;

-- Not yet verified
PROMPT
PROMPT ==== 5 of 9: Blocked sessions and their blockers ====
CLEAR COLUMNS
SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN blocked        HEADING "BLOCKED"      FORMAT A14
COLUMN username       HEADING "USER"         FORMAT A15
COLUMN event          FORMAT A30 TRUNCATE
COLUMN wait_secs      HEADING "WAIT SECS"    FORMAT 999,999
COLUMN blocker        HEADING "BLOCKER"      FORMAT A14
COLUMN blocker_user   HEADING "BLOCKER USER" FORMAT A15
COLUMN blocker_status HEADING "STATUS"       FORMAT A8
COLUMN blocker_sql_id HEADING "BLOCKER SQL"  FORMAT A13

SELECT s.sid || ',' || s.serial# AS blocked,
       s.username, s.event,
       s.seconds_in_wait AS wait_secs,
       b.sid || ',' || b.serial# AS blocker,
       b.username AS blocker_user,
       b.status AS blocker_status,
       NVL(b.sql_id, b.prev_sql_id) AS blocker_sql_id
FROM v$session s
JOIN v$session b ON b.sid = s.blocking_session
WHERE s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

-- Not yet verified
PROMPT
PROMPT ==== 6 of 9: Top sessions by CPU used ====
CLEAR COLUMNS
SET LINESIZE 160 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A14
COLUMN username   FORMAT A15
COLUMN program    FORMAT A35 TRUNCATE
COLUMN status     FORMAT A8
COLUMN sql_id     FORMAT A13
COLUMN cpu_sec    HEADING "CPU SEC" FORMAT 999,999,990

SELECT * FROM (
  SELECT s.sid || ',' || s.serial# AS sid_serial,
         s.username, s.program, s.status, s.sql_id,
         ROUND(st.value / 100) AS cpu_sec
  FROM v$sesstat st
  JOIN v$statname n ON n.statistic# = st.statistic#
  JOIN v$session s ON s.sid = st.sid
  WHERE n.name = 'CPU used by this session'
    AND s.type = 'USER'
  ORDER BY st.value DESC
) WHERE ROWNUM <= 15;

-- Not yet verified
PROMPT
PROMPT ==== 7 of 9: Top 20 SQL by elapsed time ====
CLEAR COLUMNS
SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN sql_id      FORMAT A13
COLUMN executions  HEADING "EXECS"       FORMAT 999,999,999
COLUMN elapsed_sec HEADING "ELAPSED SEC" FORMAT 999,999,990
COLUMN avg_ms      HEADING "AVG MS"      FORMAT 999,999,990.9
COLUMN buffer_gets HEADING "BUFFER GETS" FORMAT 999,999,999,999
COLUMN disk_reads  HEADING "DISK READS"  FORMAT 999,999,999,999
COLUMN sql_text    FORMAT A50 WORD_WRAPPED

SELECT * FROM (
  SELECT sql_id, executions,
         ROUND(elapsed_time / 1e6) AS elapsed_sec,
         ROUND(elapsed_time / NULLIF(executions, 0) / 1e3, 1) AS avg_ms,
         buffer_gets, disk_reads,
         SUBSTR(sql_text, 1, 120) AS sql_text
  FROM v$sqlstats
  ORDER BY elapsed_time DESC
) WHERE ROWNUM <= 20;

-- Not yet verified
PROMPT
PROMPT ==== 8 of 9: Sessions using temp space ====
CLEAR COLUMNS
SET LINESIZE 160 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A14
COLUMN username   FORMAT A15
COLUMN program    FORMAT A30 TRUNCATE
COLUMN sql_id     FORMAT A13
COLUMN tablespace FORMAT A12
COLUMN segtype    HEADING "USE"     FORMAT A9
COLUMN used_mb    HEADING "USED MB" FORMAT 9,999,990

SELECT s.sid || ',' || s.serial# AS sid_serial,
       s.username, s.program, u.sql_id, u.tablespace, u.segtype,
       ROUND(u.blocks * t.block_size / 1048576) AS used_mb
FROM v$tempseg_usage u
JOIN v$session s ON s.saddr = u.session_addr
JOIN dba_tablespaces t ON t.tablespace_name = u.tablespace
ORDER BY u.blocks DESC;

-- Not yet verified
PROMPT
PROMPT ==== 9 of 9: Long-running operations in progress ====
CLEAR COLUMNS
SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A14
COLUMN opname     HEADING "OPERATION"   FORMAT A30 TRUNCATE
COLUMN target     FORMAT A35 TRUNCATE
COLUMN pct_done   HEADING "DONE %"      FORMAT 990.9
COLUMN elapsed_s  HEADING "ELAPSED S"   FORMAT 999,990
COLUMN remain_s   HEADING "REMAINING S" FORMAT 999,990
COLUMN sql_id     FORMAT A13

SELECT sid || ',' || serial# AS sid_serial,
       opname, target,
       ROUND(100 * sofar / NULLIF(totalwork, 0), 1) AS pct_done,
       elapsed_seconds AS elapsed_s,
       time_remaining AS remain_s,
       sql_id
FROM v$session_longops
WHERE sofar < totalwork
ORDER BY start_time;