You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by Hatta <rm...@yahoo.com> on 2008/05/05 04:58:35 UTC
Re: maximum open cursors exceeded
Hi again,
To the respected ActiveMQ Team, appreciate your kind response on this matter
as I have performed the action based on the following advise given by
Oracle.
The result was the lock did not occurred for some time. But after 5 to 6
hours, the message is getting displayed again.
The message is the same as the previous posting.
I sincerely appreciate that the ActiveMQ team would look into this matter.
Regards
Hatta
Hatta wrote:
>
> 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-tp16834950s2354p17053893.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.
Re: maximum open cursors exceeded
Posted by Hatta <rm...@yahoo.com>.
Hi Rob,
I m thankful for your reply and the effort put up by yourself to get this
bug fixed.
I have already downloaded and run the standalone version of 5.2 snapshot on
windows environment.
After leaving it open for one night since 6 May 2008, I m glad to see that
its not producing the same problem anymore.
For validity purpose I will leave it on again for today. (its been running
more than 18 hours now).
Again, I appreciate the effort given. Thanks.
Regards
Hatta
rajdavies wrote:
>
> Could you try the latest SNAPSHOT - I'm hoping this could be fixed by
> https://issues.apache.org/activemq/browse/AMQ-1702
>
> cheers,
>
> Rob
>
> http://open.iona.com/ -Enterprise Open Integration
> http://rajdavies.blogspot.com/
>
>
> On 5 May 2008, at 03:58, Hatta wrote:
>
>>
>> Hi again,
>>
>> To the respected ActiveMQ Team, appreciate your kind response on
>> this matter
>> as I have performed the action based on the following advise given by
>> Oracle.
>>
>> The result was the lock did not occurred for some time. But after 5
>> to 6
>> hours, the message is getting displayed again.
>>
>> The message is the same as the previous posting.
>>
>> I sincerely appreciate that the ActiveMQ team would look into this
>> matter.
>>
>> Regards
>> Hatta
>>
>>
>>
>>
>> Hatta wrote:
>>>
>>> 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-tp16834950s2354p17053893.html
>> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>>
>
>
>
--
View this message in context: http://www.nabble.com/maximum-open-cursors-exceeded-tp16834950s2354p17093701.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.
Re: maximum open cursors exceeded
Posted by Rob Davies <ra...@gmail.com>.
Could you try the latest SNAPSHOT - I'm hoping this could be fixed by https://issues.apache.org/activemq/browse/AMQ-1702
cheers,
Rob
http://open.iona.com/ -Enterprise Open Integration
http://rajdavies.blogspot.com/
On 5 May 2008, at 03:58, Hatta wrote:
>
> Hi again,
>
> To the respected ActiveMQ Team, appreciate your kind response on
> this matter
> as I have performed the action based on the following advise given by
> Oracle.
>
> The result was the lock did not occurred for some time. But after 5
> to 6
> hours, the message is getting displayed again.
>
> The message is the same as the previous posting.
>
> I sincerely appreciate that the ActiveMQ team would look into this
> matter.
>
> Regards
> Hatta
>
>
>
>
> Hatta wrote:
>>
>> 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-tp16834950s2354p17053893.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>