RE: Bind Variable - Query slow
Date: Fri, 23 Mar 2012 09:43:18 -0400
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.
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
Cc: Oracle-L Freelists
Subject: Re: Bind Variable - Query slow
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.
On Fri, Mar 23, 2012 at 5:53 AM, GovindanK <gkatteri_at_gmail.com> wrote:
> Linux RAC ,126.96.36.199
> 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-lReceived on Fri Mar 23 2012 - 08:43:18 CDT
>>> 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