SQL Investigation

Review SQL currently represented in the shared SQL area, ordered by recent activity. This can help identify statements worth investigating further.

SELECT
    sql_id,
    child_number,
    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,
    last_active_time
FROM v$sql
WHERE last_active_time IS NOT NULL
ORDER BY last_active_time DESC
FETCH FIRST 50 ROWS ONLY;

A high execution count does not automatically mean a SQL statement is problematic. It can, however, help identify frequently executed statements that deserve further review.

SELECT
    sql_id,
    child_number,
    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,
    buffer_gets,
    disk_reads,
    last_active_time
FROM v$sql
WHERE executions > 0
ORDER BY executions DESC
FETCH FIRST 50 ROWS ONLY;

CPU time can help identify SQL statements that are consuming significant database CPU. Review the SQL in the context of executions and overall workload.

SELECT
    sql_id,
    child_number,
    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,
    buffer_gets,
    disk_reads,
    last_active_time
FROM v$sql
WHERE cpu_time > 0
ORDER BY cpu_time DESC
FETCH FIRST 50 ROWS ONLY;

Buffer gets represent logical reads and can help identify statements performing significant work in the buffer cache.

SELECT
    sql_id,
    child_number,
    executions,
    buffer_gets,
    ROUND(
        CASE
            WHEN executions > 0
            THEN buffer_gets / executions
        END
    ) AS buffer_gets_per_exec,
    disk_reads,
    rows_processed,
    last_active_time
FROM v$sql
WHERE buffer_gets > 0
ORDER BY buffer_gets DESC
FETCH FIRST 50 ROWS ONLY;

Once a SQL ID has been identified, use it to retrieve the SQL text for further investigation.

SELECT
    sql_id,
    child_number,
    sql_text
FROM v$sql
WHERE sql_id = '&SQL_ID'
ORDER BY child_number;

Correlate the SQL ID from V$SESSION with V$SQL to connect current session activity with the SQL statement represented in the shared SQL area.

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    s.event,
    s.wait_class,
    q.plan_hash_value,
    q.executions,
    ROUND(q.elapsed_time / 1000000, 2) AS elapsed_seconds,
    q.sql_text
FROM v$session s
LEFT JOIN v$sql q
    ON q.sql_id = s.sql_id
   AND q.child_number = s.sql_child_number
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.sid;