Sunday, April 23, 2006

Is this an Oracle WTF?

Using JDBC with connection pooling, we see this in our statspack (over a 15 minute period, on one of a 3 node RAC setup:

SQL ordered by Executions for DB: XXX





ExecutionsRows ProcessedStatement
192,827192,826select 'x' from dual
54,64454,644INSERT INTO AUDIT ...


We seem to get select 'x' from dual issued exactly once as each connection is opened. This can represent a significant fraction of the executions. I raised this as an SR and was told:

This is a standard feature in the later JDBC libraries (10.1.0.x onwards) and is designed to ensure that the connection remains up and running - this is documented in the JDBC documentation ... This can be altered but not stopped by setting the min connections in the datasource to a minumum needed.

See BUG:4601037 - PINGDATABASE() PERFORMANCE CAN BE IMPROVED IN BOTH DRIVERS (against JDBC 10.1.x) however this is not published, and not fixed until JDBC 11.1.

Is it just me, or does it seem mad to have to test that your connection is up by sending an SQL statement? That's the first thing the app is going to do itself anyway; why not detect the failure then? In a well tuned app, the sessions should spend very little time 'on the shelf' when the app is busy; the database is just as likely to disappear while the session is being actively used as while it is resting. So this logic simply provides extra protection to the very first JDBC operation in a transaction; why not at least be able to tell it not to send this 'heartbeat' query unless the pooled session has been idle for longer than some (configurable) threshold.

Cheers Nigel

3 comments:

VJ said...

Hi Nigel,

I have a question with regards to connection pooling using JDBC for RAC environment.

Can I use connection pooling mechanism for Oracle 8i for 10g just by replacing connection url value with that of RAC parameters?

Thanks,
vj

Nigel said...

VJ

Connection pooling happens in the app server (the JDBC client), not in the database server. Are you thinking about how you distribute connections between RAC nodes - eg using load balancing and/or TAF?

Yes, you can pool sessions across your RAC node using the RAC service name; but that can have some drawbacks - see my other posts tagged JDBC.

Regards Nigel

VJ said...

Thank you for your reply. Yes, I was talking about connection pooling on client side, but wasn't sure if there is any difference between connection pooling for 8i and 10g except for the connection url being different for 10g with RAC params.

Thank you,
VJ