V$SESS_TIME_MODEL - Break Down DB Time by Session (CPU vs Waits)

V$SESS_TIME_MODEL

Overview

V$SESS_TIME_MODEL provides a per-session breakdown of how database time is spent, using the same time model hierarchy as the system-wide V$SYS_TIME_MODEL. Each row represents one time model statistic for one session, measured in microseconds from when the session connected (or the instance started for background processes). DBAs use this view to identify which sessions are driving CPU consumption, parse overhead, PL/SQL execution time, or Java call time — essential for both real-time triage and post-incident analysis.

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

Key Columns

Column	Datatype	Description
SID	NUMBER	Session identifier; joins to V$SESSION.SID
STAT_ID	NUMBER	Numeric identifier for the time model statistic
STAT_NAME	VARCHAR2(64)	Name of the time model statistic
VALUE	NUMBER	Cumulative microseconds spent in this statistic for the session since connect or instance start
CON_ID	NUMBER	Container ID (12c+); 0 = CDB-wide or non-CDB

Essential Queries

Basic Usage

Show all time model statistics for a specific session. Replace &sid with the target SID:

SELECT
    stat_name,
    ROUND(value / 1e6, 4)   AS seconds,
    value                   AS microseconds
FROM
    v$sess_time_model
WHERE
    sid = &sid
ORDER BY
    value DESC;

Monitoring Query — Top Sessions by DB Time

Identify the sessions consuming the most database time right now. This is the first query to run during a CPU or performance incident:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.program,
    s.module,
    s.action,
    s.machine,
    ROUND(tm.db_time       / 1e6, 2)   AS db_time_sec,
    ROUND(tm.db_cpu        / 1e6, 2)   AS db_cpu_sec,
    ROUND(tm.db_time - tm.db_cpu) / 1e6  AS wait_time_sec,
    CASE
        WHEN tm.db_time = 0 THEN NULL
        ELSE ROUND(tm.db_cpu / tm.db_time * 100, 1)
    END                                AS cpu_pct_of_dbtime
FROM
    v$session s
    JOIN (
        SELECT
            sid,
            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
        FROM
            v$sess_time_model
        GROUP BY
            sid
    ) tm ON tm.sid = s.sid
WHERE
    s.type       = 'USER'
    AND tm.db_time > 0
ORDER BY
    tm.db_time DESC
FETCH FIRST 20 ROWS ONLY;

Combined with Other Views — DB Time Breakdown with Wait Info

Join V$SESS_TIME_MODEL with V$SESSION_WAIT to see both where time was spent historically and what the session is waiting on right now:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    sw.event                            AS current_wait_event,
    sw.wait_class                       AS current_wait_class,
    sw.seconds_in_wait,
    ROUND(db.db_time    / 1e6, 2)       AS total_db_time_sec,
    ROUND(db.db_cpu     / 1e6, 2)       AS total_cpu_sec,
    ROUND(db.parse_time / 1e6, 2)       AS total_parse_sec,
    ROUND(db.plsql_time / 1e6, 2)       AS total_plsql_sec,
    ROUND(db.java_time  / 1e6, 2)       AS total_java_sec
FROM
    v$session s
    JOIN (
        SELECT
            sid,
            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 = 'parse time elapsed'
                THEN value ELSE 0 END)                          AS parse_time,
            SUM(CASE WHEN stat_name = 'PL/SQL execution elapsed time'
                THEN value ELSE 0 END)                          AS plsql_time,
            SUM(CASE WHEN stat_name = 'Java execution elapsed time'
                THEN value ELSE 0 END)                          AS java_time
        FROM
            v$sess_time_model
        GROUP BY
            sid
    ) db ON db.sid = s.sid
    JOIN v$session_wait sw ON sw.sid = s.sid
WHERE
    s.type    = 'USER'
    AND db.db_time > 0
ORDER BY
    db.db_time DESC
FETCH FIRST 25 ROWS ONLY;

Advanced Analysis — Hard Parse and Failed Parse Time Per Session

Excessive hard parse time per session indicates cursor sharing problems, missing bind variables, or frequent invalidations. Sessions with high hard parse elapsed time are prime candidates for SQL tuning:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    ROUND(total_parse.value     / 1e6, 4)   AS total_parse_sec,
    ROUND(hard_parse.value      / 1e6, 4)   AS hard_parse_sec,
    ROUND(failed_parse.value    / 1e6, 4)   AS failed_parse_sec,
    CASE
        WHEN total_parse.value = 0 THEN NULL
        ELSE ROUND(hard_parse.value / total_parse.value * 100, 1)
    END                                     AS hard_parse_pct,
    ROUND(sql_exec.value        / 1e6, 4)   AS sql_exec_sec,
    ROUND(db_time.value         / 1e6, 4)   AS db_time_sec
