APEX and “ORA-01000 maximum open cursors exceeded”

Yes, APEX uses a lot of cursors and if you dive into APEX tuning docs, this is also explained in the Application Express (APEX) Performance Tuning and Scalability Factors (Doc ID 1418234.1):

“(…) APEX applications (and mod_plsql applications, for that matter) do not reuse session cursors. This is the nature of the architecture (…)”.

So the Oracle database will get a lot of open cursors to handle. Sometimes you will read it can be fixed by raising the APEX/ORDS listener “jdbc.MaxLimit”. It’s probably a bit low by default for production systems and it can be raised to 25 or 50 or something whatever your need is, but that will not fix the ORA-01000.

The Oracle database open cursor setting (300 by default) is per session. So raising the max number of connections does not solve it. Of course the setting can be raised in the database to 500 or higher, but there is another APEX setting called “jdbc.MaxConnectionReuseCount” which controls the number of connection reuses for sessions. By default it is set to 1000, so if each session on a connection reuse will open a cursor, you could get a 1000 open cursors, much higher than the open cursor setting in the database.

I have been lowering this default a couple of times to 100 and now a few more connections to the database are created, but it also makes the APEX/ORDS listener not creating so many sessions / open cursors on each connection any more, therefore also solving the “ORA-01000 maximum open cursors exceeded”.

Settings changed in ORDS defaults.xml are global. It’s better to set them individually in the conf/<db-name>.xml files (see: REST Data Services (3.0) Installation and Configuration Guide). My global default settings:

<entry key="jdbc.MaxLimit">100</entry>
<entry key="jdbc.MaxConnectionReuseCount">100</entry>

Why 100 and 100? Lowering the jdbc.MaxConnectionReuseCount by a factor of 10 will need about 10 times more connections, so raising jdbc.MaxLimit by a factor of 10 will result in the same number of ‘cursor limit’. It will cost a bit more resources at the database side because of extra connections.

Cheers!

Tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *