Some indexes not used in JDBC call
Hello everyone,
I'm having a problem where a JDBC PreparedStatement without bind parameters can take more than a minute to execute a query that takes less than a second to execute in SQL*Plus. The query is identical, the database instance is the same, neither query is cached, and the query returns only 18 records with 11 columns all of which are either VARCHAR2 or NUMBER. I'm using Oracle's JDBC 2.0 drivers (classes12.jar) and Oracle 8i (Release 8.1.7.4.0) database. Oracle DB is set to use the cost-based optimizer.
I did an explain plan in SQL*Plus and via JDBC. It turns out that some of the unique indexes that are used when executing the query in SQL*Plus are not used when executing via JDBC.
Does anyone know why this would happen?
Thanks,
Jeff
[795 byte] By [
jaypeea] at [2007-11-26 18:27:19]
# 1
Sorry, but I don抰 have an exact answer to your question but perhaps the following thoughts will help. Also, I don抰 think this is a JDBC issue it is more likely an Oracle issue. You might try the Oracle support forums and ask them about this.
You are on an older version of Oracle when the Oracle COST based optimizer was in its infancy. The COST based optimizer didn't always work as expected. Do you know which optimizer you are using (COST or RULE). I'm assuming COST because RULE was awfully consistent about what execution path it was going to take. If you are using COST make sure you have up to date database statistics and if that doesn't help try it with either an Oracle hint to use the index, or if it is allowed in your shop an Oracle hint to use the RULE based optimizer.
What you are describing should not happen even when using the flaky 8.x COST based optimizer unless you have bogus database statistics on the tables or you are providing different literal values, or the underlying tables are growing exponentially between tests, or there is a bug in Oracle.
If these thoughts don抰 help you resolve this then I have a couple of questions that might help me understand some important details.
What happens when you use bind parameters? Does it still take more then 1 minute through JDBC?
When you say the query is identical do you mean syntactically identical with all SQL syntax being the same and all literals being identical or is it logically identical with all SQL syntax being the same but using different literals?
# 2
Thanks for the quick reply. We are using Oracle's cost based optimizer. The JDBC vs. SQL*Plus executions take place within seconds of one another. The statistics aren't changed between executions. The table data isn't changed between executions. The query string and literals are both identical between executions. We haven't tried using bind parameters because our understanding is that this would only make things slower for a single execution of the statement (for the purposes of this test).
Now, I have one more clue that just came to my attention. It seems that although all else is identical, the one difference between the executions is the user used to connect to Oracle. I tried switching users in SQL*Plus and the same dramatic performance difference happened there, which really makes this an Oracle question, not a JDBC question.
Thanks for your time. If you happen to know the answer then please post it but I'll be shifting my focus to Oracle's forums now to find out why indexes would be treated differently between users.
- Jeff
# 3
since you use a bind variable,oracle's cost based optimizer can not decide correctly whether to use this index is a good idea.You may try giving hits between /*+*
# 4
Actually, I'm not using bind variables. I still haven't gotten to the root of the problem but it has something to do with user used to login to the database.
# 5
> since you use a bind variable,oracle's cost based
> optimizer can not decide correctly whether to use
> this index is a good idea.
The OP said he was NOT using bind variables in the testing within the SQL String of the PreparedStatement so this comment doesn't address his current problem.
To the OP:
Sounds like you have an Oracle permissions issue not related to JDBC specifically. Shouldn't be to hard to determine what the permission differences are between the two userids.
Regarding proper use of PreparedStatement
ALWAYS use PreparedStatement and host variables. There are 100's if not 1000's of posts documentation why this is a good idea here on the forums. Here are a couple of reasons why PreparedStatement with Host variables is a good idea.
1) PreparedStatement using host variables will provide you best overall system performance.
2) PreparedStatement using host variables eliminates the very real security risk of SQL injection.
3) PreparedStatement using host variables aids the programmer in handling escape sequence and the frequent errors associated with special characters within SQL strings.
4) PreparedStatement using host variables allows JDBC to take care of the majority of data conversions between Java and your database simplifying and standardizing data conversion coding
There are isolated cases where using Host variables impedes performance when compared against dynamic SQL (SQL with literals) but they are few and far between (1: 1000?) and the standard should be to always use PreparedStatement with host variables.
Good luck on resolving your current problem and remember to always use PreparedStatements WITH host variables when coding in Java!
WFF
# 6
One more thought.
Rather then a permissions problem this could also be a problem with Oracle synonyms. If you are using private and global synonyms it is possible that the private synonym points to a different table then the public synonym.
The order of resolution in Oracle is:
1) current user
2) private synonym
3) public synonym
If you are overriding the public synonym you could be pointing to a different table with different data in it or you could be pointing to a different table with the same data in BUT with out dated Oracle statistics. In the first instance you would probably see different data being returned but in the second instance the ResultSet would contain the same data and would be harder to debug. In both cases you could very easily see profound differences in performance as a symptom.
# 7
Thanks for the advice on host parameters. One reason they aren't being used in this case is because the query string doesn't contain any user input.
I've confirmed that there are no public or private synonyms getting in the way.
I'm now looking into table policies that may be modifying the query predicates.
Thanks,
Jeff