FROM
    v$session s
    JOIN v$sess_time_model total_parse
        ON  total_parse.sid       = s.sid
        AND total_parse.stat_name = 'parse time elapsed'
    JOIN v$sess_time_model hard_parse
        ON  hard_parse.sid        = s.sid
        AND hard_parse.stat_name  = 'hard parse elapsed time'
    JOIN v$sess_time_model failed_parse
        ON  failed_parse.sid      = s.sid
        AND failed_parse.stat_name = 'failed parse elapsed time'
    JOIN v$sess_time_model sql_exec
        ON  sql_exec.sid          = s.sid
        AND sql_exec.stat_name    = 'sql execute elapsed time'
    JOIN v$sess_time_model db_time
        ON  db_time.sid           = s.sid
        AND db_time.stat_name     = 'DB time'
WHERE
    s.type = 'USER'
    AND total_parse.value > 0
ORDER BY
    hard_parse.value DESC
FETCH FIRST 20 ROWS ONLY;

PL/SQL and Java Execution Time Analysis

Identify sessions spending significant time in PL/SQL or Java calls. High PL/SQL execution time relative to DB time can indicate inefficient stored procedures or excessive context switching:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.module,
    ROUND(db_time.value         / 1e6, 2)   AS db_time_sec,
    ROUND(plsql_exec.value      / 1e6, 2)   AS plsql_exec_sec,
    ROUND(plsql_comp.value      / 1e6, 2)   AS plsql_compile_sec,
    ROUND(java_exec.value       / 1e6, 2)   AS java_exec_sec,
    CASE
        WHEN db_time.value = 0 THEN NULL
        ELSE ROUND(plsql_exec.value / db_time.value * 100, 1)
    END                                     AS plsql_pct_of_dbtime,
    CASE
        WHEN db_time.value = 0 THEN NULL
        ELSE ROUND(java_exec.value / db_time.value * 100, 1)
    END                                     AS java_pct_of_dbtime
FROM
    v$session s
    JOIN v$sess_time_model db_time
        ON  db_time.sid       = s.sid
        AND db_time.stat_name = 'DB time'
    JOIN v$sess_time_model plsql_exec
        ON  plsql_exec.sid        = s.sid
        AND plsql_exec.stat_name  = 'PL/SQL execution elapsed time'
    JOIN v$sess_time_model plsql_comp
        ON  plsql_comp.sid        = s.sid
        AND plsql_comp.stat_name  = 'PL/SQL compilation elapsed time'
    JOIN v$sess_time_model java_exec
        ON  java_exec.sid         = s.sid
        AND java_exec.stat_name   = 'Java execution elapsed time'
WHERE
    s.type = 'USER'
    AND (plsql_exec.value > 0 OR java_exec.value > 0)
ORDER BY
    plsql_exec.value + java_exec.value DESC
FETCH FIRST 20 ROWS ONLY;

Common Use Cases

• CPU incident triage — Query top sessions by DB CPU during a CPU spike to identify the specific session(s) responsible before checking V$SQL for their current SQL.
• Parse overhead identification — High hard parse elapsed time per session directs investigation toward cursor sharing configuration (CURSOR_SHARING, bind variables, SESSION_CACHED_CURSORS).
• PL/SQL performance regression — Compare PL/SQL execution elapsed time before and after a code deployment to detect regressions in stored procedure performance.
• Connection management overhead — connection management call elapsed time reveals sessions spending excessive time on logon/logoff, suggesting connection pool misconfiguration.
• Background process accounting — Filter on v$session.type = 'BACKGROUND' to determine how much time LGWR, DBWR, or ARCH background processes spend in specific activities.
• Java application profiling — For applications using ojdbc with Java stored procedures, Java execution elapsed time quantifies the Java VM contribution to response time.

Time Model Statistic Hierarchy

The key statistics and their relationships (child time is a subset of parent):

DB time
├── DB CPU
├── sql execute elapsed time
│   ├── PL/SQL execution elapsed time
│   │   └── Java execution elapsed time
│   └── inbound PL/SQL rpc elapsed time
├── 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
└── connection management call elapsed time

Related Views

• V$SYS_TIME_MODEL — System-wide equivalent; the sum of all sessions’ values approximates (but does not exactly equal) the system totals due to background process accounting.
• V$SESSION — Session metadata including username, program, module, action; join on SID to add context to time model data.
• V$SESSION_WAIT — Current wait event per session; combine with V$SESS_TIME_MODEL to see both historical time consumption and present-moment wait state.
• V$SQL — SQL text and execution statistics; after identifying a high-CPU session, look up V$SESSION.SQL_ID in V$SQL for the specific statement.
• V$ACTIVE_SESSION_HISTORY — Sampled session activity history; provides the time dimension missing from the cumulative V$SESS_TIME_MODEL statistics.

Version Notes

• Oracle 10g R1: View introduced alongside V$SYS_TIME_MODEL as part of the Time Model framework; provides the session-level counterpart.
• Oracle 10g R2: failed parse (out of shared memory) elapsed time statistic added.
• Oracle 11g: PL/SQL compilation elapsed time and repeated bind elapsed time statistics added.
• Oracle 12c R1: CON_ID column added; in a CDB, values reflect per-session activity within the connected container. V$CON_SESS_TIME_MODEL provides an equivalent PDB-scoped view.
• Oracle 19c: No structural changes; view available in all configuration types.
• Oracle 23ai: Compatible; additional statistics may appear for new SQL execution engine features (e.g., SQL domains, new JSON processing paths), but the core hierarchy remains unchanged.