This issue might blur the line between Java and database problem, so I apologize if this is the wrong place for it. Not sure which side is to blame yet.
I'm a Java developer temporarily playing DBA on my project, so I'm pretty new at this. We're running Oracle 11.2.0.1 on a Red Hat Linux 64-bit server that our Java application uses. We've recently decided to switch our Oracle 11g instance from dedicated servers to shared servers, and now our application throws these errors often:
09:58:00,077 ERROR [main] SparqlHelper:198 - Error performing query:
com.hp.hpl.jena.shared.JenaException: java.sql.SQLRecoverableException: Closed Connection
at oracle.spatial.rdf.client.jena.OracleSemQueryPlan.executeBindings(OracleSemQueryPlan.java:494)
at oracle.spatial.rdf.client.jena.OracleStagePattern.<init>(OracleStagePattern.java:216)
at oracle.spatial.rdf.client.jena.OracleQueryIterBlockTriplesQH.nextStage(OracleQueryIterBlockTriplesQH.java:98)
at com.hp.hpl.jena.sparql.engine.iterator.QueryIterRepeatApply.makeNextStage(QueryIterRepeatApply.java:94)
at com.hp.hpl.jena.sparql.engine.iterator.QueryIterRepeatApply.hasNextBinding(QueryIterRepeatApply.java:55)
at com.hp.hpl.jena.sparql.engine.iterator.QueryIteratorBase.hasNext(QueryIteratorBase.java:69)
... (lots more)
Caused by: java.sql.SQLRecoverableException: Closed Connection
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3331)
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3232)
at oracle.jdbc.OracleConnectionWrapper.prepareStatement(OracleConnectionWrapper.java:115)
at oracle.spatial.rdf.client.jena.Oracle.executeQuery(Oracle.java:411)
at oracle.spatial.rdf.client.jena.Oracle.executeQuery(Oracle.java:374)
at oracle.spatial.rdf.client.jena.Oracle.executeQuery(Oracle.java:366)
at oracle.spatial.rdf.client.jena.Oracle.executeQuery(Oracle.java:354)
at oracle.spatial.rdf.client.jena.OracleSemQueryPlan.executeBindings(OracleSemQueryPlan.java:457)
... 75 more
When we turned off the shared servers, the application worked fine again.
We're not exactly sure where to even look to find out what's causing it, whether it's configuration on the application side or database side. Any insight into this error or where to look is greatly appreciated.
There are no corresponding ORA errors, as it seems the database is treating this like business-as-usual. We've tried increasing the number of shared servers, as well as dispatchers,
Please let me know if I should supply any more information that I neglected to cover. Thank you!
Update: We were able to get past these errors by turning on the dispatcher pool on the server. This means we added (POOL=on) to the dispatchers parameter. Now we are seeing intermittent SQL queries hanging, which I thought was from underallocating the shared servers, but there are 3 or 4 of them that aren't processing a single message.
