ORA-01000 - Too many open cursors

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

ORA-01000 - Too many open cursors

neulens
hi, A particular Sonar build ends with an error from Oracle DB - Too many open cursors. Other jobs that use the same Sonar instance run successfully. Any ideas where too start looking?
Reply | Threaded
Open this post in threaded view
|

Re: ORA-01000 - Too many open cursors

David Racodon-2
Hi,

Are you getting the following error: ORA-01000: maximum open cursors exceeded ?
If so, see  http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams138.htm.

Regards,


David RACODON | SonarSource
Senior Consultant

---------------------------------------------
  Sign up to our newsletter here!
---------------------------------------------


On 18 July 2013 15:16, neulens <[hidden email]> wrote:
hi,A particular Sonar build ends with an error from Oracle DB - Too many open
cursors.Other jobs that use the same Sonar instance run successfully.  Any
ideas where too start looking?



--
View this message in context: http://sonar.15.x6.nabble.com/ORA-01000-Too-many-open-cursors-tp5014801.html
Sent from the Sonar user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: ORA-01000 - Too many open cursors

neulens
In reply to this post by neulens
DBA has doubled the number of open cursors (from 200 to 400) and currently the build runs successfully.
Reply | Threaded
Open this post in threaded view
|

Re: ORA-01000 - Too many open cursors

David Racodon-2
Good news! Thanks for the notification.


David RACODON | SonarSource
Senior Consultant

---------------------------------------------
  Sign up to our newsletter here!
---------------------------------------------


On 29 August 2013 01:02, neulens <[hidden email]> wrote:
DBA has doubled the number of open cursors (from 200 to 400) and currently
the build runs successfully.



--
View this message in context: http://sonar.15.x6.nabble.com/ORA-01000-Too-many-open-cursors-tp5014801p5016420.html
Sent from the Sonar user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: ORA-01000 - Too many open cursors

sberthiaume
This post was updated on .
Not sure if reviving this old thread is the proper way to go or if I should have opened a new one; sorry if I broke protocol.

I'm having the same issue and and the 400 cursor "fix" worked for us too; however, since max cursors are a system-wide setting in Oracle and not a per-user/schema setting, this can have a significant impact on the DB server. Would it be possible to control the "batch-size" in the sonar config for people that need to do so?

Hopefully, it might be as simple as giving access to override the batchSize in BatchSession.java.

Thank you for helping me make our DBAs happy  

Here is the stack trace of the error since it seems like useful information; also, I'm using SonarQube 5.1; we didn't seem to have the problem with Sonar 5.0 or 5.0.1, might be a coincidence.
2015.04.08 21:38:22 INFO  web[o.s.s.c.ComputationService] Analysis of project my::project (report 10302) (done) | time=5342ms
2015.04.08 21:38:22 ERROR web[o.s.s.c.ComputationThread] Failed to process analysis report 10302 of project my::project
org.apache.ibatis.exceptions.PersistenceException:
### Error committing transaction.  Cause: org.apache.ibatis.executor.BatchExecutorException: org.sonar.core.issue.db.IssueChangeMapper.insert (batch index #200) failed. 199 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: ORA-01000: maximum open cursors exceeded
ORA-06512: at "DEVSONAR.ISSUE_CHANGES_IDT", line 3
ORA-04088: error during execution of trigger 'DEVSONAR.ISSUE_CHANGES_IDT'

### Cause: org.apache.ibatis.executor.BatchExecutorException: org.sonar.core.issue.db.IssueChangeMapper.insert (batch index #200) failed. 199 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: ORA-01000: maximum open cursors exceeded
ORA-06512: at "DEVSONAR.ISSUE_CHANGES_IDT", line 3
ORA-04088: error during execution of trigger 'DEVSONAR.ISSUE_CHANGES_IDT'

        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:177) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:169) ~[mybatis-3.2.7.jar:3.2.7]
        at org.sonar.core.persistence.DbSession.commit(DbSession.java:61) ~[sonar-core-5.1.jar:na]
        at org.sonar.core.persistence.BatchSession.commit(BatchSession.java:177) ~[sonar-core-5.1.jar:na]
        at org.sonar.core.persistence.BatchSession.increment(BatchSession.java:214) ~[sonar-core-5.1.jar:na]
        at org.sonar.core.persistence.BatchSession.insert(BatchSession.java:134) ~[sonar-core-5.1.jar:na]
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52) ~[mybatis-3.2.7.jar:3.2.7]
        at com.sun.proxy.$Proxy104.insert(Unknown Source) ~[na:na]
        at org.sonar.server.computation.step.PersistIssuesStep.insertChanges(PersistIssuesStep.java:129) ~[sonar-server-5.1.jar:na]
        at org.sonar.server.computation.step.PersistIssuesStep.execute(PersistIssuesStep.java:100) ~[sonar-server-5.1.jar:na]
        at org.sonar.server.computation.ComputationService.process(ComputationService.java:89) ~[sonar-server-5.1.jar:na]
        at org.sonar.server.computation.ComputationContainer.execute(ComputationContainer.java:47) ~[sonar-server-5.1.jar:na]
        at org.sonar.server.computation.ComputationThread.run(ComputationThread.java:58) ~[sonar-server-5.1.jar:na]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [na:1.7.0_75]
        at java.util.concurrent.FutureTask.run(FutureTask.java:262) [na:1.7.0_75]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:178) [na:1.7.0_75]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:292) [na:1.7.0_75]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_75]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_75]
        at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75]
