Hi again,
Below is the response from Oracle Meta-link regarding the issue with maximum
open cursors exceeded:
WORKAROUNDS FOR ORA-01000
Solution Description:
=====================
There are two ways to workaround this ORA-01000 error. You can tune cursor
usage at the database level and at the application level.
1. Tuning at the DATABASE LEVEL
There is a parameter you can set in the init.ora that determines the number
of
cursors a user can open in a session: OPEN_CURSORS.
OPEN_CURSORS by default is 50 and usually, this is not high enough. The
highest
value you can set this parameter to is operating system dependant. For more
information, please refer to Oracle7 Server Administrator's Guide, Appendix
A.
To solve the ORA-01000 error, set the OPEN_CURSORS to a higher number (such
as
255). You may need to set it to the maximum of the operating system limit.
Consequences to changing this parameter:
This parameter does not effect performance in any way but Oracle will now
need
a little more memory to store the cursors.
2. Tuning at the APPLICATION LEVEL
There are three parameters that affect handling cursors at the application
level: RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS. You should set these
parameters at the precompiler level.
HOLD_CURSOR by default is NO. This means that after Oracle executes a SQL
statement the links to the cursor cache, memory, and parse locks are
released
and marked for reuse. For more details refer to Programmer's Guide to
Precompilers Version 1.6 p.6-16.
RELEASE_CURSOR by default is NO. This means that after Oracle executes a SQL
statement, the links to the cursor cache is maintained and not released. For
more information, refer to Programmer's Guide to Precompilers Version 1.6
p.6-26.
These two parameters must be used in conjunction for them to be effective.
Here is a table that shows how settings of the two parameters interact.
----------------------------------------------------
|HOLD_CURSOR | RELEASE_CURSOR | LINKS ARE... |
----------------------------------------------------
| NO | not applicable | marked as reusable |
| YES | NO | maintained |
| NO | YES | removed immediately|
| n/a | YES | removed immediately|
----------------------------------------------------
To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and
RELEASE_CURSOR=YES. This way, after the cursors are used, Oracle will free
up
the memory for other cursors.
Consequences of setting these parameters HOLD_CURSOR=NO and
RELEASE_CURSOR=YES:
This will cause Oracle to release the links and locks for each cursor after
the
SQL statement is executed. This means that the next time Oracle needs to
issue
the same SQL statement, Oracle will have to reparse the statement, and
rebuild
the execution plan. This will cause some performance overhead.
MAXOPENCURSORS by default is 10. This number indicates the concurrent number
of open cursors that the precompiler tries to keep cached. It specifies the
initial size of the cursor cache. The limit of this parameter is determined
by
what you set OPEN_CURSORS to. Here is the formula:
MAXOPENCURSORS + 6 <= OPEN_CURSORS
6 is the overhead cursors Oracle needs.
Here is a calculation of the maximum number of cursors in use:
SQL statement cursors
PL/SQL parent cursors
PL/SQL child cursors
+6 cursors for overhead
-----------------------
sum of cursors in use.
Appreciate a response from the ActiveMQ Team.
Regards
Hatta
Hatta wrote:
>
> Hi again,
>
> There are a few items which I would like to add on this topic as well,
>
> 1. I have referred to the Oracle Forums and the general statement given
> was to ensure that the application client whom is accessing Oracle
> Database to check their open cursor statement.
>
> If there exist codes where the open cursor statements are not closed, then
> it should be considered to correct that code.
>
> From the Oracle point of view, is by increasing the open cursor parameter
> in the database to a certain amount. This would definitely hide the issue.
>
> But the flaw of this approach is that if the issue occured again, then
> what is the final solution?
>
> Appreciate the ActiveMQ technical team to respond to this matter.
>
> Thanks in advance
>
> Hatta
>
>
>
>
>
>
> Hatta wrote:
>>
>> Hi,
>>
>> I m using ActiveMQ 5.1 SNAPSHOT version. I have already experienced the
>> same problem in ActiveMQ 5.0.0 Production release.
>>
>> ActiveMQ 5.1 has been installed on a Linux OS : kernel version 2.6 and
>> its currently connecting to an Oracle 10 RAC (Real Application Cluster).
>>
>> After a few hours of observation and no activity between my application
>> and ActiveMQ 5.1, the following was noticed:
>>
>> ERROR DefaultDatabaseLocker - Failed to update database lock:
>> java.sql.SQLException: ORA-01000: maximum open cursors exceeded
>>
>>
>>
>> java.sql.SQLException: ORA-01000: maximum open cursors exceeded
>>
>>
>>
>> at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
>>
>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
>>
>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
>>
>> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
>>
>> at
>> oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
>>
>> at
>> oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
>>
>> at
>> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
>>
>> at
>> oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
>>
>> at
>> oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
>>
>> at
>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:94)
>>
>> at
>> org.apache.activemq.store.jdbc.DefaultDatabaseLocker.keepAlive(DefaultDatabaseLocker.java:103)
>>
>> at
>> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.databaseLockKeepAlive(JDBCPersistenceAdapter.java:458)
>>
>> at
>> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter$3.run(JDBCPersistenceAdapter.java:260)
>>
>> at
>> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:417)
>>
>> at
>> java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:280)
>>
>> at
>> java.util.concurrent.FutureTask.runAndReset(FutureTask.java:135)
>>
>> at
>> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:65)
>>
>> at
>> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(ScheduledThreadPoolExecutor.java:142)
>>
>> at
>> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:166)
>>
>> at
>> java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
>>
>> at
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
>>
>> at java.lang.Thread.run(Thread.java:595)
>>
>>
>> However, My application is still able to send and receive jms messages
>> from the broker. But this error message is disturbing
>> and may give an impression that there something wrong with the server
>> communication with the DB.
>>
>> Appreciate a response to this matter.
>>
>> Regards
>> Hatta
>>
>>
>>
>
>
--
View this message in context:
http://www.nabble.com/maximum-open-cursors-exceeded-tp16834950s2354p16884121.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.
