Re: Bind Variable - Query slow

From: GovindanK <gkatteri_at_gmail.com>
Date: Tue, 27 Mar 2012 13:55:58 -0700
Message-ID: <CAPb52X4gQWpBfJtBspudyzeJi1PUgBHiLL+DDzuYvPZm5vEFkQ_at_mail.gmail.com>

Hi Greg
Oracle's behaviour seems a bit strange. Contrary to metalink entry method_opt 254 buckets does get decent response though not the best. We have more issues to iron out. But at least we seem to have hold of one issue :-(

On Fri, Mar 23, 2012 at 9:33 AM, Greg Rahn <greg_at_structureddata.org> wrote:

> 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 Tue Mar 27 2012 - 15:55:58 CDT