Problem

After going through standard troubleshooting based on this article (see: Confluence slows and times out during periods of high load due to database connection pool ), you arrived at the conclusion that you need to increase your database connection pool (so that it equals to 60-80% of your max http threads at peak). However, increasing the pool turns out to make the problem even worse, and it seems that the bottleneck occurs at the DB-side

Cause

The maximum number of sessions and processes set in your Oracle DB may not be enough to satisfy the increased load from the larger Confluence database connection pool. If Oracle hits the maximum number of processes, atlassian-confluence.log will throw an error as below:

Caused by: java.sql.SQLException: ORA-00018: maximum number of sessions exceeded

Resolution

1. To fix the ORA-00018 and the ORA-00020, we increase the processes parameter, usually doubling the processes value to allow for future database growth. Check your current processes and sessions utilization in Oracle (while Confluence is running at peak), by running the following query as sysdba in Oracle:

   select * from v$resource_limit where resource_name in ('processes','sessions');

2. Run these queries to set the new limit:

   alter system set processes=<num_processes> scope=spfile
   alter system set sessions=<num_sessions> scope=spfile;

   A basic formula for determining these parameter values is as follows:

   processes = x  
   sessions  = x*1.1+5

   Example:

   alter system set processes=500 scope=spfile;
   alter system set sessions=555 scope=spfile;

This requires an Oracle DB restart