V$OSSTAT - Check CPU Utilization, Memory & OS Load from Oracle

V$OSSTAT

Overview

V$OSSTAT exposes operating system statistics collected by Oracle from the underlying host, covering CPU counts, CPU utilization, physical memory, and system load. DBAs use this view to correlate Oracle workload with host-level resource consumption, verify CPU counts for licensing compliance, and diagnose memory pressure without leaving the database session. The statistics are accumulated since instance startup and reset on restart.

View Type: Dynamic Performance View Available Since: Oracle 10g R1 Required Privileges: SELECT on V_$OSSTAT or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
OSSTAT_ID	NUMBER	Internal numeric identifier for the statistic
STAT_NAME	VARCHAR2(64)	Name of the OS statistic (e.g., NUM_CPUS, IDLE_TIME)
VALUE	NUMBER	Current cumulative value of the statistic
COMMENTS	VARCHAR2(256)	Human-readable description of what the statistic measures
CUMULATIVE	VARCHAR2(3)	YES if the value accumulates over time; NO for point-in-time snapshots
CON_ID	NUMBER	Container ID (12c+); 0 = applies to entire CDB or non-CDB

Essential Queries

Basic Usage

List all OS statistics with their current values:

SELECT
    osstat_id,
    stat_name,
    value,
    cumulative,
    comments
FROM
    v$osstat
ORDER BY
    stat_name;

CPU Utilization from Oracle’s Perspective

Calculate the percentage of time the OS CPUs spent in each state since instance startup. Useful for understanding overall host CPU demand relative to Oracle’s share:

SELECT
    stat_name,
    value                                                          AS centiseconds,
    ROUND(value / NULLIF(SUM(CASE
        WHEN stat_name IN ('IDLE_TIME', 'BUSY_TIME')
            THEN value
        ELSE 0
    END) OVER (), 0) * 100, 2)                                    AS pct_of_total
FROM
    v$osstat
WHERE
    stat_name IN (
        'IDLE_TIME',
        'BUSY_TIME',
        'USER_TIME',
        'SYS_TIME',
        'IOWAIT_TIME',
        'NICE_TIME'
    )
ORDER BY
    stat_name;

Note: TIME values are in centiseconds and are cumulative across all CPUs (i.e., a 4-CPU system with 100% busy will accumulate 400 centiseconds per second of wall clock time).

Memory Pressure Analysis

Check current physical memory availability. Values below 5–10% of total physical memory indicate memory pressure that can cause increased paging and Oracle SGA/PGA performance degradation:

SELECT
    stat_name,
    ROUND(value / 1024 / 1024 / 1024, 2)   AS value_gb,
    value                                   AS value_bytes,
    comments
FROM
    v$osstat
WHERE
    stat_name IN (
        'PHYSICAL_MEMORY_BYTES',
        'FREE_MEMORY_BYTES',
        'INACTIVE_MEMORY_BYTES',
        'SWAP_FREE_BYTES',
        'SWAP_SPACE_BYTES'
    )
ORDER BY
    stat_name;

Combined with Other Views — OS Load vs. Oracle Wait Time

Join V$OSSTAT with V$SYS_TIME_MODEL to compare OS-reported CPU load against Oracle’s own CPU consumption, helping identify whether CPU pressure is Oracle-driven or from competing processes:

SELECT
    os.stat_name,
    os.value                                            AS os_value,
    ROUND(stm.value / 1e6, 2)                           AS oracle_cpu_seconds,
    ROUND(os.value / 100, 2)                            AS os_cpu_seconds,
    ROUND(
        (stm.value / 1e6) /
        NULLIF(ROUND(os.value / 100, 2), 0) * 100, 2
    )                                                   AS oracle_pct_of_os_busy
FROM
    v$osstat          os
    JOIN v$sys_time_model stm
        ON stm.stat_name = 'DB CPU'
WHERE
    os.stat_name = 'BUSY_TIME';

Advanced Analysis — CPU Count Verification for Licensing

