1

I am running a java program which connects to the oracle db and retrieves some information. The program contains 12 threads, each thread interns execute sql select commands with multiple join conditions on the tables.

Some times the application runs fine and giving the exact result but sometimes it is not. Finally what we have found is the database session is getting killed. But there are no scripts running at the db side to kill the idle sessions, no session time out parameter is set with the db profile for that particular user. No locks on the tables. And i have handled all the exceptions in front end application, but it is not throwing any exceptions. the program is keep on running even the session is killed..... please suggest.............

Thanks ABCReddy

Improve this question
  • Is there any connection pooling going on at the Java end? – Jack Douglas Oct 13 '11 at 10:41
  • No... No session pooling – abcreddy Oct 13 '11 at 11:22
  • 1
    Have you checked your alert log? Is that how you know the sessions are killed? – Jack Douglas Oct 13 '11 at 11:30
1

I would hope you have some session pooling going on, or at least one database connection per thread. This is not clear from your description. In a multi-threaded design, I would not close connections in the threads unless they make their own connections (which I would consider a bad design).

I would get the process running with one thread, then add an additional thread. Once you are comfortable you have that multi-threading gives you the results you want, you can scale up.

You could turn on auditing on the Oracle side to see what is really happening.

You didn't say how long the queries take to run. If they take minutes, to run you need to ensure you have keep-alive on the network connections. Otherwise, the network connection may get dropped.

EDIT: Check the resource limits applicable to the userid you are connecting with. These might get your connections closed. You may also be getting killed off due to exceeding resource limits. I would expect the reason for disconnect to be in the SQL Error text for the connection.

Given how long these queries are taking, you may want to investigate optimizing the queries. Unless you have lots of memory and processors on the database server you may find that the queries run faster when run serially. You may be forcing data out of the buffer pool with this many long queries running in parallel.

Improve this answer
  • Queries usually takes some 3 hours to complete. and i have 12 different connections to the database for 12 threads. – abcreddy Oct 13 '11 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.