FAQ
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.

Any input would be appreciated.

TIA
GovindanK

Search Discussions

  • Phillip Jones at Mar 23, 2012 at 9:19 am
    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 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
  • Jorgensen, Finn at Mar 23, 2012 at 1:44 pm
    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@freelists.org On Behalf Of Phillip Jones
    Sent: Friday, March 23, 2012 5:19 AM
    To: gkatteri@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 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
  • Greg Rahn at Mar 23, 2012 at 4:34 pm
    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 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
  • GovindanK at Mar 23, 2012 at 11:36 pm
    I deleted the histogram and it worked. So i went ahead and deleted the
    statistics, gathered stats , checked the histogram / buckets; deleted the
    histogram for the columns of the particular problematic table , verified
    and it is now working fine. Of interest is the Metalink article :
    1031826.6 ; The concepts remain the same. Don't worry about the version. I
    wish i had seen it earlier.
  • GovindanK at Mar 27, 2012 at 9:49 pm
    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 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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 23, '12 at 5:54a
activeMar 27, '12 at 9:49p
posts6
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase