RE: Bind Variable - Query slow

From: Jorgensen, Finn <Finn.Jorgensen_at_constellation.com>
Date: Fri, 23 Mar 2012 09:43:18 -0400
Message-ID: <9CE162BC5ED2C643956B526A7EDE46FF02EA7DE97918_at_EXM-OMF-04.Ceg.Corp.Net>

Agree on the datatype theory. Find the sql id in the shared pool and then use

select * from table(dbms_xplan.display_cursor('&sql_id',nvl('&child_cursor',0)));

Look at the notes section. If there's something about internal functions or other things that look suspicious you have a bind var data type issue. E.g. ODBC makes bind vars chars by default so comparing to a number column makes oracle execute a function on the column eliminating the possibility of using an index. The developers would have to declare the ODBC var as a number to fix that.

Thanks,
Finn

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Phillip Jones Sent: Friday, March 23, 2012 5:19 AM
To: gkatteri_at_gmail.com
Cc: Oracle-L Freelists
Subject: Re: Bind Variable - Query slow

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




>>> This e-mail and any attachments are confidential, may contain legal,

professional or other privileged information, and are intended solely for the
addressee.  If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. -IP2

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

Received on Fri Mar 23 2012 - 08:43:18 CDT