• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Some indexes not used in JDBC call

 
Jefferson Parke
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34839
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeff,
Strange. Is it safe to assume that the query is being run with the same user id through JDBC and SQLPlus?
 
Jefferson Parke
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's actually not safe to assume that, and yes the performance difference is attributable to different users. It's still the same database instance and same table being read (no synonyms or anything). I'm now looking into Oracle table policies. This is no longer a JDBC issue.

Thanks!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic