V$SYS_TIME_MODEL - System-Wide DB Time Breakdown & Parse Analysis

V$SYS_TIME_MODEL

Overview

V$SYS_TIME_MODEL provides cumulative, system-wide time model statistics measured in microseconds since instance startup. It is the authoritative source for understanding where the database collectively spends its time — how much is CPU-bound versus wait-bound, how much is consumed by parsing versus execution, and how much belongs to PL/SQL or Java. DBAs use this view as the starting point for workload characterization: if DB CPU is close to DB time, the workload is CPU-bound; if there is a large gap, wait events are the bottleneck. AWR reports, ADDM, and the OEM Performance Hub all derive their Time Model section from this view.

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

Key Columns

Column	Datatype	Description
STAT_ID	NUMBER	Numeric identifier for the time model statistic
STAT_NAME	VARCHAR2(64)	Name of the time model statistic
VALUE	NUMBER	Cumulative microseconds since instance startup
CON_ID	NUMBER	Container ID (12c+); 0 = CDB-wide or non-CDB

Essential Queries

Basic Usage

Display all time model statistics with values converted to seconds, sorted by most time-consuming:

SELECT
    stat_id,
    stat_name,
    ROUND(value / 1e6, 2)       AS seconds,
    value                       AS microseconds
FROM
    v$sys_time_model
ORDER BY
    value DESC;

Time Model Hierarchy — DB Time Breakdown

The canonical workload characterization query. Shows the complete time model with each component as a percentage of total DB time. The percentages for child statistics (e.g., DB CPU, parse time elapsed) may sum to more than 100% because they overlap — a parse operation is counted in both DB time and parse time elapsed:

SELECT
    stat_name,
    ROUND(value / 1e6, 2)                                           AS seconds,
    ROUND(
        value / NULLIF(
            (SELECT value FROM v$sys_time_model WHERE stat_name = 'DB time'),
            0
        ) * 100, 2
    )                                                               AS pct_of_db_time
FROM
    v$sys_time_model
ORDER BY
    value DESC;

Monitoring Query — DB CPU vs. Background CPU vs. Wait Time

Separate Oracle foreground (user session) CPU from background process CPU, and quantify total wait time. This is the single most useful query for quickly classifying whether a system is CPU-bound or I/O/wait-bound:

SELECT
    ROUND(db_time        / 1e6, 2)   AS db_time_sec,
    ROUND(db_cpu         / 1e6, 2)   AS db_cpu_sec,
    ROUND(bg_cpu         / 1e6, 2)   AS background_cpu_sec,
    ROUND(db_time - db_cpu) / 1e6    AS wait_time_sec,
    ROUND(db_cpu         / NULLIF(db_time, 0) * 100, 1)   AS cpu_pct,
    ROUND((db_time - db_cpu) / NULLIF(db_time, 0) * 100, 1) AS wait_pct
FROM (
    SELECT
        SUM(CASE WHEN stat_name = 'DB time'            THEN value ELSE 0 END) AS db_time,
        SUM(CASE WHEN stat_name = 'DB CPU'             THEN value ELSE 0 END) AS db_cpu,
        SUM(CASE WHEN stat_name = 'background cpu time' THEN value ELSE 0 END) AS bg_cpu
    FROM
        v$sys_time_model
);

Parse Time Analysis — Hard Parse, Failed Parse, and Bind Mismatch

Parse overhead is one of the top five performance problems in Oracle systems. This query breaks down all parse-related statistics to identify whether the problem is hard parsing (missing CURSOR_SHARING, no bind variables), failed parsing (syntax errors, privilege issues), or bind mismatch (same SQL text with different bind variable types):

SELECT
    stat_name,
    ROUND(value / 1e6, 4)                                               AS seconds,
    ROUND(
        value / NULLIF(
            (SELECT value FROM v$sys_time_model WHERE stat_name = 'parse time elapsed'),
            0
        ) * 100, 2
    )                                                                   AS pct_of_parse_time,
    ROUND(
        value / NULLIF(
            (SELECT value FROM v$sys_time_model WHERE stat_name = 'DB time'),
            0
        ) * 100, 4
    )                                                                   AS pct_of_db_time
FROM
    v$sys_time_model
WHERE
    stat_name IN (
        'parse time elapsed',
        'hard parse elapsed time',
        'hard parse (sharing criteria) elapsed time',
        'hard parse (bind mismatch) elapsed time',
        'failed parse elapsed time',
        'failed parse (out of shared memory) elapsed time'
    )
ORDER BY
    value DESC;

Combined with Other Views — AWR-Based Delta Calculation

V$SYS_TIME_MODEL is cumulative from instance startup. To get the rate of change over a specific interval, take two snapshots and subtract. This example uses AWR snapshot data to compute the time model breakdown for the last completed AWR interval:

SELECT
    e.stat_name,
    ROUND((e.value - b.value) / 1e6, 2)                             AS interval_seconds,
    ROUND(
        (e.value - b.value) /
        NULLIF(
            (SELECT e2.value - b2.value
             FROM   dba_hist_sys_time_model e2
                    JOIN dba_hist_sys_time_model b2
                        ON  b2.stat_name = e2.stat_name
                        AND b2.snap_id   = b2.snap_id
             WHERE  e2.stat_name = 'DB time'
               AND  e2.snap_id   = (SELECT MAX(snap_id) FROM dba_hist_snapshot)
               AND  b2.snap_id   = (SELECT MAX(snap_id) - 1 FROM dba_hist_snapshot)
               AND  e2.dbid      = (SELECT dbid FROM v$database)
               AND  b2.dbid      = (SELECT dbid FROM v$database)
            ), 0
        ) * 100, 2
    )                                                               AS pct_of_db_time
FROM
    dba_hist_sys_time_model e
    JOIN dba_hist_sys_time_model b
        ON  b.stat_name = e.stat_name
        AND b.snap_id   = (SELECT MAX(snap_id) - 1 FROM dba_hist_snapshot)
        AND b.dbid      = (SELECT dbid FROM v$database)
        AND b.instance_number = (SELECT instance_number FROM v$instance)
WHERE
    e.snap_id         = (SELECT MAX(snap_id) FROM dba_hist_snapshot)
    AND e.dbid        = (SELECT dbid FROM v$database)
    AND e.instance_number = (SELECT instance_number FROM v$instance)
ORDER BY
    (e.value - b.value) DESC;

Advanced Analysis — Connection Management and Sequence Load Time

Less commonly monitored but high-impact in OLTP systems: connection overhead and sequence cache misses. connection management call elapsed time is relevant for applications without proper connection pooling. sequence load elapsed time spikes when sequence cache sizes are too small relative to the INSERT rate:

SELECT
    stat_name,
    ROUND(value / 1e6, 4)       AS seconds,
    ROUND(
        value / NULLIF(
            (SELECT value FROM v$sys_time_model WHERE stat_name = 'DB time'), 0
        ) * 100, 4
    )                           AS pct_of_db_time
FROM
    v$sys_time_model
WHERE
    stat_name IN (
        'connection management call elapsed time',
        'sequence load elapsed time',
        'PL/SQL compilation elapsed time',
        'PL/SQL execution elapsed time',
        'Java execution elapsed time',
        'repeated bind elapsed time',
        'inbound PL/SQL rpc elapsed time'
    )
ORDER BY
    value DESC;

Common Use Cases

• Workload classification — Before any tuning engagement, run the DB CPU vs. wait time query. If DB CPU / DB time > 0.8, focus on SQL efficiency and indexes. If < 0.4, focus on wait event analysis.
• Parse overhead auditing — hard parse elapsed time exceeding 5% of DB time consistently indicates a systemic cursor sharing problem that CURSOR_SHARING=FORCE or application-level bind variables should address.
• PL/SQL tuning prioritization — When PL/SQL execution elapsed time is a dominant fraction of DB time, profiling individual packages with DBMS_PROFILER or DBMS_HPROF is warranted.
• AWR report validation — The Time Model section of an AWR report is a direct rendering of the delta between two DBA_HIST_SYS_TIME_MODEL snapshots; understanding V$SYS_TIME_MODEL makes AWR reports immediately readable.
• Connection pool sizing — connection management call elapsed time growing proportionally with session count confirms connection overhead; justify implementing a connection pool by quantifying the overhead.
• Sequence cache tuning — sequence load elapsed time increasing with INSERT throughput growth is resolved by increasing SEQUENCE.CACHE size (e.g., from 20 to 1000 for high-volume tables).

Time Model Hierarchy Reference

background elapsed time
└── background cpu time

DB time  (foreground session time only)
├── DB CPU
├── sql execute elapsed time
│   ├── PL/SQL execution elapsed time
│   │   ├── inbound PL/SQL rpc elapsed time
│   │   └── Java execution elapsed time
│   └── (wait time during SQL execution)
├── parse time elapsed
│   ├── hard parse elapsed time
│   │   ├── hard parse (sharing criteria) elapsed time
│   │   │   └── hard parse (bind mismatch) elapsed time
│   │   └── failed parse elapsed time
│   │       └── failed parse (out of shared memory) elapsed time
│   └── (soft parse time)
├── connection management call elapsed time
├── sequence load elapsed time
├── PL/SQL compilation elapsed time
└── repeated bind elapsed time

Important: DB time covers only foreground (user) sessions. Background process time is in background elapsed time and background cpu time. The two do NOT add up to total Oracle CPU consumption; add both for the full picture.

Related Views

• V$SESS_TIME_MODEL — Per-session equivalent; sum of all sessions does not exactly equal the system total because of how Oracle accounts for recursive SQL and background work.
• V$METRIC — Provides DB Time Per Sec as a rate metric derived from the delta of V$SYS_TIME_MODEL; use for real-time monitoring without manual delta calculation.
• V$OSSTAT — OS-level CPU statistics; compare DB CPU + background cpu time against OS BUSY_TIME to determine what fraction of host CPU is Oracle-driven.
• V$ACTIVE_SESSION_HISTORY — Sampled session activity; provides the time dimension and SQL-level breakdown that V$SYS_TIME_MODEL aggregates away.
• DBA_HIST_SYS_TIME_MODEL — AWR-persisted historical snapshots of V$SYS_TIME_MODEL; enables trending over days or weeks.

Version Notes

• Oracle 10g R1: View introduced; core statistics including DB time, DB CPU, parse time elapsed, and sql execute elapsed time available from initial release.
• Oracle 10g R2: background elapsed time and background cpu time added to separate foreground from background Oracle CPU.
• Oracle 11g: repeated bind elapsed time added; PL/SQL compilation elapsed time separated from PL/SQL execution elapsed time.
• Oracle 12c R1: CON_ID added; in a CDB, V$SYS_TIME_MODEL at the root reflects CDB-wide totals. V$CON_SYS_TIME_MODEL provides per-PDB equivalents for CON_ID > 0.
• Oracle 19c: No structural changes; DBA_HIST_SYS_TIME_MODEL retention governed by AWR retention settings (default 8 days).
• Oracle 23ai: Additional statistics for new execution engine features may appear but do not alter the established hierarchy. Compatible with all existing queries.