2

I have a Java code that uses Spring to connect and execute sql on an Oracle DB. I have a query that takes long time to execute (20 minutes or sometimes more). I have a Executor Service and it has a Thread that will execute the query and process the results. If i put a timeout to the DB and Spring, the system will time out correctly but will return nothing else before that. If i run the query from SQL plus, it will return values. The time out is set up 3 times what it takes to execute on SQL Developer.

Any ideas!?

Improve this question
3
  • Can you post your relevant section of query code and your spring configuration? Also, how are you using Spring? Just to inject data base connection info? Commented Apr 11, 2011 at 18:35
  • Sorry, no code. I'm using spring to inject DB info and to manage the queries. Commented Apr 11, 2011 at 18:41
  • I notice yesterday that i had the time out set to 20 minutes. More than 20 minutes after a query i was not getting a timeout error every time. So i think the amount of data that i'm bringing over with the query is causing some kind of issue...i put more logs and more tracers and see what happens... Commented Apr 12, 2011 at 15:47

2 Answers 2

Reset to default
1

Assuming that your Spring query is using bind variables, are you using bind variables when you execute the query in SQL*Plus/ SQL Developer? Or are you using literals?

What version of Oracle are you using?

Have you checked to see whether the query plans for the two environments are different?

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

using Oracle 10. We are using SimpleJdbcTemplate from Spring, with queries in prepare statement format (using "?").
OK, so it sounds like you are using bind variables from Spring. Are you using bind variables when you use SQL*Plus/ SQL Developer? Or are you using literals? Have you checked to see if the same query plan is being used?
we are using literals from SQL Developer. I will change the code to use literals and will get back to you on the results.
1

20 minutes for a query in Oracle? I'll bet you don't have appropriate indexes on the columns in your WHERE clause.

The dead giveaway is to do an EXPLAIN PLAN on the query. If you see a TABLE SCAN, take appropriate measures.

If you can run the same query in SQL*Plus and see it return in a reasonable time, then I'm incorrect and the problem is due to something else that you did in Java code.

I don't see why you need a separate thread for a query. I'd run the code straight, without a thread, and see how it behaves. If you aren't indexed properly, add some; if the query brings back too much data, add WHERE clauses to restrict it. You've taken extraordinary measures without really understanding what the root cause is.

Improve this answer

7 Comments

yeah...sadly is true. Problem is that we are connecting to an schema that is out of our control. They are not the smartest developers and they don't want to help up. We set up views to hammer their data into our domain. :-(. Believe me, there is nothing we can do about it.
I'd ask them to run EXPLAIN PLAN on the query and send the results.
Yeah...not gonna happen...they hate us because the customer loves us. They are putting lots of hurdles just to make us look bad. Too many things...we are dealing with child minded monkeys...its not easy...
Is this a business or a playground? If the customer loves you, I'd get some leverage. Just ask for the data and let it tell the story.
I'm on your side, by the way. There's no good reason for one group to hold the best interests of the customer hostage just because of animosity. It would be especially grievous if they needed you to instruct them on how to configure and manage an Oracle database properly.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.