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 126.96.36.199.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.
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.