Summary

Confluence may fail in different situations when executing SQL statements in the Confluence repository (stored in Oracle database), which can lead to functional errors or a performance impact. 

Environment

Confluence 8.X

Diagnosis

In the atlassian-confluence.log  file you will notice multiple errors caused by a similar stack trace: 

Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
...
...

You can use the following SQL statement to identify the actual number of Open Cursors configured and the number of cursors taken by Confluence process: 

show parameter open_cursors

select a.value, s.username, s.sid, s.serial# 
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and USERNAME='<INSERT-DATABASE-USERNAME-HERE>

Cause

For every SQL statement execution, certain area in the memory is allocated. This private SQL memory are is called cursor. These cursors take up space in the shared pool, which is essential memory component of Oracle Database, specifically in the library cache.

To prevent a session from filling up the library cache, the OPEN_CURSORS  database parameter limits this. When reached the max allowed value set for OPEN_CURSORS, the ORA-01000: maximum open cursors exceeded is triggered in the Oracle side.

Solution

As part of our Database Setup for Oracle in Confluence, we do not specify any minimum value for the OPEN_CURSORS parameter, as Confluence does not work directly with them. Usually the management of cursors is delegated in the JDBC Driver.

Oracle's recommendation is that JDBC driver version should always be either same as or higher than the Oracle database version in order to leverage the latest capabilities of the driver. 

Hence, our recommendation is that you upgrade your Oracle JDBC Driver to the latest one available in the Oracle JDBC Downloads page. This might help to fix any already identified JDBC Driver bug that could cause a open cursors leak. 

Workaround

As an alternative, you may want to increase the maximum open cursor in the database by executing the following SQL command on the Oracle side:

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

This example sets the maximum open cursors to 1000. Change this value as required. Please, check with our Oracle DBA before implementing any change in this regard.