RE: Jboss/Oracle/select 1 form dual

Well, in this case - it is Jboss who will be doing it - since it is the Jboss Validation string - to close idle connections that are not being used.  

I don't think it is in the JDBC driver itself.  

I will find out more.  

• Pradeep

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nigel Thomas Sent: Friday, May 08, 2009 5:35 AM
To: Oracle-L Freelists
Subject: Fwd: Jboss/Oracle/select 1 form dual    

Martin

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.

Regards Nigel

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 a

	connection
	is valid/usable, they can only execute standard conform methods,
typically a
	"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

	SCNR,
	Martin

	
	--
	http://www.freelists.org/webpage/oracle-l
	
	

 

 


 

Pradeep Chetal
Sr. Director - Infrastructure Architecture

Mformation Technologies Inc.
Switchboard: +1 732 692 6200  

Visit WWW.MFORMATION.COM and see how the MFORMATION SERVICE MANAGER can help you to transform your business

Please consider the environment before printing

--
http://www.freelists.org/webpage/oracle-l