Caused by: org.apache.ibatis.executor.BatchExecutorException: org.sonar.core.issue.db.IssueChangeMapper.insert (batch index #200) failed. 199 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: ORA-01000: maximum open cursors exceeded
ORA-06512: at "DEVSONAR.ISSUE_CHANGES_IDT", line 3
ORA-04088: error during execution of trigger 'DEVSONAR.ISSUE_CHANGES_IDT'

        at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:127) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:114) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:109) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.BaseExecutor.commit(BaseExecutor.java:201) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.CachingExecutor.commit(CachingExecutor.java:104) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:174) ~[mybatis-3.2.7.jar:3.2.7]
        ... 20 common frames omitted
Caused by: java.sql.BatchUpdateException: ORA-01000: maximum open cursors exceeded
ORA-06512: at "DEVSONAR.ISSUE_CHANGES_IDT", line 3
ORA-04088: error during execution of trigger 'DEVSONAR.ISSUE_CHANGES_IDT'

        at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10500) ~[ojdbc6.jar:11.2.0.4.0]
        at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230) ~[ojdbc6.jar:11.2.0.4.0]
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) ~[commons-dbcp-1.4.jar:1.4]
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) ~[commons-dbcp-1.4.jar:1.4]
        at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:103) ~[mybatis-3.2.7.jar:3.2.7]
        ... 25 common frames omitted
Reply | Threaded
Open this post in threaded view
|

Re: ORA-01000 - Too many open cursors

Julien Lancelot
Hi,

Thanks for this feedback, unfortunately, I'm not sure that being able to configure the bath size is a good idea as setting a too low value will result in poor performance...

FYI, I think that you can set the number of cursor to 250, as this is the max batch size number.

Regards,

Julien LANCELOT | SonarSource

On 9 April 2015 at 16:23, sberthiaume <[hidden email]> wrote:
Not sure if reviving this old thread is the proper way to go or if I should
have opened a new one; sorry if I broke protocol.

I'm having the same issue and and the 400 cursor "fix" worked for us too;
however, since max cursors are a system-wide setting in Oracle and not a
per-user/schema setting, this can have a significant impact on the DB
server. Would it be possible to control the "batch-size" in the sonar config
for people that need to do so?

Hopefully, it might be as simple as giving access to override the  batchSize
in BatchSession.java
<https://github.com/morristech/sonar/blob/master/sonar-core/src/main/java/org/sonar/core/persistence/BatchSession.java>
.

Thank you for helping me make our DBAs happy

Here is the stack trace of the error since it seems like useful information;
also, I'm using SonarQube 5.1; we didn't seem to have the problem with Sonar
5.0 or 5.0.1, might be a coincidence.




--
View this message in context: http://sonarqube.15.x6.nabble.com/ORA-01000-Too-many-open-cursors-tp5014801p5034213.html
Sent from the SonarQube Users mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: ORA-01000 - Too many open cursors

sberthiaume
I will have to discuss this with DBAs as I'm not the one in control of the DB; the test using 400 cursor was done with their help, but after that they reverted back to the original 200 (so Sonar is not working again).

I understand this giving control over this element can impact performance, but in environments with tight control it can be the only option. If they limit us to 200, we will either have to move to another DB (probably MySQL/Postgres) or give-up using Sonar (I don't think we will reach that point).

Thanks for you prompt answer nonetheless.
Reply | Threaded
Open this post in threaded view
|

Re: ORA-01000 - Too many open cursors

sberthiaume
Actually, I might have found some kind of workaround with the following sonar.properties setting:
# Simon B.
# Ref: https://mybatis.github.io/guice/datasources/dbcp.html
# Ref: https://commons.apache.org/proper/commons-dbcp/configuration.html
sonar.jdbc.maxOpenPreparedStatements=200
Seems to have worked (perhaps by accident, but it did).
Reply | Threaded
Open this post in threaded view
|

Re: ORA-01000 - Too many open cursors

sberthiaume
Nevermind, it was a fluke of some sort, it doesn't seem to work