Fwd: Jboss/Oracle/select 1 form dual
Date: Fri, 8 May 2009 10:35:13 +0100
Well yes, but millions of times a day (around 800k times per hour in my example, on a system simulating 2000 concurrent users)? The same error would be returned from SELECT 1 FROM DUAL or your real application SQL; so why don't the JDBC driver writers deal with the problem when it is encountered in the first real application SQL call rather than introduce an overhead call? IMHO it's just because it's "easier" for them - and not much easier anyway, as they still need to build error handling into all the later calls (including connection problems).
The only justification for a keep-alive call is if there has been a long delay since the last use of a connection, ie if it has been sitting idle in the connection pool for many minutes/hours; NOT if the pooled connection is being reused rapidly. But a keep-alive would ideally be while the connection was idle in the pool - and not when it is assigned to an application thread which wants to do real application work.
2009/5/8 Martin Busik <martin.busik_at_busik.de>
> > Personally, I would shoot anyone who configures a pointless additional
> > select statement to check connectivity; I blogged on
> > it<http://preferisco.blogspot.com/2006/04/is-this-oracle-wtf.html>a
> > couple of years ago and it has come up several times on this list. But
> > that's another matter.
> It may become quite hard to find the one, who is doing a "pointless
> additional query".
> The JDBC interface contains no ping (or isAlive or similiar) method.
> Connection pool implementations rely on the JDBC interface, so to check if
> is valid/usable, they can only execute standard conform methods, typically
> "select null from dual".
> Without a connection check, application users (and in the next steps admins
> and developers) are
> bored with more or less regular errors ("The first user on monday morning
> gets this exception")
> So, a "select null from dual" is the least evil one can choose...