Scops
Docs Download

Resources

Average Active Sessions in Oracle: What It Means, How to Read It

4 November 2026

Average active sessions is the number of sessions working in an Oracle database at the same moment, averaged over a period. Oracle defines it exactly: "The average active sessions for a time period equals the total database time of all user sessions during the period divided by the elapsed time (wall-clock time) for the period." An average of 6 active sessions means that, at an average moment, six sessions were on CPU or in a wait that is not idle. Read it against the CPU count: below it the database has CPU to spare, above it sessions queue. It is the one number that measures the whole load of a database, and Scops draws it for every database from its own samples every 5 seconds, with no agent and no Diagnostics Pack.

The definition, in Oracle's words

Three sentences from Oracle's performance guides hold the whole idea:

Average active sessions is DB time per second of wall clock. An hour with 21,600 seconds of DB time averaged 6 active sessions, whether that was six sessions busy all hour or sixty sessions busy for six minutes each. Because it adds up everything the users waited for, it moves when anything hurts: a slow disk, a lock, a plan that changed, a commit storm.

How to read the chart

The active sessions chart stacks the active sessions by what they were doing, usually by wait class, and draws the CPU count as a line. Three questions read it:

  1. How tall is it against the CPU line? Oracle's guide puts the line in one sentence: "When the CPU value reaches the CPU Cores line, the database instance is consuming 100 percent of CPU time on the system." A total below the line with most of it in CPU is a busy, healthy database. CPU alone at or above the line is a database short of CPU, and the extra sessions queue in the run queue, or in resmgr:cpu quantum when the Resource Manager holds them back. See Oracle CPU saturated.
  2. Which color grew? The wait class tells you where the time went. User I/O is reads, usually db file sequential read or scattered reads. Application is row locks, enq: TX - row lock contention. Commit is log file sync. Concurrency is latches, mutexes and buffer busy waits. Cluster is RAC traffic, such as gc buffer busy. Configuration is a log switch or a full archiver. The wait events guide covers each one.
  3. What shape is it? A step up at one moment is a change: a plan, a release, a new job, a lock. A slow climb over days is growth or a leak. A spike at the same hour every night is the batch, and it is only a problem if it is larger than on other nights.

A gap in the chart is time when nobody measured, never zero, and the most common mistake in reading active sessions is to compare the number with a fixed line instead of with the same hour on other days.

What is a good average active sessions value?

There is no good number in general, only a good number for this database at this hour. Two rules hold everywhere:

Beyond that, the usual level is what matters: 20 active sessions is normal at 10:00 on a Monday for one database and an incident at 03:00 on a Sunday for the same database. That is why Radar Engine in Scops learns the usual level of every hour, weekdays and weekends apart, and judges the active sessions against it. See Oracle anomaly detection.

Get average active sessions from Oracle yourself

The current minute, from the system metrics, which Oracle fills every 60 seconds and every 15 seconds:

SELECT inst_id, begin_time, end_time,
       ROUND(value, 1) AS avg_active
FROM   gv$sysmetric
WHERE  metric_name = 'Average Active Sessions'
AND    group_id = 2
ORDER  BY inst_id;

Any interval, from the time model: read DB time twice and divide the difference by the seconds between the readings. The value is in microseconds.

SELECT value / 1e6 AS db_time_s, SYSDATE AS taken
FROM   v$sys_time_model
WHERE  stat_name = 'DB time';
-- read again later:
-- avg active = (db_time_s2 - db_time_s1)
--       / ((taken2 - taken1) * 86400)

Right now, by counting, with the same rule Oracle uses for an active session, and split the way the chart splits it:

SELECT CASE WHEN state <> 'WAITING' THEN 'CPU'
            ELSE wait_class END AS activity,
       COUNT(*) AS sessions
FROM   v$session
WHERE  type = 'USER'
AND    status = 'ACTIVE'
AND    (state <> 'WAITING' OR wait_class <> 'Idle')
GROUP  BY CASE WHEN state <> 'WAITING' THEN 'CPU'
               ELSE wait_class END
ORDER  BY sessions DESC;

Use STATE, not EVENT, to tell CPU from waits: a session that is not waiting still shows the last event it waited for. The first two queries say how much load there is. Only the third says what it is made of, and it says it only for this second. The history of that split is Active Session History, which is the Diagnostics Pack on Enterprise Edition and does not exist on Standard Edition 2. Sampling V$SESSION into a table of your own is the way around it, shown in session history without the Diagnostics Pack.

Average active sessions in Scops

Scops samples every session of every database every 5 seconds itself, with one read-only account and nothing installed on the database server, and keeps the samples in its own repository. The active sessions chart is the first thing on every database's Dashboard:

See it coming

Radar Engine checks every database every 5 minutes against the usual level of that hour, so a rise in active sessions becomes a finding with its cause before it becomes a phone call:

DetectorClockWhen it fires
Database load surgeLive, against the usual of this hour or the hour before.Warning. Far above the usual and above the CPU count: Critical.
SQL S taking most of the loadLive. Half or more of the active sessions.Above the usual of this hour: Warning. More sessions than CPUs: Critical. Always so at this hour: Watch.
Concurrency waits surging (also Application, Cluster, Commit, Configuration)Live. Twice the hour before, well above its usual spread.Watch. With a second sign, such as 5 blocked sessions or commits three times slower: Warning. Above max(4, 4 × usual) active sessions, with an effect: Critical.
Unusual User I/O waits (one for each wait class) · Unusual CPU loadHourly, against the same hour on other days.By size, duration and cause. Critical only with a named cause, high for 3 hours, while blocking, stalled executions or slow reads are open. Else Warning at most.

Ask Scops AI "Why was SALESPRD slow in the last hour?" and the first line of the answer is the active sessions and their window, such as 29.6 active sessions against 14.0 just before, followed by the statements and the change behind it.

Questions

What does average active sessions mean in Oracle?
The number of sessions on CPU or in a non-idle wait at an average moment of a period: total DB time divided by elapsed time. An average of 4 active sessions over an hour means four sessions worked at once on average.

What is the difference between average active sessions and DB time?
DB time is a total, in seconds, for a period. Average active sessions is the same total divided by the length of the period, so it can be compared across windows of any length and with the CPU count.

What is a good average active sessions value?
CPU below the CPU count, and the total close to the usual level of that hour for that database. A fixed number means nothing across databases or hours.

Why are the active sessions higher than the number of CPUs?
Either the sessions wait for something other than CPU, such as I/O, locks or commits, or there are more sessions on CPU than cores and they queue. The split by wait class tells which.

Does average active sessions need the Diagnostics Pack?
The number itself does not: V$SYSMETRIC and V$SYS_TIME_MODEL give it. Its history by wait class, statement and session comes from ASH, which is the Diagnostics Pack. Scops collects that history itself on every edition, with no pack.

Related

Sources