Tuning an Application Statement with Bind Variables

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Daniel Roy
    #1

    Tuning an Application Statement with Bind Variables

    Hi gurus,
    I just started to look at a very slow-running SQL statement
    generated by an application (Siebel). I spooled the SQL from the
    application, replaced the bind variables by their values, and tuned
    from the Query Analyser. But after awhile, I realized that the
    statement using bind variables and the same statement using the values
    instead of the bind variables often have completely different
    execution plans! Is that normal? Can someone tell me how the SQL
    Server treats bind variables. Don't worry about being too technical,
    I'm an Oracle DBA/developer.

    Thanx

    Daniel
    Tags: None
    • lucjanc
      #2
      Re: Tuning an Application Statement with Bind Variables

      Hi,
      when optimazer looks at the query with bind variables.. in simple terms.. it
      'estimates' value of the variable, puts this into query, and then based on
      different combinations vs. cost , finds the best query plan. This is all
      good under assumption that data is fairly evenly distributed, so are your
      variable values when they are being used in your query. Problems appear when
      , distribution is havily scewed, data not indexed, or wrong/missing index on
      your table(s).

      To clean it up, you need to do some logging in your profiler, grab sql, try
      to figure out what the query plan really should be, if you can modify sql
      great if not, or play with index. Don't give up ... it has to work.

      Lucjan



      "Daniel Roy" <danielroy10jun k@hotmail.com> wrote in message
      news:3722db.040 1151631.308ffa7 d@posting.googl e.com...[color=blue]
      > Hi gurus,
      > I just started to look at a very slow-running SQL statement
      > generated by an application (Siebel). I spooled the SQL from the
      > application, replaced the bind variables by their values, and tuned
      > from the Query Analyser. But after awhile, I realized that the
      > statement using bind variables and the same statement using the values
      > instead of the bind variables often have completely different
      > execution plans! Is that normal? Can someone tell me how the SQL
      > Server treats bind variables. Don't worry about being too technical,
      > I'm an Oracle DBA/developer.
      >
      > Thanx
      >
      > Daniel[/color]


        Comment

        Previous template Next
        Working...