V$SQL - Find Top SQL by CPU, Elapsed Time & Buffer Gets

V$SQL

Overview

V$SQL exposes one row per child cursor in the shared pool, representing every SQL statement Oracle has parsed and cached since the instance started (or since the cursor was last aged out). It is the primary view for SQL-level performance analysis — DBAs use it to find the top consumers of CPU, elapsed time, buffer gets, and disk reads, and to correlate execution plans with real workload statistics. Every SQL_ID visible in V$SESSION, AWR, and ASH traces back to a row in V$SQL while the cursor remains in the shared pool.

View Type: Dynamic Performance View Available Since: Oracle 9i (V$SQLAREA existed earlier; V$SQL introduced child-cursor granularity) Required Privileges: SELECT on V_$SQL or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
SQL_ID	VARCHAR2(13)	Unique identifier for the SQL statement — stable across instances and restarts for the same SQL text
CHILD_NUMBER	NUMBER	Child cursor number; multiple children share a SQL_ID when cursor sharing is prevented by bind peeking, partitioning, or environment differences
SQL_TEXT	VARCHAR2(1000)	First 1000 characters of the SQL statement
SQL_FULLTEXT	CLOB	Complete SQL text; use when the statement exceeds 1000 characters
PLAN_HASH_VALUE	NUMBER	Numeric hash of the execution plan; changes when the plan changes, useful for detecting plan regressions
EXECUTIONS	NUMBER	Total number of executions since the cursor was loaded
ELAPSED_TIME	NUMBER	Cumulative elapsed time in microseconds across all executions
CPU_TIME	NUMBER	Cumulative CPU time in microseconds across all executions
BUFFER_GETS	NUMBER	Cumulative logical reads (consistent gets + db block gets) across all executions
DISK_READS	NUMBER	Cumulative physical reads from disk across all executions
ROWS_PROCESSED	NUMBER	Cumulative rows returned or affected across all executions
FETCHES	NUMBER	Cumulative number of fetches (relevant for SELECT statements)
PARSE_CALLS	NUMBER	Number of parse calls; high parse-to-execute ratios indicate soft or hard parse problems
SORTS	NUMBER	Number of sorts performed by this cursor
DIRECT_WRITES	NUMBER	Cumulative direct path writes (PGA bypass of buffer cache)
SHARABLE_MEM	NUMBER	Bytes of shared pool memory consumed by this child cursor
PERSISTENT_MEM	NUMBER	Fixed memory required for the lifetime of the open cursor
PARSING_USER_ID	NUMBER	User ID of the schema that first parsed this cursor
PARSING_SCHEMA_NAME	VARCHAR2(128)	Schema name of the first parser
FIRST_LOAD_TIME	VARCHAR2(19)	Timestamp (YYYY-MM-DD/HH24:MI:SS) when this child cursor was first loaded
LAST_ACTIVE_TIME	DATE	Time this cursor was last executed
INVALIDATIONS	NUMBER	Number of times this cursor has been invalidated (plan changes, object DDL, statistics gathering)
LOADS	NUMBER	Number of times this cursor has been loaded or reloaded
OPTIMIZER_MODE	VARCHAR2(10)	Optimizer mode in effect when the cursor was parsed (ALL_ROWS, FIRST_ROWS_n)
MODULE	VARCHAR2(64)	Application module name captured at parse time
ACTION	VARCHAR2(64)	Application action name captured at parse time

Essential Queries

Basic Usage

Find a SQL statement’s full text, execution count, and key metrics by SQL_ID:

SELECT
    s.sql_id,
    s.child_number,
    s.plan_hash_value,
    s.executions,
    ROUND(s.elapsed_time / 1e6, 2)              AS elapsed_secs_total,
    ROUND(s.elapsed_time / NULLIF(s.executions, 0) / 1e6, 4)
                                                AS elapsed_secs_per_exec,
    ROUND(s.cpu_time / 1e6, 2)                  AS cpu_secs_total,
    s.buffer_gets,
    ROUND(s.buffer_gets / NULLIF(s.executions, 0), 0)
                                                AS buffer_gets_per_exec,
    s.disk_reads,
    s.rows_processed,
    s.parsing_schema_name,
    s.module,
    s.last_active_time,
    s.sql_fulltext
