Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!news.swapon.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: Harry Tuttle <SOZRBLNTLEEE@spammotel.com>
Newsgroups: comp.lang.java.databases
Subject: Re: Query performance
Date: Tue, 16 Apr 2013 14:59:10 +0200
Lines: 33
Message-ID: <at506qFdv9oU1@mid.individual.net>
References: <02ee34f7-b22a-4704-b504-4b24920092a3@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: individual.net zHIsGlg7M5ByM5pfV68sUgqzy07GzGSP8BVZ5g0ufrh/cyCy8=
Cancel-Lock: sha1:AKLtytToSApgE7pxQL4GvJ1Rvkg=
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
In-Reply-To: <02ee34f7-b22a-4704-b504-4b24920092a3@googlegroups.com>
Xref: csiph.com comp.lang.java.databases:599

thomas.lehmann@adtech.com, 05.04.2013 13:16:
> Hi,
>
> using a very simple Java application I've connected to a database that
> is Oracle. The code just uses standard JDBC handling like Connection,
> Statement and ResultSet.
>
> Now ...
>
> A very specific SQL statement takes about 250ms just providing an ID.
> Using "SQuirrel" or "SQL Plus" as clients exactly the same query takes 5ms.
>
> SQuirrel is in Java and - so far I have seen - the same JDBC handling
> has been taken as I did. For Squirrel to say: I have been using the
> same Oracle (thin) driver and same connection parameter (for sure).
>
> So what might be the reason for the performance problem?
>

I assume in Squirrel or SQL*PLus you are using literals for your statement parameters.

Inside your code you are probably using bind variables - which can lead to completely different execution plans.


To confirm this theory, you need to do an explain with and without bind variables:

explain plan for select * from foo where id = ?;

vs.

explain plan for select * from foo where id = 42;