Oracle licensing is CPU-based. Verify the number of logical CPUs, cores, and sockets Oracle can see — critical for Standard Edition 2 (limited to 2 sockets) and Processor-based licensing:

SELECT
    stat_name,
    value,
    comments
FROM
    v$osstat
WHERE
    stat_name IN (
        'NUM_CPUS',
        'NUM_CPU_CORES',
        'NUM_CPU_SOCKETS',
        'NUM_LCPUS'
    )
ORDER BY
    stat_name;

Expected output interpretation:

STAT_NAME	Example Value	Meaning
NUM_CPUS	16	Logical CPUs (threads) visible to Oracle
NUM_CPU_CORES	8	Physical cores across all sockets
NUM_CPU_SOCKETS	2	Physical processor sockets
NUM_LCPUS	16	Logical CPUs (same as NUM_CPUS on most platforms)

OS Load Monitoring

Check the current system load average. A sustained LOAD value greater than NUM_CPUS indicates a run-queue backlog and potential CPU saturation:

SELECT
    o.stat_name,
    o.value,
    o.comments,
    cpu.value   AS num_cpus,
    ROUND(o.value / NULLIF(cpu.value, 0), 2) AS load_per_cpu
FROM
    v$osstat o
    JOIN v$osstat cpu
        ON cpu.stat_name = 'NUM_CPUS'
WHERE
    o.stat_name = 'LOAD';

Common Use Cases

• CPU licensing audit — Confirm NUM_CPU_SOCKETS and NUM_CPU_CORES match what was licensed; discrepancies cause compliance risk with Oracle LMS audits.
• Memory pressure diagnosis — Monitor FREE_MEMORY_BYTES trending toward zero before ORA-04031 (shared memory allocation failures) or excessive swapping occur.
• OS CPU saturation — Compare BUSY_TIME against IDLE_TIME to determine whether the host is CPU-bound independent of Oracle waits.
• I/O wait attribution — Use IOWAIT_TIME to determine whether CPU wait time is storage-bound, helping prioritize storage vs. SQL tuning work.
• Baseline and capacity planning — Snapshot V$OSSTAT at regular intervals using AWR or custom scripts to track CPU and memory utilization trends over weeks or months.
• RAC node comparison — Query V$OSSTAT on each RAC node to identify imbalanced workload distribution across cluster members.

Related Views

• V$SYS_TIME_MODEL — Oracle’s own CPU and elapsed time accounting; compare with V$OSSTAT BUSY_TIME to calculate Oracle’s share of host CPU.
• V$METRIC — Recent metric snapshots including CPU utilization rate per second, complementing the cumulative totals in V$OSSTAT.
• V$SYSSTAT — Oracle internal cumulative statistics (logical reads, physical reads, parses) that can be correlated with OS CPU and I/O stats.
• V$PGASTAT — PGA memory aggregate statistics; cross-reference with FREE_MEMORY_BYTES to assess memory contention between PGA growth and OS availability.
• V$PROCESS — Per-process PGA and CPU usage; correlate with OS CPU stats to identify specific Oracle processes driving host load.

Version Notes

• Oracle 10g R1: View introduced; basic CPU time and count statistics available.
• Oracle 10g R2: IOWAIT_TIME and NICE_TIME added on Linux platforms.
• Oracle 11g: PHYSICAL_MEMORY_BYTES, FREE_MEMORY_BYTES, INACTIVE_MEMORY_BYTES, SWAP_SPACE_BYTES, and SWAP_FREE_BYTES added, eliminating the need for OS-level memory queries from external scripts.
• Oracle 11g R2: LOAD statistic added (Linux/Unix); reflects 1-minute load average reported by the OS.
• Oracle 12c R1: CON_ID column added for multitenant support; V$OSSTAT at CDB level shows host-wide stats (CON_ID = 0).
• Oracle 19c: No structural changes; available in all container and non-CDB configurations.
• Oracle 23ai: Compatible; NUM_LCPUS behavior consistent with previous releases. On cloud/containerized deployments, values reflect the cgroup-visible CPU allocation rather than the full host.