FROM
    v$sql s
WHERE
    s.sql_id = '&sql_id'
ORDER BY
    s.child_number;

Monitoring Query

Top 20 SQL statements by total elapsed time — the standard starting point for any performance review:

SELECT *
FROM (
    SELECT
        s.sql_id,
        s.child_number,
        s.plan_hash_value,
        ROUND(s.elapsed_time / 1e6, 2)          AS elapsed_secs,
        s.executions,
        ROUND(s.elapsed_time / NULLIF(s.executions, 0) / 1e6, 4)
                                                AS elapsed_per_exec_secs,
        ROUND(s.cpu_time / 1e6, 2)              AS cpu_secs,
        s.buffer_gets,
        ROUND(s.buffer_gets / NULLIF(s.executions, 0), 0)
                                                AS gets_per_exec,
        s.disk_reads,
        ROUND(s.rows_processed / NULLIF(s.executions, 0), 0)
                                                AS rows_per_exec,
        s.parsing_schema_name,
        s.module,
        SUBSTR(s.sql_text, 1, 100)              AS sql_text_snippet
    FROM
        v$sql s
    WHERE
        s.executions > 0
    ORDER BY
        s.elapsed_time DESC
)
WHERE ROWNUM <= 20;

Combined with Other Views

Join V$SQL with V$SESSION to see the full SQL text and per-session statistics for every currently active session:

SELECT
    sess.sid,
    sess.serial#,
    sess.username,
    sess.status,
    sess.event,
    sess.wait_class,
    sess.seconds_in_wait                        AS secs_waiting,
    sess.last_call_et                           AS secs_since_call,
    sql.sql_id,
    sql.plan_hash_value,
    sql.executions,
    ROUND(sql.elapsed_time / NULLIF(sql.executions, 0) / 1e6, 4)
                                                AS avg_elapsed_secs,
    ROUND(sql.buffer_gets / NULLIF(sql.executions, 0), 0)
                                                AS avg_buffer_gets,
    SUBSTR(sql.sql_fulltext, 1, 200)            AS sql_text
FROM
    v$session sess
    JOIN v$sql sql ON sql.sql_id      = sess.sql_id
                   AND sql.child_number = sess.sql_child_number
WHERE
    sess.username IS NOT NULL
    AND sess.status = 'ACTIVE'
ORDER BY
    sess.last_call_et DESC;

Advanced Analysis: Top SQL by Buffer Gets

High logical reads are the most common root cause of CPU pressure; these statements are the primary tuning targets:

SELECT *
FROM (
    SELECT
        s.sql_id,
        s.plan_hash_value,
        s.buffer_gets,
        s.executions,
        ROUND(s.buffer_gets / NULLIF(s.executions, 0), 0)
                                                AS gets_per_exec,
        s.disk_reads,
        ROUND(s.disk_reads / NULLIF(s.executions, 0), 0)
                                                AS disk_reads_per_exec,
        ROUND(s.elapsed_time / 1e6, 2)          AS elapsed_secs,
        ROUND(s.cpu_time / 1e6, 2)              AS cpu_secs,
        s.rows_processed,
        s.parse_calls,
        s.invalidations,
        s.parsing_schema_name,
        s.module,
        s.last_active_time,
        SUBSTR(s.sql_text, 1, 120)              AS sql_text
    FROM
        v$sql s
    WHERE
        s.executions > 0
    ORDER BY
        s.buffer_gets DESC
)
WHERE ROWNUM <= 20;

High Disk Read Statements

Identify SQL driving the most physical I/O — these candidates benefit most from index tuning, result caching, or In-Memory Column Store:

SELECT *
FROM (
    SELECT
        s.sql_id,
        s.plan_hash_value,
        s.disk_reads,
        s.executions,
        ROUND(s.disk_reads / NULLIF(s.executions, 0), 0)
                                                AS disk_reads_per_exec,
        s.buffer_gets,
        ROUND(s.buffer_gets / NULLIF(s.executions, 0), 0)
                                                AS gets_per_exec,
        ROUND(s.elapsed_time / 1e6, 2)          AS elapsed_secs,
        s.rows_processed,
        s.parsing_schema_name,
        s.module,
        s.last_active_time,
        SUBSTR(s.sql_text, 1, 120)              AS sql_text
    FROM
        v$sql s
    WHERE
        s.executions    > 0
        AND s.disk_reads > 0
    ORDER BY
        s.disk_reads DESC
)
WHERE ROWNUM <= 20;

