Oracle problems

Logon Storm: Which Pool Reconnected at Once, What It Cost, and the Fix

3 November 2026

A logon storm is a few hundred connections arriving in the same second, usually from one application: a pool that was restarted, a pool that dropped every idle connection and now opens them all back at once, a failover that sent every client to the surviving node, or a reconnect loop after a password change. Each logon is a new server process, a share of PGA, a row in V$SESSION and a set of dictionary reads, and a thousand of them in a second put the host on its knees, fill PROCESSES and lock out the users who were already there. The queries below name the pool, and the listener can be told to take them at a pace the database survives.

How fast logons are arriving

SELECT name, value
FROM   v$sysstat
WHERE  name IN ('logons cumulative', 'logons current');

logons cumulative is the total since the instance started; two readings a minute apart give the rate. The last minute is also in the metrics:

SELECT metric_name, ROUND(value, 1) AS value, end_time
FROM   v$sysmetric
WHERE  metric_name = 'Logons Per Sec'
AND    group_id = 2;

A database with pools in front of it logs on a few times a minute. Five a second is a storm, and the hour before tells you whether it is the usual one at this time of day.

Which pool it is

SELECT username, program, machine, COUNT(*) AS sessions,
       TO_CHAR(MIN(logon_time), 'HH24:MI:SS') AS first_logon,
       TO_CHAR(MAX(logon_time), 'HH24:MI:SS') AS last_logon
FROM   v$session
WHERE  type = 'USER'
AND    logon_time > SYSDATE - 5/1440
GROUP  BY username, program, machine
ORDER  BY sessions DESC;

One program on one or two machines, with the first and last logon seconds apart, is the pool. The shape of the storm, minute by minute:

SELECT TO_CHAR(logon_time, 'HH24:MI') AS minute, COUNT(*) AS logons
FROM   v$session
WHERE  type = 'USER'
AND    logon_time > SYSDATE - 1/24
GROUP  BY TO_CHAR(logon_time, 'HH24:MI')
ORDER  BY 1;

V$SESSION only has the sessions that are still there. The connections that logged on, failed and left, which is the whole of a storm that could not get in, are in the listener log, one line per connection with the program, the host and the service, and a count of its lines per minute shows the storm that the database never saw.

What it cost

SELECT resource_name, current_utilization, max_utilization,
       limit_value
FROM   v$resource_limit
WHERE  resource_name IN ('processes', 'sessions');

MAX_UTILIZATION at the limit is the storm that reached PROCESSES: the sessions after that got ORA-00020, including the DBA. Oracle's reference derives the default of PROCESSES from the cores and sets SESSIONS at 1.5 times PROCESSES plus 22, and every login requires a session, so the two run out together. The cost on the host is in the CPU page: the dedicated server process is the most expensive thing a logon creates, and a storm creates a thousand of them on the same second.

The fix, in order

Find out why the pool reconnected. A restart of the application server is the honest case. The others are a pool with a minimum size of zero that drops idle connections and reopens them all on the first busy minute, a validation query that started failing so every borrowed connection is thrown away and replaced, a password that changed on the database and not in the pool, and a failover: when a RAC node goes, every pool on the other nodes reconnects to the survivor at the same second.
Give the pool a shape that cannot storm. Minimum equal to maximum, so the pool never shrinks and never refills; an idle timeout longer than the quiet hours; one pool per application server, not one per thread; a cheap validation, or none. A pool that holds its connections logs on once a day.
Rate-limit the listener. Oracle Net Listener has a connection rate limiter for exactly this: CONNECTION_RATE_listener_name = 10 in listener.ora sets a global rate of new connections a second, enforced on each endpoint that carries (RATE_LIMIT=yes) in its address, and an endpoint can carry its own number instead. The storm then reaches the database at ten a second, which it handles without noticing, and the pool is full a minute later instead of never.
Leave room in PROCESSES for a failover. On RAC each node needs room for the sessions of the node that fails, or the failover is a storm that ends in ORA-00020. A bigger limit is headroom, not a fix: a pool that reconnects a thousand times will do it again.
Pool in the database when the client cannot. Database Resident Connection Pooling keeps server processes pooled inside the database, so a client that must connect per request, which some web stacks do, gets a pooled server instead of a new process each time. It works on Standard Edition 2.
Mind the account. A pool that retries with an old password is a storm of failed logons, and after FAILED_LOGIN_ATTEMPTS the account is locked for every application that uses it. Rotate with a gradual rollover, and change the pool before the old password stops working.

See it coming

Radar Engine, the detection layer of Scops, watches the logon rate and the session count of every database live, against the hour before, so the storm is a finding in the first minute, with the program and the machine it came from, rather than an ORA-00020 ticket an hour later:

Detector	Clock	When it fires
Logon storm	Live. 5 logons a second or more, against the hour before.	Warning. Five times the usual and 15 a second: Critical.
Session surge	Live, against the hour before.	75% of the limit: Critical. 50%, or three times the usual and rising: Warning. Else Watch.

Scops samples every session every 5 seconds, so the storm is also visible in the Activity chart as a wall of new sessions with their program and machine, on any edition, with nothing installed on the database server.

Questions

What is a logon storm?
A burst of new database connections in a few seconds, usually from one application pool that restarted, refilled or failed over. Each logon creates a server process, so a burst of hundreds saturates the CPU and can fill PROCESSES, locking everyone else out with ORA-00020.

How do I find which application caused it?
Group V$SESSION by program and machine for sessions whose LOGON_TIME is in the last few minutes; the pool is the row with hundreds of sessions logged on within seconds. Connections that failed are not in V$SESSION; the listener log has them.

Can the listener limit the connection rate?
Yes. CONNECTION_RATE_listener_name in listener.ora sets a number of new connections a second, enforced on each endpoint with RATE_LIMIT=yes in its address, and an endpoint can carry its own rate. A storm then arrives as a queue the database can handle.

Why does a logon storm hurt more than the same number of sessions?
Because the cost is in the logon, not the session: a new server process, memory, authentication and dictionary reads, all on the same second. A pool that holds its connections pays that cost once a day.

Sources

Oracle: V$SESSION, V$RESOURCE_LIMIT, PROCESSES, SESSIONS
Oracle: Listener parameters, connection rate limiter, Managing processes, Database Resident Connection Pooling
Scops docs: Detector reference