//
you're reading...
Java Programming, Oracle Database Toubleshooting, Oracle Installation & Configuration

java.sql.SQLRecoverableException: No more data to read from socket cause by connection pool running out

java.sql.SQLRecoverableException: No more data to read from socket caused by connection pool running out

Issues

Currently, in Oracle database server is built on more than one tablespace for different systems, both systems at the same time in the course of a project being developed in a test run, appeared from time to time the connection pool is full, connection is not the problem, so need to modify the Oracle connection pool configuration.

Oracle currently supports only one connection pool, pool name is “SYS_DEFAULT_CONNECTION_POOL”, connection pool management information is also a package named “DBMS_CONNECTION_POOL”.

error we got from appliction layer

java.sql.SQLRecoverableException: No more data to read from socket
	at com.orchestral.templates.extdatasource.standard.StandardExtDischargeEventManager.getFirstAdmitDate(StandardExtDischargeEventManager.java:272)
	at com.orchestral.templates.extdatasource.standard.StandardExtDischargeEventManager.loadFirstAdmitDate(StandardExtDischargeEventManager.java:92)
	at com.orchestral.templates.web.servlets.dischargesummary.DischargeSummaryExtLoadFirstAdmitDateHandler.process(DischargeSummaryExtLoadFirstAdmitDateHandler.java:29)
	at come.newzontechtest.servlet.ControllerServlet.service(ControllerServlet.java:77)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:89)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:369)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:288)
	at com.orchestral.servlet.filter.NoCacheFilter.doFilter(NoCacheFilter.java:6)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:245)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:198)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:273)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:272)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:257)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:237)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:148)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:376)
	at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:305)
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:488)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:923)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:613)
	at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:845)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:883)
	at java.lang.Thread.run(Thread.java:549)
Nested Exception: 
java.sql.SQLRecoverableException: No more data to read from socket
	at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1724)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:238)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:512)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:200)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:832)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1189)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1354)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3524)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3679)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1563)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:73)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:73)
	at com.orchestral.templates.extdatasource.standard.StandardExtDischargeEventManager.getFirstAdmitDate(StandardExtDischargeEventManager.java:56)
	at com.orchestral.templates.extdatasource.standard.StandardExtDischargeEventManager.loadFirstAdmitDate(StandardExtDischargeEventManager.java:56)
	at com.orchestral.templates.web.servlets.dischargesummary.DischargeSummaryExtLoadFirstAdmitDateHandler.process(DischargeSummaryExtLoadFirstAdmitDateHandler.java:29)
	at come.newzontechtest.servlet.ControllerServlet.service(ControllerServlet.java:87)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:749)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:556)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
	at com.orchestral.servlet.filter.NoCacheFilter.doFilter(NoCacheFilter.java:9)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:173)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
	at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:200)
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:773)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:713)
	at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:896)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:878)
	at java.lang.Thread.run(Thread.java:620)

Review the DBMS_CONNECTION_POOL

SQL> desc DBMS_CONNECTION_POOL  
  
Element          Type  
---------------- ---------   
ALTER_PARAM      PROCEDURE  
CONFIGURE_POOL   PROCEDURE   
RESTORE_DEFAULTS PROCEDURE  
START_POOL       PROCEDURE  
STOP_POOL        PROCEDURE  

There are five stored procedure packages. Oracle contains a default connection pool SYS_DEFAULT_CONNECTION_POOL, but did not open, open the connection pool needs to be displayed, the first step is to open the connection pool:

exec DBMS_CONNECTION_POOL.START_POOL('SYS_DEFAULT_CONNECTION_POOL');  

This operation is done only once, after the next restart of the database connection pool will open automatically.

After opening the connection pool can be queried through the system view dba_cpool_info:

SQL> column connection_pool format a30
SQL> column status format a10
SQL> select connection_pool,status from dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ ----------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE

When the connection pool started, you can view the connection pool configuration items by DBMS_CONNECTION_POOL.CONFIGURE POOL.

SQL> desc DBMS_CONNECTION_POOL.CONFIGURE_POOL  
Parameter              Type           Mode Default?  
---------------------- -------------- ---- --------  
POOL_NAME              VARCHAR2       IN   Y  
MINSIZE                BINARY_INTEGER IN   Y  
MAXSIZE                BINARY_INTEGER IN   Y  
INCRSIZE               BINARY_INTEGER IN   Y  
SESSION_CACHED_CURSORS BINARY_INTEGER IN   Y  
INACTIVITY_TIMEOUT     BINARY_INTEGER IN   Y  
MAX_THINK_TIME         BINARY_INTEGER IN   Y  
MAX_USE_SESSION        BINARY_INTEGER IN   Y  
MAX_LIFETIME_SESSION   BINARY_INTEGER IN   Y 

Parameter Description

  • MINSIZE: in the pool in a minimum number of pooled servers, default is 4.
  • MAXSIZE: the maximum number of pooled servers in the pool, the default is 40.
  • INCRSIZE: this parameter is when a client application needs to connect, when pooled servers unavailable state, every time increasing the number of pooled servers in the pool.
  • SESSION_CACHED_CURSORS: cache in each pooled servers session cursor number, the default is 20.
  • The maximum time INACTIVITY_TIMEOUT pooled server in idle state, in seconds, over this time, the server will be stopped. The default is 300.

  • MAX_THINK_TIME: obtain a pooled server from the pool in a client later, if at the time of not submitted within MAX_THINK_TIME database calls, then this pooled server will be released, the client connection will be stopped. The default is 30, in seconds.
  • Number MAX_USE_SESSION: pooled server can be taken and released on the pool and the default is 5000.
  • MAX_LIFETIME_SESSION The time, in seconds, to live for a pooled server in the pool. Thedefault value is 3600. a pooled server in the pool of the value of life.

NOTE: pooled server in the number of not less than MINSIZE.

You can use DBMS_CONNECTION_POOL.CONFIGURE_POOL or DBMS_CONNECTION_POOL.ALTER_PARAM connection pool settings can be modified.

Let’s look at parameter information:

SQL> desc DBMS_CONNECTION_POOL.ALTER_PARAM  
  
Parameter   Type     Mode Default?  
----------- -------- ---- --------  
POOL_NAME   VARCHAR2 IN   Y  
PARAM_NAME  VARCHAR2 IN  
PARAM_VALUE VARCHAR2 IN  
SQL> exec DBMS_CONNECTION_POOL.ALTER_PARAM ('','minsize','10');  
PL/SQL procedure successfully completed
  
SQL> exec DBMS_CONNECTION_POOL.ALTER_PARAM ('','maxsize','100');  
PL/SQL procedure successfully completed  

Since only one connection pool, the value of the first parameter may be omitted.

There are several system view system more useful:

DBA_CPOOL_INFO This view contains the connection pool status

V$CPOOL_STATS This view contains statistical information on the connection pool

V$CPOOL_CC_STATS This view contains the type of connection pool level statistics

After a successful connection can query modification under Pool Information:

SQL> select CONNECTION_POOL, STATUS,MINSIZE,MAXSIZE from DBA_CPOOL_INFO;

CONNECTION_POOL                STATUS        MINSIZE    MAXSIZE
------------------------------ ---------- ---------- ----------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE            4         40
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: