Re: Bind Variable - Query slow

From: Greg Rahn <greg_at_structureddata.org>
Date: Fri, 23 Mar 2012 09:33:06 -0700
Message-ID: <CAGXkmivgdNX3359A8GcV3V09Yo3zxMxyFU5cb_AAtfSTGv4TGw_at_mail.gmail.com>

Does the column referenced by the bind variable have a histogram on it? If so, is the value a popular one or not? Have you experimented w/o a histogram?
I would troubleshoot the root cause, but one option is to use a SQL plan baseline to give the plan from the literal version to the bind version. https://blogs.oracle.com/optimizer/entry/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex

On Thu, Mar 22, 2012 at 10:53 PM, 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.
> With hard code , it does not do MCJ but plan is different for few tables.
> I don't say the query is written the best way.
>
>

-- 
Regards,
Greg Rahn  |  blog <http://bit.ly/u9N0i8>  |  twitter <http://bit.ly/v733dJ>  |
 linkedin <http://linkd.in/gregrahn>


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

Received on Fri Mar 23 2012 - 11:33:06 CDT