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
