I am working on struts application, when I connect the application with development database it executes query( result = stmt.executeQuery()) in few seconds but when I changes only user name and password in server.xml and connects to production and executes the same query (which fetches same number of records from database as development), it gets stuck for 40-45 mins.
While executing same query in toad(for both dev and production) gets result in few seconds. Issue rises only from application.
Please help.Any kind of help is really appreciated.
Possible causes to look into are:
1) Is there a lock on the table in production that it is waiting on? (you may need a DBA to check)
2) Is the connection pool exhausted? Maybe the app is waiting to start?
3) How much data is being returned. Maybe there are network issues. This one seems unlikely as it wouldn't account for 40 minutes.
Thanks for your reply but please find below the comments against causes you suggested:
1) Is there a lock on the table in production that it is waiting on? (you may need a DBA to check) --If there would be a lock on table then it won't even execute from toad.
2) Is the connection pool exhausted? Maybe the app is waiting to start? --I am connecting my local(from eclipse) to production by restarting tomcat every time.
3) How much data is being returned. Maybe there are network issues. --It returns hardly 5 rows.
You are changing only the username and password and not the IP address to connect to the production database?
What about other queries from the application? Are they running successfully on the production database?
Yes I have changed everything in server.xml and its been connected to production database thats why its giving result in 40 mins for the same query which runs in few seconds in sql(toad).
Thanks for your reply, yes I debugged the application and it gets stucked on stmt.executeQuery();. Prints all the comments before executeQuery and takes around 40 mins on it and then prints next statement.
As far as query is concerned it gets executed in seconds in sql(toad) in both dev and production , but only through application its creating problem that only in production not in dev.
Smita Ahuja wrote:yes I debugged the application and it gets stucked on stmt.executeQuery();. P
This doesn't sound like a Struts problem so I'm going to move this topic.
We need a lot more detail about your application: Database, JDBC driver, query, and so on. Otherwise we are just guessing.
Have you tried running the application outside Eclipse?
Yes other queries are also bit slow but in one query only it takes 40 mins.Yes they are running on production database in 30-40 secs.
We are using oracle 10g with ojdbc14.jar with jdk1.4
This application is deployed on linux server and running successfully from the past 8-10 yrs . Its creating an issue from the past one month. So for digging out the issue, I changed my eclipse server.xml settings and tested with production. the same issue persists.
You need to do some investigations.
Determine/confirm if the delay is happening in the database call or in the application before calling the database.
Do you use the same driver in toad as the one you use in the application?
If you create a small Java program and run the query with a connection obtained using DriverManager does it run faster?
Just saw your code , Can i suggest you some thing write one stand alone program taking a 1) connection object (make sure that connection is coming - confirm by sout staments) ,2) prepare a query (in a single string object) , 3)if you need pass the parameter in dynamically or else hard code in the previous query , 4) execute the query - rs
By implementing this, it simplifies your code and helps you to debug the code , so that you easily identify and fix the issue.
It looks like a particularly bad case of bind variables problem. I'll try to explain the issue, but we need to go through some concepts first, so please bear with me.
Let's say that we have a table of invoices in our database, which contains million of rows. We have a properly indexed PAID column with values of 'Y' and 'N', which indicates whether the particular invoice has already been paid. Say that we have only ten unpaid invoices now, and the rest has been paid. It's obvious that the values are not evenly distributed in our table - there are 10 'N' values in that particular column, and 999,990 'Y' values. This situation - the uneven distribution of values - is usually called a "data skew" (or just "skew") and it creates some troubles with bind variables. There are ways to let the database know there data are skewed, through something called "statistics", but for now let's just say that the database can - and actually probably does - know about the skew.
At this point we'll have a look at how the databases processes queries in general, and queries with binds in particular. When you submit a query to the database, it compiles the query to some sort of executable code so that it can run it. In databases, this executable code is usually called a "plan". We also need to know that databases generally create a plan only the first time they see a statement, because creating a plan is an expensive operation, and - especially in an OLTP environment - the database must be able to execute thousands of queries quickly; there is no time to compile ("prepare") each one individually. In this context, statements are considered "the same" if the text of the query is identical. Multiple executions of a statement with different values of parameters, but the same text of the query, result in the execution of the same code.
Knowing this, let's have a look at some simple queries. What happens when we submit the following query to the database?
This is a very simple query, and the database has practically only two ways of executing it. It can either go through all the rows in the table, looking for ones where the PAID column contains the value of 'N', or it can use an index. In this case, the index is obviously the right solution. The database will use an index and return the ten unpaid invoices quickly.
Let's say we execute a different query:
Well, in this case, the index would not be useful. The database would have to do many times more work if it used an index in this situation. The correct plan for this query is to read the entire table (this kind of processing is usually called "full scan") and only keep the rows which contain the value of 'Y'. This query will obviously execute much slower than the first one, because it has to read the entire table and not just a few entries in the index.
Eventually we're going to the gist of our problem. Consider this query:
What should the database do now? Well, this is not an easy question. Remember that after creating plan for this query, the database will use it for future executions, regardless of the value of the parameter we'll execute it with.
To choose the correct plan, the database has to estimate somehow how many rows will the statement return, to decide between using an index, or a full scan. There might be different strategies for this, but Oracle uses a straightforward one: when it is executing the query for the first time, it looks at the value of the parameters that were given to it, and prepares a plan that is suitable for the particular set of parameters it encountered. This process is called "bind peeking".
So, the first execution of the above query determines which plan will be used for all subsequent executions of that query. If the query was first executed using value 'N' for the parameter, all subsequent executions will use an index. If the query was first executed using value 'Y' for the parameter, all subsequent executions will use full scan. Note that the opposite plan is incorrect in both cases, but the latter one is much more to be felt by the end user. The database returns only 10 rows, but makes all the work needed to read a million of rows. Whoa!
So, executing the query from an application that uses binds takes ages, but executing exact same query, just with string literals instead of parameters, in Toad returns immediately. I bet it sounds pretty familiar...
(You might ask what the "first execution of a query" means. Good question. The compiled queries are kept in some sort of a cache, which is generally cleared when the database is restarted. The cache can also be cleared by a database administrator (though they don't look kindly at this kind of a request), and the statement can also be evicted from the cache when it is not active and the cache is full. Specifically, the "first execution" in this context doesn't mean the first execution in a particular application - a cached plan will be used if the text of the query being executed by an application matches the text of the cached query. In your particular case, the database might have been restarted a month ago and then the first execution of your query happened with an unsuitable values of bind variables - parameters in JDBC terminology.)
At this point, I'd suggest to verify this is actually the case by a simple experiment: modify the text of the prepared statement in your application in a way that doesn't affect the meaning and run the application in production again. Just adding or removing a space or a line break counts, but I'd suggest to add a comment into the query, such as:
If I'm right, the statement with a comment will execute quickly.
Can you try this in production? If you can, let us know and we'll go on. If you can't, we might need some help from your DBA to flush the cache (which would be only temporary solution, of course) to see whether it helped.
Today I tested the query with a standalone code when I hard code the title value, it gives result in few seconds but when I pass variable and then gives value in stmt.setString(), it again takes time on execute query.
Please find below both samples:
Sample1 gets halted after printing System.out.println("----listQuery----"); for few minutes and then proceed whereas Sample1 executes in few seconds
Smita, I've added code tags to your posts to make them more readable. You can do so yourself next time using the Code button above the edit box.
Is this question related to your previous one? Did you try what I suggested in my last post?
This issue is probably different from your previous one. Using LIKE operator might not be very effective in Oracle, especially if it starts with a leading '%' sign. In this case you're trying to find all rows containing some substring, and the standard index is not useful at all for this kind of queries, so Oracle has to do a full scan and evaluate the LIKE condition against all rows in the table. Needless to say, this can be slow. Without the leading percent sign the LIKE operator can actually use an index, in your case the LIKE 'PLUMBING%' will simply look at the index for all entries stating with 'PLUMBING'. They are all grouped together in the index and therefore easily accessible through it.
But when you use the LIKE ? clause, the database cannot reliably use an index, because you can execute this query with any value you like, eg. 'PLUMBING%', but also '%TECHNICIAN' (just for an example). In the latter case, the database couldn't use an index at all. Therefore all LIKE ? queries result into a full table scan, and thus are slow, even if you pass "good" search string (one without a leading percent sign) to it.
However, Oracle supports full-text indexing of its database tables. These full text indexes can then be used for efficient searches using the LIKE operator with leading percent sign, regardless of whether you use a parameter or not. I don't have direct experience with it myself, but a good start might be to search for oracle text index.