Saturday, March 7, 2009

ORA-01000: maximum open cursors exceeded - How To Avoid

When you will develop relatively complex form with a big set of View Objects and multiple Master-Detail relationships in the Model, there is a chance during testing you will get ORA-01000: maximum open cursors exceeded exception. It will happen, because there will be too many open row sets, especially in the case of complex Master-Detail relationships. However, its possible to avoid this problem.

Its described in Oracle Fusion Middleware Java API Reference for Oracle ADF Model 11g Release 1 (11.1.1.0.0) for ViewObjectImpl class, setFetchMode(byte) method. Documentation says that there are two modes to fetch data - FETCH_AS_NEEDED and FETCH_ALL. By default, FETCH_AS_NEEDED is set. However, with this setting, if user will scroll through Master, for each Detail group will be opened new row set and not closed (because most probably user will not navigate to the end in the list). This means, many ResultSet's will be created and there will be no available cursors. As documentation recommends, in this case FETCH_ALL should be used.

This setting can be configured declaratively, in View Object wizard. In Tuning section, Retrieve from the Database group, set All at Once instead of As Needed.


JDeveloper automatically will set FETCH_ALL setting in View Object XML:


In order to describe how it works on runtime, let's take an example of Master-Detail form:


In this case, with default FETCH_AS_NEEDED setting, only visible 25 rows will be fetched when Master record with DeartmentId=50 is selected:


If user will not scroll to last record, RecordSet will not be closed.

But, when FETCH_ALL is set, all Detail records for Master record with DepartmentId=50 will be fetched and ResultSet will be closed:


Of course, this can decrease application performance. So, you should use this setting smart.

Download sample application - OpenCursors.zip.

Spanish Summary:


Si se esta desarrollando páginas con acceso a múltiples View Objets, es posible que te topes con problemas asociados a la apertura de muchos cursores en la Base de Datos. Andrejus nos explica en este tópico la manera de evitar este problema, haciendo uso de las personalizaciones a nivel de View Object.

3 comments:

Surachart Opun said...

Good Stuff to help to avoid "ORA-01000: maximum open cursors exceeded.

By the way, If it make problem about performance... and don't need to do it.

told DBA:
Please help me to increase "open_cursors" initialized parameter.

Just my kidding... anyway your stuff great!

Andrej Baranovskij said...

Thanks ;-)

AjAiN said...

I am having issues database connections, as per ur article "Oracle ADF Application Pool", i modified following params:
max Inactive Age = 60000
monitor sleep interval = 30000
time to live = 120000

but this is also not giving desired result.

issues i am having is with particular datasource which is mostly usedin pages with table.

AS we have table with huge dataset we are using
AccessMode="RANGE_PAGING_INCR" and navigation buttons to go forward and backward.

can you help me with that, also one more question is if an AM has 10 view obejcts and page is using 1 view object, not 9 others, then how many connection wuold be created.