Strange issue with JDBC execute performance

[ken zhu]

hi everyboy:

i met a strange issue in my j2ee project, i got connection from tomcat dbcp(connection pool) when i
call ResultSet resultSet = pst.executeQuery(sql) it's became very slow need 100 seconds sometime,
1, but i execute the SQL in toad is very fast and it's very simple in fact
2, but when i open or refresh other servlet which also need invoke database, the previous page will load successful very fast.

I don't know how to explain these strange issues and what's the root cause.

Thanks for your hint.

[Kiran Joshi]

Try to check execution plan of the sql query in the database.
Check this when your servlet is firing the sql query.
There could be some issue with the indexes of the database tables involved in the query.

[ken zhu]

Kiran Joshi wrote:Try to check execution plan of the sql query in the database.
Check this when your servlet is firing the sql query.
There could be some issue with the indexes of the database tables involved in the query.

Thanks your reply.
The sql query is very simple just like 'select sysdate from dual' and i executed it in toad or sqlplus, it's very fast.
Just when invoke in servlet it will become slow sometimes, i don't know whether have matter with jdbc driver version or other unknown reason?

[Kiran Joshi]

Ken,

I have witnessed such an issue where the sql query fired by java program was following a different execution path than the one fired through sqlplus or TOAD. And that's why it was taking time too.There was an index which was not being used by query from java prog.
Our DBA had analyzed that by monitoring the execution path of the two cases. He suggested us to use a particular 'hint' in the sql query & it worked.
So the suggestion is, try to monitor the execution plan.
I assume your sql query is querying some db table and not querying 'dual'.

[ken zhu]

You are right after tried, thanks a lot.
Our 'DBA'(actually we have no a real one) change DB configuration and set server as dedicated mode.
Then i found i can't use DBCP connection pool and we use normal jdbc, but it need long time to establish a connection.
So, would i ask how your web application apply which connect DB way? What happen to me?

below code is our connect DB now:
====================================

OracleConnectionPoolDataSource connectionPoolDS = new OracleConnectionPoolDataSource();   
protected PooledConnection pooledconn = null;
protected Connection connection = null;    
 
String url = "jdbc:oracle:thin:@" + hostName + ":" + port + ":" + sid;
connectionPoolDS.setURL(url);
connectionPoolDS.setUser(userName);
connectionPoolDS.setPassword(password);
pooledconn = connPoolDS.getPooledConnection();
connection = pooledconn.getConnection();
......