V$SYSSTAT - Calculate Buffer Cache Hit Ratio & Key Performance Metrics

V$SYSSTAT

Overview

V$SYSSTAT contains one row per system-wide statistic, accumulating counts and totals from instance startup. It is the foundation for the classic Oracle performance ratios — buffer cache hit ratio, library cache hit ratio, soft parse ratio, and redo generation rate — that appear in Statspack, AWR reports, and every Oracle DBA textbook. Unlike session-level views, V$SYSSTAT gives the aggregate picture across all sessions simultaneously active on the instance. DBAs query it directly for quick health checks, use differences between two snapshots to calculate rates, and rely on it to identify whether a performance problem is systemic (affecting the whole instance) rather than isolated to a single session.

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

Key Columns

Column	Datatype	Description
STATISTIC#	NUMBER	Internal statistic number; stable within a release but can change across major versions
NAME	VARCHAR2(64)	Descriptive name of the statistic (e.g., “session logical reads”, “physical reads”)
CLASS	NUMBER	Bitmask classification: 1=User, 2=Redo, 4=Enqueue, 8=Cache, 16=OS, 32=RAC, 64=SQL, 128=Debug
VALUE	NUMBER	Cumulative count or total since instance startup; never decreases (wraps at very large values on long-running instances)
STAT_ID	NUMBER	Unique ID for the statistic; more stable than STATISTIC# across patchsets

Essential Queries

Basic Usage

Retrieve the current value of any named statistic — the simplest form of the query:

SELECT
    statistic#,
    name,
    value,
    CASE class
        WHEN 1   THEN 'User'
        WHEN 2   THEN 'Redo'
        WHEN 4   THEN 'Enqueue'
        WHEN 8   THEN 'Cache'
        WHEN 16  THEN 'OS'
        WHEN 32  THEN 'RAC'
        WHEN 64  THEN 'SQL'
        WHEN 128 THEN 'Debug'
        ELSE          TO_CHAR(class)
    END                                          AS class_name
FROM
    v$sysstat
WHERE
    name IN (
        'session logical reads',
        'physical reads',
        'physical reads direct',
        'db block gets',
        'consistent gets',
        'redo size',
        'parse count (total)',
        'parse count (hard)',
        'parse count (failures)',
        'execute count',
        'user commits',
        'user rollbacks',
        'sorts (memory)',
        'sorts (disk)',
        'table scans (long tables)',
        'table scans (short tables)'
    )
ORDER BY
    name;

Monitoring Query: Key Performance Ratios

Calculate the five most important Oracle performance ratios in a single query — suitable for inclusion in a monitoring script or health check report:

