Querying Oracle V$SESSION for resource usage

This page shows the exact async Python needed to turn Oracle’s V$SESSION view — joined to V$PROCESS, V$SESS_IO, V$SESS_TIME_MODEL, and V$SESSTAT — into per-session CPU, memory, temp, and I/O deltas that map cleanly onto a tenant cost ledger.

Back to: System View Querying Patterns

Oracle exposes no cost anywhere in its dictionary; it exposes cumulative primitives — DB CPU microseconds, PGA bytes, logical and physical reads, allocated temp — that only ever climb within a session’s life. Reconstructing spend means sampling those counters, differencing consecutive samples per session, and pricing the delta. A single snapshot tells you nothing billable, and a naive SELECT * polled every second introduces latch contention on the very memory-mapped structures you are measuring. The pattern below reads only billable USER sessions, computes deltas with reset detection so a reconnect never subtracts real cost, prices them, and checks each session against its quota. It is the Oracle counterpart to extracting pg_stat_activity for cost tracking, and it feeds the same reconciliation and enforcement layer described in the parent Metric Extraction & Aggregation Pipelines architecture. Because these are cumulative counters rather than a live snapshot, the extraction shape differs from Postgres even though both land in one canonical usage record.

The diagram below traces the end-to-end flow from the joined session views through delta computation to per-session cost attribution.

Oracle V$SESSION extraction flow: join companion views, delta vs baseline, price, and attribute to tenant quotaV$SESSION is the identity anchor. It joins V$PROCESS on PADDR=ADDR for current PGA, and V$SESS_IO, V$SESS_TIME_MODEL and V$SESSTAT plus V$STATNAME on SID for logical and physical reads, DB CPU microseconds, and temp bytes. The joined per-session snapshot is differenced against the prior baseline with negative deltas clamped to zero, priced into cost units by per-primitive rates, and attributed to a tenant and its quota.PADDR = ADDRon SIDon SIDon SID− baseline× ratesJoined per-session snapshotV$SESSIONidentity +lifecycleSID · SERIAL#V$PROCESSPGA_USED_MEM (gauge)V$SESS_IOlogical + physical readsV$SESS_TIME_MODELDB CPU · microsecondsV$SESSTAT + V$STATNAMEtemp allocated (bytes)Delta vs baselineclamp resets → 0Price the delta→ cost unitsAttributetenant + quota

Prerequisites

Before running the extractor, confirm the following are in place.

  • Database grants: the identity that polls needs read-only access to the fixed views — nothing that can modify data or sessions. The public synonyms (V$SESSION) resolve to underlying V_$ objects, so grant on those. This least-privilege posture is part of broader access control for cost data.

    -- Run as SYS / a DBA. Grant only what the extractor reads.
CREATE USER finops_ro IDENTIFIED BY "<rotated-secret>";
GRANT CREATE SESSION TO finops_ro;
GRANT SELECT ON V_$SESSION         TO finops_ro;
GRANT SELECT ON V_$PROCESS         TO finops_ro;
GRANT SELECT ON V_$SESS_IO         TO finops_ro;
GRANT SELECT ON V_$SESS_TIME_MODEL TO finops_ro;
GRANT SELECT ON V_$SESSTAT         TO finops_ro;
GRANT SELECT ON V_$STATNAME        TO finops_ro;
-- Or, in one grant on modern Oracle: GRANT SELECT_CATALOG_ROLE TO finops_ro;

  • Python: 3.10 or newer (the code uses X | None unions and modern asyncio APIs).

  • Libraries: install the python-oracledb driver. Thin mode needs no Oracle client install, which keeps the poller a self-contained container image.

    pip install "oracledb>=2.0"

Step-by-Step Implementation

The extractor (1) reads the joined per-session counters, (2) differences them against a per-session baseline with reset detection, (3) prices the delta into cost units, and (4) checks each session against a quota — all behind an async pool with bounded retry. Each step is independently testable.

Step 1 — Read the joined session counters

