activemq-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hatta <rmi_j...@yahoo.com>
Subject Re: maximum open cursors exceeded
Date Mon, 05 May 2008 02:58:35 GMT

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.


Mime
View raw message