-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 Jean-Pierre,
On 7/20/17 6:03 AM, Jean Pierre Urkens wrote: > I’m running Tomcat-8.-5.15 with following Oracle datasource > definition: > > <Resource name="jdbc/idpdatasource" auth="Container" > type="javax.sql.DataSource" username="xxx" password="yyy" > driverClassName="oracle.jdbc.OracleDriver" > url="jdbc:oracle:thin:@zzz:1521:SID" maxTotal="10" maxIdle="10" > maxWaitMillis="10000" Ok. > removeAbandonedOnBorrow="true" removeAbandonedTimeout="120" > logAbandoned="true" validationQuery="select sysdate from dual" > testOnBorrow="true"/> Ok. > Tomcat is using the ojdbc6.jar driver library to connect to an > Oracle 12c DB instance. Initially this datasource declaration seems > to work fine. > > However when resuming a HTTP session after a period of inactivity > the database pool seems to return stale DB connections. The log > file shows entries like: > > > [IDP] 2017-07-20 11:28:35,655 [T8N2TP1-13] DEBUG $--$ > (be.xm.base.database.DBPoolManager:141) - Connection: 502672209, > URL=jdbc:oracle:thin:@zzz:1521:SID, UserName=xxx, Oracle JDBC > driver > > [IDP] 2017-07-20 11:28:35,656 [T8N2TP1-13] DEBUG $--$ > (org.hibernate.impl.SessionImpl:221) - opened session at > timestamp: 15005429156 > > [IDP] 2017-07-20 11:28:35,657 [T8N2TP1-13] DEBUG $--$ > (org.hibernate.jdbc.AbstractBatcher:389) - about to open > PreparedStatement (open PreparedStatements: 0, globally: 0) > > [IDP] 2017-07-20 11:*28:35*,657 [T8N2TP1-13] DEBUG $--$ > (org.hibernate.jdbc.ConnectionManager:421) - opening JDBC > connection > > [IDP] 2017-07-20 11:*28:54*,802 [T8N2TP1-13] DEBUG $--$ > (org.hibernate.jdbc.AbstractBatcher:424) - select this_.PERSOONID > as PERSOONID2_0_, this_.rrn as rrn2_0_ from APP_ID_P_RRN.PERSOON > this_ where (this_.rrn like ?) > > There is almost 20 seconds between opening the JDBC connection and > effectively sending the SQL query. Now the standard HTTP connector > in Tomcat is configured with a connectionTimeout=20000 which in > this situation will cause the request to fail. Right. > Why does opening the JDBC connection takes so long? Because... Oracle? TCP connections in various states of shutdown/wait could also be the case. > Is this an indication that the DBConnection returned by the DBCP > is stale and if so why didn’t the pool cleaned up this connection > itself? It may in fact have cleaned-up and re-established this connection, but it may have taken 20 seconds to do it. You might want to enable these settings: timeBetweenEvictionRunsMillis="10000" // maintenance cycle in ms testWhileIdle="true" // test connection validity during maint cycle validationQueryTimeout="[something less than 20s]" Since you expect SELECT sysdate FROM DUAL to execute rather quickly, you could set a low timeout and see if that causes the connection-validity check to progress more quickly. You would do this if you didn't want to enable the background maintenance thread. - -chris -----BEGIN PGP SIGNATURE----- Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJZcMV8AAoJEBzwKT+lPKRYM7UQAIBheGgCHvgT7zBq6FzaaZIy lOySRjxt5HpsHa/HyogipfMpnUDkZRFX2fFwQHr44BxliVhn8gXiqVmaWp2+i5s5 XNAjMJOy3DRLqv67y1+gbvNmmRqVXtOQoJbPUdxAbp8TQJdLjFl6sZd+MXvpTwoU MMTW34NZiOXtuY9nUA/kHOr0bGSuxNJuzmmwwecptw/G87AxIbsTIniVSMYM5nje YUJRQKjS0GhYtaWaaqJ0TJO378D2YsS0aR/6bXlrD2dtoyT6NFihBaBFzaJUsu2N rhsrduxoq5aWLKAUONzgP1ot3Bp1KwIYeUlg0tFwlB2dFKbRjnHcfjn5eT0CRDDz aGSvB8dmAAb+3LbKWsDbpHuOxgHHvTCemKfWBzudBqNqjW11mmohwekdU2HVdprk K7j7HKmcyS0CRa320iwaiVeFTcWoLdkQvOt41HdBAGa1ofS+VRx2p2aZOVUeObio xD5lj4S9O2a61DFMJMyXmZaR+EW82KturtKomfq2vcrcL5dragnKQSxZdHnGDwNR dhgkJ3qEQIHaLJ+P3SeBrFSDgEQ/ue88xAH8wCU/vgzvbs5LFDNpDtTPNBnOl2KP mlVYwq1rHgqCQIfU6w3tB04dwL6lFm0AVZslTfm8NKwOVWmfI5ltduHSWdOakHhH WPGXQo7HKVEuYRnijCq9 =gVkc -----END PGP SIGNATURE----- --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
