Connection storm (too many connections)

You got 'too many connections', 'remaining connection slots reserved' or ORA-00018; new connections are being rejected. Find the source, relieve the pressure now, fix it for good.

Measure the situation: how many connections, and how many are active?

First look at total connections, the limit and the active/idle split.

SELECT status, COUNT(*) FROM v$session WHERE type='USER' GROUP BY status;
SELECT name, value FROM v$parameter WHERE name IN ('processes','sessions');

• Most connections are idle / idle in transaction
• Most are active; I need to find the source

Who/from where? Group connections by source

Usually a single application server's pool has blown up or there is a connection leak. Look at the distribution by host/user/application.

SELECT client_addr, usename, application_name, count(*) FROM pg_stat_activity GROUP BY 1,2,3 ORDER BY 4 DESC;

• A single application/host has blown up the connections (pool)
• Load really increased; all are legitimate and active

Root cause: Pile-up of idle connections / leak

Connections are opened and never closed, or left 'idle in transaction'; they hold slots for nothing.

Root cause: Application pool has blown up

An application server's connection pool is unbounded/too large or leaking. It is the one producing the connections.

Root cause: Genuine load increase

The connections are legitimate and active; capacity really is insufficient.