V$SQLAREA - Aggregate SQL Statistics by SQL_ID

V$SQLAREA

Overview

V$SQLAREA presents one row per unique SQL statement in the shared pool, aggregating execution statistics across all child cursors for that statement’s SQL_ID. This is the parent-cursor level view: where V$SQL shows one row per child cursor (one per distinct execution environment, optimizer environment, or session context), V$SQLAREA collapses them into a single aggregate row per SQL_ID. This makes it the preferred starting point for workload analysis — identifying the top SQL consumers by CPU, elapsed time, logical reads, or execution count without the need to group by SQL_ID yourself.

V$SQLAREA is one of the most frequently consulted views during performance tuning engagements, AWR report analysis, and proactive workload monitoring. It is populated from the shared pool and cleared when statements age out or the pool is flushed.

View Type: Dynamic Performance View Available Since: Oracle 7 Required Privileges: SELECT on V_$SQLAREA or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
SQL_ID	VARCHAR2(13)	Unique identifier for the SQL statement; consistent across RAC instances and database restarts for the same SQL text
SQL_TEXT	VARCHAR2(1000)	First 1000 characters of the SQL statement text
SQL_FULLTEXT	CLOB	Complete SQL text; use for long statements that exceed SQL_TEXT’s 1000-character limit
PARSING_USER_ID	NUMBER	User ID of the schema that parsed the statement most recently; join to DBA_USERS on USER_ID
PARSING_SCHEMA_ID	NUMBER	Schema ID in which the statement was parsed
PARSING_SCHEMA_NAME	VARCHAR2(128)	Name of the parsing schema (available directly, no join needed)
EXECUTIONS	NUMBER	Total number of times this SQL has been executed across all child cursors
PARSE_CALLS	NUMBER	Total number of parse calls (hard and soft) for this statement
DISK_READS	NUMBER	Total physical reads (block reads from disk) across all executions
BUFFER_GETS	NUMBER	Total logical reads (buffer cache gets) across all executions
ROWS_PROCESSED	NUMBER	Total rows returned or processed by all executions
CPU_TIME	NUMBER	Total CPU time consumed in microseconds across all executions
ELAPSED_TIME	NUMBER	Total elapsed (wall clock) time in microseconds across all executions
USER_IO_WAIT_TIME	NUMBER	Total time in microseconds spent waiting on user I/O events
CLUSTER_WAIT_TIME	NUMBER	Total time in microseconds spent on RAC cluster wait events
SORTS	NUMBER	Total number of sorts performed by this statement
FETCHES	NUMBER	Total number of fetch calls
LOADS	NUMBER	Number of times the statement was hard-parsed (loaded into the shared pool)
INVALIDATIONS	NUMBER	Number of times the execution plan was invalidated (e.g., after statistics gather)
VERSION_COUNT	NUMBER	Number of child cursors currently in the shared pool for this SQL_ID
SHARABLE_MEM	NUMBER	Amount of shared memory (bytes) consumed by this cursor and all its children
LAST_ACTIVE_TIME	DATE	Timestamp of the most recent execution
MODULE	VARCHAR2(64)	Application module name from DBMS_APPLICATION_INFO at the time of the last parse
ACTION	VARCHAR2(64)	Application action name from DBMS_APPLICATION_INFO at the time of the last parse
PLAN_HASH_VALUE	NUMBER	Hash of the execution plan for the most recent child cursor
OPTIMIZER_COST	NUMBER	Optimizer-estimated cost for the current plan
CHILD_NUMBER	NUMBER	Not applicable at the parent level; see V$SQL for per-child detail

Essential Queries

Basic Usage

List the top 20 SQL statements by total elapsed time in the shared pool:

SELECT
    sql_id,
    ROUND(elapsed_time / 1e6, 2)                AS total_elapsed_secs,
    ROUND(cpu_time     / 1e6, 2)                AS total_cpu_secs,
    executions,
    ROUND(elapsed_time / NULLIF(executions, 0) / 1e6, 4)   AS avg_elapsed_secs,
    buffer_gets,
    disk_reads,
    rows_processed,
    parsing_schema_name,
    SUBSTR(sql_text, 1, 100)                    AS sql_text
FROM
    v$sqlarea
WHERE
    executions > 0
ORDER BY
    elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

Monitoring Query

Find SQL statements with the worst logical reads per execution — a key indicator of unoptimised queries that should be reviewed for missing indexes or rewritten:

SELECT
    sql_id,
    executions,
    ROUND(buffer_gets / NULLIF(executions, 0))  AS gets_per_exec,
    ROUND(disk_reads  / NULLIF(executions, 0))  AS reads_per_exec,
    ROUND(rows_processed / NULLIF(executions, 0)) AS rows_per_exec,
    ROUND(elapsed_time / NULLIF(executions, 0) / 1e6, 4) AS secs_per_exec,
    plan_hash_value,
    parsing_schema_name,
    SUBSTR(sql_text, 1, 120)                    AS sql_text
FROM
    v$sqlarea
WHERE
    executions > 10
ORDER BY
    gets_per_exec DESC NULLS LAST
FETCH FIRST 25 ROWS ONLY;

