Some indexes not used in JDBC call

Thread view:

samuelrollins@gmail.com - 14 Feb 2007 15:27 GMT

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,
Sam

Reply to this Message

Juha Laiho - 26 Feb 2007 16:02 GMT

samuelrollins@gmail.com said:

>I'm having a problem where a JDBC PreparedStatement without bind
>parameters can take more than a minute to execute a query that takes
[quoted text clipped - 10 lines]
>
>Does anyone know why this would happen?

It might be better if you took this to an Oracle-related group, or to
Oracle forums, but one guess, anyway.

With SQL*Plus I expect that you create a complete query with fixed values
whereas with JDBC you create the query with placeholders for values.
This affects Oracle optimizer somewhat, and can make Oracle leave out
any indexes regarding the fields compared with placeholders.

Signature

Wolf a.k.a. Juha Laiho Espoo, Finland
(GC 3.0) GIT d- s+: a C++ ULSH++++$ P++@ L+++ E- W+$@ N++ !K w !O !M V
PS(+) PE Y+ PGP(+) t- 5 !X R !tv b+ !DI D G e+ h---- r+++ y++++
"...cancel my subscription to the resurrection!" (Jim Morrison)

Reply to this Message

kuassi.mensah@gmail.com - 27 Feb 2007 17:28 GMT

Hi,

As i have explained in Oracle Forums, even though this is my own
speculation, if you were using JDBC-Thin then this is certainly due to
the fact that SQL*plus and JDBC are not using the same version of TTC
protocol messages. JDBC-Thin is pure Java and has to turn each release
of C/OCI based protocol into pure Java; sometimes the version being
used by a particular JDBC release is not in sync with the one used by
SQL*Plus. If you were using JDBC-OCI then the problem is elsewhere.

Kuassi http://db360.blogspot.com

On Feb 14, 7:27 am, samuelroll...@gmail.com wrote:

> Hello everyone,
>
[quoted text clipped - 15 lines]
> Thanks,
> Sam

Reply to this Message

Some indexes not used in JDBC call

Free Magazines