Performance & Diagnostics

Start with instance-level wait time to identify where foreground activity is spending time.

SELECT
    event,
    wait_class,
    total_waits,
    ROUND(time_waited_micro / 1000000, 2) AS wait_seconds,
    ROUND(
        CASE
            WHEN total_waits > 0
            THEN time_waited_micro / total_waits / 1000
        END,
        2
    ) AS avg_wait_ms
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 20 ROWS ONLY;

Use wait classes to see whether the current pressure is concentrated in User I/O, Concurrency, Commit or another class.

SELECT
    wait_class,
    total_waits,
    ROUND(time_waited_micro / 1000000, 2) AS wait_seconds,
    ROUND(
        CASE
            WHEN total_waits > 0
            THEN time_waited_micro / total_waits / 1000
        END,
        2
    ) AS avg_wait_ms
FROM v$system_wait_class
WHERE wait_class <> 'Idle'
ORDER BY time_waited_micro DESC;

Combine session state, wait information and SQL identifiers to identify what is active right now.

SELECT
    sid,
    serial#,
    username,
    status,
    event,
    wait_class,
    state,
    seconds_in_wait,
    sql_id,
    blocking_session
FROM v$session
WHERE type = 'USER'
  AND status = 'ACTIVE'
ORDER BY seconds_in_wait DESC
FETCH FIRST 30 ROWS ONLY;

Inspect operations with measurable progress to understand what is running for an extended period.

SELECT
    sid,
    serial#,
    opname,
    target,
    sofar,
    totalwork,
    units,
    start_time,
    elapsed_seconds,
    time_remaining
FROM v$session_longops
WHERE totalwork > 0
  AND sofar < totalwork
ORDER BY elapsed_seconds DESC
FETCH FIRST 20 ROWS ONLY;

Compare current and maximum utilization against configured limits for key database resources.

SELECT
    resource_name,
    current_utilization,
    max_utilization,
    limit_value
FROM v$resource_limit
ORDER BY resource_name;

Use Real-Time SQL Monitoring data to inspect recent executions and their elapsed time, CPU and I/O activity.

SELECT
    m.sql_id,
    m.sql_exec_start,
    m.status,
    ROUND(m.elapsed_time / 1000000, 2) AS elapsed_seconds,
    ROUND(m.cpu_time / 1000000, 2) AS cpu_seconds,
    m.buffer_gets,
    m.disk_reads,
    SUBSTR(s.sql_text, 1, 1000) AS sql_text
FROM v$sql_monitor m
LEFT JOIN v$sql s
    ON s.sql_id = m.sql_id
WHERE m.sql_exec_start >= SYSDATE - 1/24
ORDER BY m.elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;