Combined with Other Views

Join V$SQLAREA with V$SQL to inspect individual child cursors for a high-VERSION_COUNT statement, which can indicate cursor sharing problems:

SELECT
    p.sql_id,
    p.version_count,
    p.executions                                AS total_executions,
    c.child_number,
    c.executions                                AS child_executions,
    c.plan_hash_value,
    c.optimizer_env_hash_value,
    c.reason                                    AS not_shared_reason
FROM
    v$sqlarea   p
    JOIN v$sql  c ON c.sql_id = p.sql_id
WHERE
    p.version_count > 20
    AND p.executions > 0
ORDER BY
    p.version_count DESC,
    p.sql_id,
    c.child_number;

High Parse Rate Detection

Find SQL statements generating excessive parse calls relative to executions, indicating hard parsing or parse-heavy application code that needs cursor caching:

SELECT
    sql_id,
    executions,
    parse_calls,
    loads                                           AS hard_parses,
    ROUND(parse_calls / NULLIF(executions, 0), 2)   AS parses_per_exec,
    ROUND(loads       / NULLIF(executions, 0), 4)   AS hard_parse_ratio,
    ROUND(elapsed_time / 1e6, 1)                    AS total_elapsed_secs,
    parsing_schema_name,
    SUBSTR(sql_text, 1, 100)                        AS sql_text
FROM
    v$sqlarea
WHERE
    executions     > 100
    AND parse_calls > executions * 0.5
ORDER BY
    parse_calls DESC
FETCH FIRST 20 ROWS ONLY;

Workload Profile by Schema

Summarise the SQL workload by parsing schema to understand which application schemas are consuming the most database resources:

SELECT
    parsing_schema_name,
    COUNT(*)                                    AS distinct_statements,
    SUM(executions)                             AS total_executions,
    ROUND(SUM(elapsed_time) / 1e6, 0)          AS total_elapsed_secs,
    ROUND(SUM(cpu_time)     / 1e6, 0)          AS total_cpu_secs,
    SUM(buffer_gets)                            AS total_buffer_gets,
    SUM(disk_reads)                             AS total_disk_reads,
    ROUND(SUM(sharable_mem) / 1024 / 1024, 1)  AS shared_pool_mb
FROM
    v$sqlarea
WHERE
    parsing_schema_name NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
GROUP BY
    parsing_schema_name
ORDER BY
    total_elapsed_secs DESC;

Common Use Cases

• Top SQL identification — Sort by ELAPSED_TIME, CPU_TIME, or BUFFER_GETS to find the most resource-intensive statements in the shared pool for targeted tuning
• High VERSION_COUNT investigation — Statements with VERSION_COUNT above 20–50 indicate cursor sharing problems; drill into V$SQL to find why child cursors are not being shared
• Parse overhead analysis — Compare PARSE_CALLS to EXECUTIONS; a ratio near 1.0 means the application is issuing a parse before every execute, which should be reduced via cursor caching
• Plan instability detection — Monitor INVALIDATIONS; a high count relative to EXECUTIONS suggests frequent statistics regathers or DDL changes are causing plan reoptimisation overhead
• Shared pool memory audit — Sum SHARABLE_MEM by schema or module to identify which workloads are consuming the most shared pool memory
• Module and action attribution — Filter by MODULE and ACTION when application teams instrument their code with DBMS_APPLICATION_INFO, enabling per-feature SQL accountability without schema-level granularity

Related Views

• V$SESSION — Join on SQL_ID to find the sessions currently executing a specific statement
• V$SQL — Child-cursor level detail for V$SQLAREA; one row per plan variant per SQL_ID
• V$SQL_PLAN — Execution plan for each child cursor; join on SQL_ID and CHILD_NUMBER from V$SQL
• V$ACTIVE_SESSION_HISTORY — Sampled session history; join on SQL_ID for time-series execution analysis
• DBA_HIST_ACTIVE_SESS_HISTORY — AWR-persisted session history; use for historical SQL workload analysis beyond the shared pool retention window

Version Notes

• Oracle 9i: SQL_FULLTEXT column added as CLOB to replace the truncated SQL_TEXT for long statements
• Oracle 10g: SQL_ID introduced as the canonical statement identifier, replacing ADDRESS + HASH_VALUE pairs; ELAPSED_TIME, CPU_TIME, and USER_IO_WAIT_TIME added
• Oracle 11g: LAST_ACTIVE_TIME added; Adaptive Cursor Sharing introduced, increasing VERSION_COUNT for bind-sensitive statements with multiple child cursors per plan
• Oracle 12c: Adaptive plans can cause PLAN_HASH_VALUE to differ between executions; IS_RESOLVED_ADAPTIVE_PLAN column in V$SQL tracks this; V$SQLAREA reflects the aggregated picture
• Oracle 19c: Real-Time SQL Monitoring enhancements; SQL Quarantine entries affect whether a statement is allowed to execute, which can cause sudden EXECUTIONS stagnation for quarantined SQL_IDs
• Oracle 23ai: SQL Plan Management enhancements; SQL_ID computation algorithm unchanged, maintaining backward compatibility for monitoring scripts