V$SESSION carries identity and lifecycle (SID, SERIAL#, USERNAME, TYPE, STATUS); the consumption counters live in companion views and are joined in. Filter to billable client sessions server-side — TYPE='USER' with a non-null USERNAME — so background processes (SMON, PMON, DBWn) never reach the ledger, and so the payload crossing the wire is already scoped.

SESSION_QUERY = """
    SELECT
        s.SID || '-' || s.SERIAL# AS SESSION_KEY,
        s.USERNAME,
        NVL(tm.VALUE, 0)          AS CPU_TIME,        -- microseconds
        NVL(p.PGA_USED_MEM, 0)    AS PGA_USED_MEM,    -- bytes, current
        NVL(temp.VALUE, 0)        AS TEMP_ALLOCATED,  -- bytes
        NVL(io.CONSISTENT_GETS + io.DB_BLOCK_GETS, 0) AS LOGICAL_READS,
        NVL(io.PHYSICAL_READS, 0) AS PHYSICAL_READS
    FROM V$SESSION s
    JOIN V$PROCESS p
      ON s.PADDR = p.ADDR
    LEFT JOIN V$SESS_IO io
      ON s.SID = io.SID
    LEFT JOIN V$SESS_TIME_MODEL tm
      ON s.SID = tm.SID
     AND tm.STAT_NAME = 'DB CPU'
    LEFT JOIN (
        SELECT st.SID, st.VALUE
        FROM V$SESSTAT st
        JOIN V$STATNAME sn
          ON st.STATISTIC# = sn.STATISTIC#
        WHERE sn.NAME = 'temp space allocated (bytes)'
    ) temp
      ON s.SID = temp.SID
    WHERE s.TYPE = 'USER'
      AND s.USERNAME IS NOT NULL
      AND s.STATUS  = 'ACTIVE'
"""

# Expected row shape (SESSION_KEY, USERNAME, cpu_us, pga_bytes, temp_bytes, logical, physical):
#   ('142-38291', 'APP_TENANT_A', 4820000, 8912896, 0, 183422, 517)

PGA_USED_MEM is a current gauge, not a cumulative counter, so it is reported as-is rather than differenced; the CPU, read, and temp columns are cumulative and get differenced in Step 2.

Step 2 — Compute deltas with reset detection

Cumulative counters reset to zero when a session disconnects and its SID/SERIAL# is reused, when a session is killed (ORA-00028), or on instance restart. A delta computed across a reset is negative; propagating it would subtract real spend from the ledger. Clamp every delta at zero — a negative reading is the signal that the baseline is stale, so treat it as a fresh session and re-baseline.

def compute_deltas(current, baseline):
    """Difference cumulative counters against the last baseline, clamping resets to 0."""
    events, next_baseline = [], {}
    for key, username, cpu, pga, temp, logical, physical in current:
        prev = baseline.get(key, {})
        cpu_d      = max(0, cpu      - prev.get("cpu", 0))       # reset -> negative -> 0
        temp_d     = max(0, temp     - prev.get("temp", 0))
        logical_d  = max(0, logical  - prev.get("logical", 0))
        physical_d = max(0, physical - prev.get("physical", 0))
        events.append({
            "session_key": key, "username": username,
            "cpu_us": cpu_d, "pga_bytes": pga,        # PGA is a gauge: report as-is
            "temp_bytes": temp_d,
            "logical_reads": logical_d, "physical_reads": physical_d,
        })
        next_baseline[key] = {"cpu": cpu, "temp": temp,
                              "logical": logical, "physical": physical}
    return events, next_baseline   # sessions absent from `current` drop out -> baseline self-prunes

Step 3 — Price the delta into cost units

Each primitive gets a rate; the session’s cost for the interval is their weighted sum. With $\Delta_{\text{cpu}}$ in microseconds, $\Delta_{\text{lr}}$ logical reads, $\Delta_{\text{pr}}$ physical reads, and $\Delta_{\text{temp}}$ temp bytes, the per-session cost is:

$$\text{cost} = \frac{\Delta_{\text{cpu}}}{10^{6}},r_{\text{cpu}} + \frac{\Delta_{\text{lr}}}{10^{4}},r_{\text{lr}} + \frac{\Delta_{\text{pr}}}{10^{3}},r_{\text{pr}} + \frac{\Delta_{\text{temp}}}{2^{30}},r_{\text{temp}}$$

from datetime import datetime, timezone

# Rates are illustrative normalized compute units — calibrate to your invoice.
RATES = {"cpu": 0.002, "logical": 0.0005, "physical": 0.005, "temp": 0.01}

def price(event):
    """Attach a cost_units figure and an ISO timestamp to a delta event."""
    units = (
        event["cpu_us"]        / 1_000_000     * RATES["cpu"]      # per CPU-second
        + event["logical_reads"]  / 10_000     * RATES["logical"]  # per 10k logical reads
        + event["physical_reads"] / 1_000      * RATES["physical"] # per 1k physical reads
        + event["temp_bytes"]  / 1_073_741_824 * RATES["temp"]     # per GiB temp
    )
    event["cost_units"] = round(units, 6)
    event["timestamp"] = datetime.now(timezone.utc).isoformat()
    return event

Step 4 — Poll behind an async pool with bounded retry

Wrap the query in python-oracledb’s async pool so concurrent tenant evaluations share bounded connections, and retry transient database errors with exponential backoff. The same generic retry shape is covered in depth under implementing retry logic for failed metric pulls; here it guards a single fixed-view read.

import asyncio
import logging
import oracledb

logger = logging.getLogger("oracle_session_metrics")


class OracleSessionCostExtractor:
    def __init__(self, dsn, user, password, quota_units=1.0, max_retries=3):
        self.dsn, self.user, self.password = dsn, user, password
        self.quota_units, self.max_retries = quota_units, max_retries
        self._pool = None
        self._baseline: dict[str, dict] = {}

    async def _ensure_pool(self):
        if self._pool is None:
            self._pool = oracledb.create_pool_async(
                dsn=self.dsn, user=self.user, password=self.password,
                min=2, max=5, increment=1,
            )
            logger.info("Async connection pool initialized")

    async def _fetch(self):
        async with self._pool.acquire() as conn:
            with conn.cursor() as cur:
                await cur.execute(SESSION_QUERY)
                return await cur.fetchall()

    async def poll_once(self):
        """One sampling interval: fetch, delta, price, enforce quota."""
        await self._ensure_pool()
        for attempt in range(self.max_retries):
            try:
                rows = await self._fetch()
                break
            except oracledb.DatabaseError as exc:
                delay = min(2 ** attempt, 30)
                logger.warning("Fetch failed (%d/%d), retry in %ds: %s",
                               attempt + 1, self.max_retries, delay, exc)
                await asyncio.sleep(delay)
        else:
            logger.error("Max retries exceeded; skipping interval")
            return []

        events, self._baseline = compute_deltas(rows, self._baseline)
        priced = [price(e) for e in events]
        for e in priced:
            e["quota_exceeded"] = e["cost_units"] > self.quota_units
            if e["quota_exceeded"]:
                logger.warning("Quota breach: %s (%s) used %.4f units (limit %.2f)",
                               e["session_key"], e["username"],
                               e["cost_units"], self.quota_units)
        return priced

The first interval always returns zero-cost events because every session is being baselined for the first time; steady-state costs appear from the second poll onward.

The sequence below shows the async retry path a single poll_once call follows.

poll_once async retry sequence: acquire, execute, and either succeed or back off and retryThe Poller acquires a connection from the async pool, which executes the joined session query against the Oracle V$ views. On success the views return cumulative counter rows and the Poller computes deltas against the baseline (clamping resets) then prices and enforces the quota. On a DatabaseError the views return an ORA- error, the Poller applies exponential backoff and retries the acquire through the pool, up to max_retries.PollerAsync poolOracle V$ viewsacquire connectionexecute joined session queryalt[ query succeeds ]cumulative counter rowsdelta vs baseline · clamp resetsprice + enforce quota[ DatabaseError ]ORA- errorexponential backoffretry (≤ max_retries)

Verification

Confirm delta and pricing logic before the extractor guards real quotas. A two-sample fixture — with the second sample showing a counter drop — exercises both the normal delta and the reset-clamp path without touching Oracle.

def verify():
    # Sample 1: session baselined. Sample 2: CPU climbs, but SID was reused (counters dropped).
    first  = [("142-38291", "APP_TENANT_A", 4_820_000, 8_912_896, 0, 183_422, 517)]
    second = [("142-38291", "APP_TENANT_A",   500_000, 4_096_000, 0,  10_000,  12)]

    events, baseline = compute_deltas(first, {})
    assert events[0]["cpu_us"] == 4_820_000, "first sample baselines from zero"

    events, _ = compute_deltas(second, baseline)
    e = price(events[0])
    assert e["cpu_us"] == 0, "counter drop is a reset, must clamp to 0 not go negative"
    assert e["cost_units"] == 0.0, "a reset must never bill negative or phantom cost"
    print("reset handled cleanly:", e)

verify()
# -> reset handled cleanly: {'session_key': '142-38291', 'username': 'APP_TENANT_A',
#    'cpu_us': 0, 'pga_bytes': 4096000, 'temp_bytes': 0, 'logical_reads': 0,
#    'physical_reads': 0, 'cost_units': 0.0, 'timestamp': '2026-07-05T...'}

A steady-state event is shaped like {"session_key": "142-38291", "username": "APP_TENANT_A", "cpu_us": 320000, "pga_bytes": 8912896, "temp_bytes": 0, "logical_reads": 4210, "physical_reads": 63, "cost_units": 0.00097, "quota_exceeded": false, "timestamp": "..."} — feed that straight into strict schema validation for billing data before it reaches the ledger.

Gotchas & Edge Cases

  • V$ versus V_$ when granting. V$SESSION is a public synonym for the underlying V_$SESSION view. GRANT SELECT ON V$SESSION fails with ORA-02030; you must grant on the V_$ object (or use SELECT_CATALOG_ROLE). This trips nearly everyone the first time.
  • Multitenant containers scope your view. Connected to a PDB, V$SESSION shows only that PDB’s sessions; from CDB$ROOT you see all of them but must filter by CON_ID to attribute per pluggable database. Poll each PDB with its own service name, or read the CDB_/V$ container views and key attribution on CON_ID.
  • The PADDR = ADDR join is an inner join for a reason. A session momentarily without a server process (a dropped connection mid-poll) has no V$PROCESS row and correctly disappears from that interval rather than billing a null PGA. Do not “fix” this into a left join — you would resurrect phantom sessions.
  • DB CPU is microseconds, not centiseconds. V$SESS_TIME_MODEL reports in microseconds; older V$SESSTAT CPU stats report in centiseconds. Mixing the two silently inflates cost by 10,000x. Keep the time-model source and divide by $10^6$.
  • RAC needs GV$, not V$. On a Real Application Clusters database, V$SESSION shows only the local instance. Use the global GV$SESSION/GV$PROCESS views and include INST_ID in the session key, or a session that migrates between instances double-counts or vanishes.
  • Sampling cadence trades fidelity for observer effect. A 15–30 second interval satisfies most billing windows without adding latch pressure to the fixed views. For gaps left by transient drops, backfill from DBA_HIST_ACTIVE_SESS_HISTORY during off-peak windows rather than polling faster.
  • Blended instance cost still needs disaggregating. These per-session units are a share of a blended instance bill; reconcile them against the invoice using the compute-versus-storage cost breakdowns before the numbers become chargeback figures.

Frequently Asked Questions

Why join V$SESSION to V$SESS_TIME_MODEL instead of just reading V$SESSTAT for CPU?

V$SESS_TIME_MODEL reports DB CPU in microseconds and cleanly separates CPU time from the many other statistics in V$SESSTAT. It is the authoritative source for the “DB CPU” component of a session and avoids the centisecond-versus-microsecond unit trap that V$SESSTAT’s 'CPU used by this session' statistic introduces. Read time from the time model; read reads and temp from the I/O and stat views.

How do I stop the poller from attributing cost to Oracle’s own background processes?

Filter TYPE = 'USER' and USERNAME IS NOT NULL in the query. Background processes such as SMON, PMON, and the DBWn writers appear in V$SESSION with TYPE = 'BACKGROUND' and null usernames; excluding them at extraction time keeps maintenance work out of tenant ledgers, which is far cheaper than correcting it downstream.

What happens to my deltas when a session disconnects and its SID is reused?

The cumulative counters reset, so the raw delta goes negative. The max(0, current - baseline) clamp treats any negative reading as a session-lifecycle boundary: it bills zero for that interval and re-baselines from the new counters. Because sessions absent from a poll drop out of the rebuilt baseline, the baseline dictionary self-prunes and never leaks memory across reused SIDs.

How often should I poll V$SESSION for cost attribution?

A 15–30 second cadence is the usual sweet spot: fine enough to catch short-lived expensive sessions, coarse enough to avoid adding latch contention to memory-mapped fixed views under concurrency. If you need finer historical resolution, reconstruct it from Active Session History snapshots rather than raising the live poll rate.

Does this work on Amazon RDS for Oracle or Autonomous Database?

Yes, with grant caveats. On RDS use the rdsadmin grant procedures to expose the V_$ views to your read-only user; on Autonomous Database many fixed views are already readable by ADMIN and can be granted onward. The query and delta logic are unchanged — only how you obtain SELECT on the underlying objects differs by managed platform.

Back to: System View Querying Patterns

Oracle’s reference for the session time model and I/O views is the Database Reference on dynamic performance views, and the driver used here is documented in the python-oracledb user guide.