SELECT
    -- Buffer Cache Hit Ratio: should be > 95% for OLTP; lower is acceptable for DSS/batch
    ROUND(
        100 * (1 - (
            (SELECT value FROM v$sysstat WHERE name = 'physical reads') -
            (SELECT value FROM v$sysstat WHERE name = 'physical reads direct') -
            (SELECT value FROM v$sysstat WHERE name = 'physical reads direct (lob)')
        ) / NULLIF(
            (SELECT value FROM v$sysstat WHERE name = 'session logical reads'), 0
        )
    , 2)                                         AS buffer_cache_hit_ratio_pct,

    -- Library Cache Hit Ratio: should be > 99%; low values indicate hard parse storm
    ROUND(
        100 * (1 - (
            (SELECT SUM(reloads) FROM v$librarycache)
            / NULLIF(
                (SELECT SUM(pins) FROM v$librarycache), 0
            )
        )
    , 2)                                         AS library_cache_hit_ratio_pct,

    -- Soft Parse Ratio: high % of parses being soft; < 80% indicates missing cursor caching
    ROUND(
        100 * (1 - (
            (SELECT value FROM v$sysstat WHERE name = 'parse count (hard)')
            / NULLIF(
                (SELECT value FROM v$sysstat WHERE name = 'parse count (total)'), 0
            )
        )
    , 2)                                         AS soft_parse_ratio_pct,

    -- In-Memory Sort Ratio: sorts done in PGA vs spilling to temp; should be > 95%
    ROUND(
        100 * (
            (SELECT value FROM v$sysstat WHERE name = 'sorts (memory)')
            / NULLIF(
                (SELECT value FROM v$sysstat WHERE name = 'sorts (memory)')
                + (SELECT value FROM v$sysstat WHERE name = 'sorts (disk)'), 0
            )
        )
    , 2)                                         AS inmemory_sort_ratio_pct,

    -- Redo Log Space Wait Ratio: waits for redo buffer space; should be < 1%
    ROUND(
        100 * (
            (SELECT value FROM v$sysstat WHERE name = 'redo log space requests')
            / NULLIF(
                (SELECT value FROM v$sysstat WHERE name = 'redo entries'), 0
            )
        )
    , 4)                                         AS redo_space_wait_ratio_pct
FROM
    dual;

Combined with Other Views

Calculate per-second rates by taking two snapshots 60 seconds apart — simulating what AWR does over longer intervals:

-- Step 1: Capture baseline snapshot
CREATE GLOBAL TEMPORARY TABLE sysstat_snap1 ON COMMIT PRESERVE ROWS AS
SELECT name, value, SYSDATE AS snap_time FROM v$sysstat;

-- Step 2: Wait 60 seconds in your session, then run the delta report
SELECT
    s2.name,
    s2.value - s1.value                          AS delta_total,
    ROUND((s2.value - s1.value)
          / (86400 * (SYSDATE - s1.snap_time)), 2) AS per_second_rate
FROM
    v$sysstat s2
    JOIN sysstat_snap1 s1 ON s1.name = s2.name
WHERE
    s2.value - s1.value > 0
    AND s2.name IN (
        'session logical reads',
        'physical reads',
        'redo size',
        'user commits',
        'user rollbacks',
        'parse count (hard)',
        'parse count (total)',
        'execute count',
        'table scans (long tables)',
        'sorts (disk)'
    )
ORDER BY
    per_second_rate DESC;

Advanced Analysis: I/O Profile

Compare logical vs. physical reads and classify workload type (OLTP = high cache hit, DSS/batch = high physical reads):

SELECT
    SUM(CASE WHEN name = 'session logical reads'      THEN value END)   AS logical_reads,
    SUM(CASE WHEN name = 'physical reads'             THEN value END)   AS physical_reads,
    SUM(CASE WHEN name = 'physical reads direct'      THEN value END)   AS direct_reads,
    SUM(CASE WHEN name = 'db block gets'              THEN value END)   AS db_block_gets,
    SUM(CASE WHEN name = 'consistent gets'            THEN value END)   AS consistent_gets,
    SUM(CASE WHEN name = 'physical writes'            THEN value END)   AS physical_writes,
    SUM(CASE WHEN name = 'physical writes direct'     THEN value END)   AS direct_writes,
    ROUND(
        100 * (1 -
            SUM(CASE WHEN name = 'physical reads' THEN value END) /
            NULLIF(SUM(CASE WHEN name = 'session logical reads' THEN value END), 0)
        ), 2
    )                                                                    AS cache_hit_pct,
    ROUND(
        SUM(CASE WHEN name = 'redo size' THEN value END) / 1073741824, 3
    )                                                                    AS redo_generated_gb
FROM
    v$sysstat
WHERE
    name IN (
        'session logical reads',
        'physical reads',
        'physical reads direct',
        'db block gets',
        'consistent gets',
        'physical writes',
        'physical writes direct',
        'redo size'
    );

Parse Statistics Breakdown

Analyse parse overhead — a leading indicator of shared pool pressure and cursor sharing problems:

SELECT
    SUM(CASE WHEN name = 'parse count (total)'    THEN value END)   AS total_parses,
    SUM(CASE WHEN name = 'parse count (hard)'     THEN value END)   AS hard_parses,
    SUM(CASE WHEN name = 'parse count (failures)' THEN value END)   AS failed_parses,
    SUM(CASE WHEN name = 'execute count'          THEN value END)   AS total_executions,
    ROUND(
        100 * SUM(CASE WHEN name = 'parse count (hard)' THEN value END) /
        NULLIF(SUM(CASE WHEN name = 'parse count (total)' THEN value END), 0)
    , 2)                                                             AS hard_parse_pct,
    ROUND(
        SUM(CASE WHEN name = 'parse count (total)' THEN value END) /
        NULLIF(SUM(CASE WHEN name = 'execute count' THEN value END), 0)
    , 4)                                                             AS parse_to_execute_ratio,
    SUM(CASE WHEN name = 'cursor cache hits'      THEN value END)   AS cursor_cache_hits
FROM
    v$sysstat
WHERE
    name IN (
        'parse count (total)',
        'parse count (hard)',
        'parse count (failures)',
        'execute count',
        'cursor cache hits'
    );

Redo Generation and Commit Rate

Monitor transaction throughput and redo generation rate — essential for redo log sizing and Data Guard lag estimation:

SELECT
    SUM(CASE WHEN name = 'redo size'              THEN value END)   AS redo_bytes_total,
    ROUND(
        SUM(CASE WHEN name = 'redo size' THEN value END) / 1073741824, 3
    )                                                                AS redo_gb_total,
    SUM(CASE WHEN name = 'redo entries'           THEN value END)   AS redo_entries,
    SUM(CASE WHEN name = 'redo log space requests'THEN value END)   AS redo_space_waits,
    SUM(CASE WHEN name = 'redo log space wait time' THEN value END) AS redo_space_wait_cs,
    SUM(CASE WHEN name = 'user commits'           THEN value END)   AS commits,
    SUM(CASE WHEN name = 'user rollbacks'         THEN value END)   AS rollbacks,
    ROUND(
        100 * SUM(CASE WHEN name = 'user rollbacks' THEN value END) /
        NULLIF(
            SUM(CASE WHEN name = 'user commits' THEN value END) +
            SUM(CASE WHEN name = 'user rollbacks' THEN value END), 0
        )
    , 2)                                                             AS rollback_pct
FROM
    v$sysstat
WHERE
    name IN (
        'redo size',
        'redo entries',
        'redo log space requests',
        'redo log space wait time',
        'user commits',
        'user rollbacks'
    );

Common Use Cases

• Buffer cache sizing — A persistently low buffer cache hit ratio (below 95% for OLTP) is a primary justification for increasing DB_CACHE_SIZE; conversely, consistently high ratios with low memory pressure may indicate over-allocation
• Hard parse storm diagnosis — A high hard-parse-to-total-parse ratio combined with elevated “library cache: mutex X” waits in V$SYSTEM_EVENT points to literal SQL or SESSION_CACHED_CURSORS issues
• Redo log sizing — Divide total redo size by uptime to get average redo generation per second, then multiply by the desired log switch interval to size online redo log groups
• Sort spill identification — A non-zero and growing “sorts (disk)” value means PGA work area sizes are insufficient for the current sort workload; increase PGA_AGGREGATE_TARGET
• Workload baselining — Capture V$SYSSTAT at the start and end of a load test, business day, or batch window to produce a rate-based workload profile
• Data Guard throughput estimation — Redo generation rate from V$SYSSTAT directly determines the minimum network bandwidth required to sustain a synchronous standby without log transport lag

Related Views

• V$SESSION — Per-session statistics; V$SESSTAT is the session-level equivalent of V$SYSSTAT
• V$SYSTEM_EVENT — Wait event totals; the wait-side complement to the statistic-side picture in V$SYSSTAT
• V$SQL — Per-statement statistics; aggregate of all V$SQL rows approximates many V$SYSSTAT counters
• V$SESSTAT — Same structure as V$SYSSTAT but scoped to a single session; join with V$STATNAME on STATISTIC# for readable names
• V$STATNAME — Master lookup table mapping STATISTIC# to NAME and CLASS for V$SYSSTAT and V$SESSTAT
• DBA_HIST_SYSSTAT — AWR snapshot of V$SYSSTAT; used to compute rates over historical time periods in AWR reports

Version Notes

• Oracle 8i / 9i: Core statistics established; buffer cache hit ratio and library cache hit ratio calculations standardised in this era
• Oracle 10g: “session logical reads” replaced “db block gets + consistent gets” as the preferred denominator for the buffer cache hit ratio; AWR began persisting V$SYSSTAT deltas into DBA_HIST_SYSSTAT
• Oracle 11g: Automatic Memory Management (AMM) and Automatic PGA Management make some ratio-based tuning thresholds less actionable — Oracle self-adjusts; statistics still valid for diagnosis
• Oracle 12c (Multitenant): CON_ID column added; queries from the CDB root aggregate across all PDBs; queries from within a PDB show only that PDB’s statistics
• Oracle 19c: “In-Memory” statistics added (e.g., “IM scan rows”) when the In-Memory Column Store is enabled; “Exadata cell” statistics appear on Exadata platforms
• Oracle 21c / 23ai: Additional statistics for AutoML, JSON Relational Duality, and native JSON binary (OSON) format; STAT_ID column provides a more stable identifier than STATISTIC# across upgrades