Re: Bind Variable - Query slow

From: Phillip Jones <phil_at_phillip.im>
Date: Fri, 23 Mar 2012 09:18:48 +0000
Message-ID: <CAOyzJufQR+45f=3wwgt9Tx6pOvMR0VJeRNUiNQZHguu57xwNJA_at_mail.gmail.com>

Hi,
I've seen this happen before - the culprit was hibernate/JDBC passing in a value as a an NVARCHAR2 instead of a VARCHAR2 - I had to create a functional index (TO_NCHAR) as a workaround.

Have a look in dba_hist_sql_bind_metadata & check what type Oracle thinks the bind variable is.

Cheers,

Phil

On Fri, Mar 23, 2012 at 5:53 AM, GovindanK <gkatteri_at_gmail.com> wrote:

> Linux RAC ,11.1.0.7
> Query runs fast with hard coding / SQL*Plus '&' ; But when run through the
> application using bind variables , the plan is different and it goes for
> MERGE CARTESIAN JOIN. Metalink , google , underscore parameters are not of
> much help. Tried different bucket sizes with statistics ; Tried rewriting
> the query; Marginal improvement ; If you hard code or with SQL*Plus '&' the
> query runs in 1 second .. but with bind variable it takes anywhere from 3
> to 10 minutes depending on the input values.
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 23 2012 - 04:18:48 CDT