Cursor Invalidation and Plan Regression Detection

Find statements that have been invalidated or reloaded frequently — a sign of frequent statistics gathering, DDL on referenced objects, or bind-peeking instability:

SELECT
    s.sql_id,
    s.child_number,
    s.plan_hash_value,
    s.invalidations,
    s.loads,
    s.executions,
    s.parse_calls,
    ROUND(s.parse_calls / NULLIF(s.executions, 0), 4)
                                                AS parse_to_exec_ratio,
    s.first_load_time,
    s.last_active_time,
    ROUND(s.elapsed_time / NULLIF(s.executions, 0) / 1e6, 4)
                                                AS avg_elapsed_secs,
    s.parsing_schema_name,
    SUBSTR(s.sql_text, 1, 120)                  AS sql_text
FROM
    v$sql s
WHERE
    s.invalidations > 5
    OR s.loads > 10
ORDER BY
    s.invalidations DESC,
    s.loads DESC;

Common Use Cases

• Top-SQL workload analysis — Sort by elapsed time, CPU time, or buffer gets to find the statements that consume the most database resources and prioritize tuning effort
• Plan regression detection — Compare PLAN_HASH_VALUE over time; a changed hash for the same SQL_ID is the fingerprint of a plan regression after a statistics gather or upgrade
• Parse overhead diagnosis — High PARSE_CALLS relative to EXECUTIONS indicates a missing cursor cache, literal SQL, or SESSION_CACHED_CURSORS too low
• Shared pool sizing — Sum SHARABLE_MEM across all cursors to estimate actual shared pool demand vs. the SHARED_POOL_SIZE parameter
• Application attribution — Filter by MODULE and ACTION (populated via DBMS_APPLICATION_INFO) to measure the resource footprint of specific application features
• SQL text search — Query SQL_FULLTEXT (CLOB) with LIKE or DBMS_LOB.INSTR to locate all cursors referencing a specific table or column before a schema change

Related Views

• V$SESSION — Joins on SQL_ID + CHILD_NUMBER to link live session activity to cursor statistics
• V$SQL_PLAN — Execution plan rows for each SQL_ID + PLAN_HASH_VALUE combination
• V$SYSSTAT — System-wide aggregates (buffer gets, disk reads) that put V$SQL per-statement figures in context
• V$SQLAREA — Aggregates all child cursors for a SQL_ID into one row; useful when child-level detail is not needed
• V$SQL_BIND_CAPTURE — Shows captured bind variable values for parameterised SQL, useful when a plan depends on bind peeking
• DBA_HIST_SQLSTAT — AWR equivalent of V$SQL; retains historical SQL statistics across snapshots for trend analysis

Version Notes

• Oracle 9i: V$SQL introduced to expose child-cursor-level statistics; V$SQLAREA was the predecessor with parent-level aggregation only
• Oracle 10g: SQL_ID column added (replaced ADDRESS+HASH_VALUE as the standard identifier); AWR began capturing snapshots of V$SQL into DBA_HIST_SQLSTAT
• Oracle 11g: Adaptive cursor sharing added; a single SQL_ID can now have multiple child cursors with different plans based on bind variable histograms; BIND_DATA column added
• Oracle 12c: Adaptive plans (adaptive joins, adaptive parallel distribution) can cause PLAN_HASH_VALUE to differ between executions of the same child cursor; SQL plan directives influence plan selection
• Oracle 19c: Automatic SQL Plan Management (SPM) baselines can pin a plan even when the optimizer would choose differently; check DBA_SQL_PLAN_BASELINES alongside V$SQL
• Oracle 21c / 23ai: SQL Analysis Report (DBMS_SQLDIAG improvements) and automatic index creation (23ai) can alter which cursors appear; Real-Time SQL Monitoring threshold lowered for some statement types