SQL Performance

Identify SQL statements that have consumed the most total elapsed time in the shared SQL area.

SELECT
    sql_id,
    plan_hash_value,
    executions,
    ROUND(elapsed_time / 1000000, 2) AS elapsed_seconds,
    ROUND(
        CASE
            WHEN executions > 0
            THEN elapsed_time / executions / 1000
        END,
        2
    ) AS avg_elapsed_ms,
    sql_text
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

Find statements responsible for significant CPU consumption and focus further investigation on their execution behaviour.

SELECT
    sql_id,
    plan_hash_value,
    executions,
    ROUND(cpu_time / 1000000, 2) AS cpu_seconds,
    ROUND(
        CASE
            WHEN executions > 0
            THEN cpu_time / executions / 1000
        END,
        2
    ) AS avg_cpu_ms,
    sql_text
FROM v$sql
WHERE executions > 0
ORDER BY cpu_time DESC
FETCH FIRST 20 ROWS ONLY;

Identify statements performing large amounts of logical I/O and investigate inefficient access paths or excessive work.

SELECT
    sql_id,
    plan_hash_value,
    executions,
    buffer_gets,
    ROUND(
        CASE
            WHEN executions > 0
            THEN buffer_gets / executions
        END
    ) AS avg_buffer_gets,
    sql_text
FROM v$sql
WHERE executions > 0
ORDER BY buffer_gets DESC
FETCH FIRST 20 ROWS ONLY;

Find statements generating significant physical reads and identify SQL that may be driving unnecessary I/O.

SELECT
    sql_id,
    plan_hash_value,
    executions,
    disk_reads,
    ROUND(
        CASE
            WHEN executions > 0
            THEN disk_reads / executions
        END
    ) AS avg_disk_reads,
    sql_text
FROM v$sql
WHERE executions > 0
ORDER BY disk_reads DESC
FETCH FIRST 20 ROWS ONLY;

Compare executions, elapsed time, CPU, logical reads and physical reads to understand how a statement is behaving.

SELECT
    sql_id,
    plan_hash_value,
    executions,
    ROUND(elapsed_time / 1000000, 2) AS elapsed_seconds,
    ROUND(cpu_time / 1000000, 2) AS cpu_seconds,
    buffer_gets,
    disk_reads,
    rows_processed
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

Locate a statement by SQL ID and inspect its text together with the main execution statistics.

SELECT
    sql_id,
    plan_hash_value,
    executions,
    ROUND(elapsed_time / 1000000, 2) AS elapsed_seconds,
    ROUND(cpu_time / 1000000, 2) AS cpu_seconds,
    buffer_gets,
    disk_reads,
    sql_text
FROM v$sql
WHERE sql_id = '&sql_id';