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 18.104.22.168.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?
I guess that when you were obtaining the execution plan you did not use bind variables in SQL Plus.
I mean did you ask the excution plan for
select * from customers where customerno=:a
or did you ask the plan for select * from customers where customerno=123445somevalue
becase it differs and the first one will give you the execution plan with prepared statements.If you want that index to be used at all times you may consider giving a query hint.
Thanks for the response. I'm not using bind parameters in SQL*Plus nor JDBC. I think I've isolated the problem to the user. If I switch users used to connect to the DB the indexes used change. I'm still investigating and I realize that this is more of an Oracle issue now than a JDBC issue but if anyone has suggestions let me know. Thanks.
We are *suffering* through this as well. We've tried hints, different JDBC versions, updating stats.
We've tried different connection properties as well.
Have you been able to pin-point your issue, maybe it is similar to what we're dealing with.