I know the situation that the reliable check is to validate connection by executing simple SQL query; Actually we used the simple SQL - select 1 from dual, but the total soft parse is 48%, and 700 queries per second happened from oracle awr report, which certainly would bring impact to db performance; Currently we are looking for some workaround to reduce the frequency of SQL query, Of course, we can reduce the connection borrowing in order to get transactions down; that is the way which can be used;
I wonder if any other workaound exist to make our db performance better, more is better for your poor db performance,
I think the only reliable way to check whether connection is valid is to execute something using the connection (so it requires roundtrip to db).
a) this execution happens only once for your transaction (which may include numbers of CRUD operations etc)
b) Validation query is chosen to be simple (like select 1 from dual for oracle), so that performance penalty is minimal.
So IMO unless you are designing some realtime application you can stop worrying about additional roundtrip stealing maybe a dozen of milliseconds...
On Wed, Jun 10, 2009 at 7:35 AM, jochen <firstname.lastname@example.org> wrote:
When I was using the "validationQuery" and "testOnBorrow" properties to ensure connection works fine, did it mean two round-trip happened? Validation query and my SQL query? thus, how to reduce the db round-trip while the query became huge?
I knew iBatis to mysql would not ensure connection valid while disabling testOnBorrow, if it is the same toward oracle? is there any workaound to reduce db round-trip? any help is appreciated, thanks.