This discussion is archived
10 Replies Latest reply: May 17, 2012 11:58 AM by 937783 RSS

sql tuning advisor question on bind values

937783 Newbie
Currently Being Moderated
Hi guys,

I used the sql tuning wizard in Oracle 11g to get recommendation on how to improve performance on a long running query. Here is the recommendation I received:

At least one important bind value was missing for this sql statement. The
accuracy of the advisor's analysis may depend on all important bind values
being supplied.

Could someone please help me understand what it means? For example, what is a bind value, why was it missing, and why are they important?

Thanks!
  • 1. Re: sql tuning advisor question on bind values
    sb92075 Guru
    Currently Being Moderated
    user5535813 wrote:
    Hi guys,

    I used the sql tuning wizard in Oracle 11g to get recommendation on how to improve performance on a long running query. Here is the recommendation I received:

    At least one important bind value was missing for this sql statement. The
    accuracy of the advisor's analysis may depend on all important bind values
    being supplied.

    Could someone please help me understand what it means? For example, what is a bind value, why was it missing, and why are they important?

    Thanks!
    user5535813 wrote:Hi guys,

    I used the sql tuning wizard in Oracle 11g to get recommendation on how to improve performance on a long running query. Here is the recommendation I received:

    At least one important bind value was missing for this sql statement. The
    accuracy of the advisor's analysis may depend on all important bind values
    being supplied.

    Could someone please help me understand what it means? For example, what is a bind value, why was it missing, and why are they important?

    Thanks!
    is SEARCH broken for you

    http://www.oracle.com/pls/db112/search?remark=quick_search&word=bind+variable

    Do you know how to Read The Fine Manual yourself?
  • 2. Re: sql tuning advisor question on bind values
    937783 Newbie
    Currently Being Moderated
    In the recommendation we received, it said the bind VALUE was missing - not bind VARIABLE. It is not clear to me that bind value is the same as bind variable. So I'm inferring from what you said, that they mean the same thing?

    In addition, based on the link you passed me from search, the link takes me to a myriad of different sections. It is not clear from the recommendation which section would most likely apply to my situation. Could you offer some more helpful advice?

    Edited by: user5535813 on May 16, 2012 11:45 AM
  • 3. Re: sql tuning advisor question on bind values
    Dom Brooks Guru
    Currently Being Moderated
    Can you detail how you ran the SQL tuning advisor?

    There are a number of routes in, using statements from AWR, the cursor cache and passing the sql text.
    Depending on how you do it, you may or may not be passing (implicitly or explicitly) a set of values that were actually captured/used for the bind variables.
  • 4. Re: sql tuning advisor question on bind values
    937783 Newbie
    Currently Being Moderated
    Hi Dom,

    Let me know if this snippet helps:

    SYS@WWPTRPW> select DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task_06') script from PC;

    SCRIPT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    GENERAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    Tuning Task Name : sql_tuning_task_06
    Tuning Task Owner : SYS
    Workload Type : Single SQL Statement
    Scope : COMPREHENSIVE
    Time Limit(seconds): 60
    Completion Status : COMPLETED
    Started at : 05/15/2012 11:18:19
    Completed at : 05/15/2012 11:18:37

    -------------------------------------------------------------------------------
  • 5. Re: sql tuning advisor question on bind values
    Dom Brooks Guru
    Currently Being Moderated
    No, not really.
    What you've posted here is how you get the output on the tuning task which is consistent regardless of how you initiate the tuning task - which is what I was interested in.
    Is it just automatic tuning tasks then (given that the task owner is SYS)?
  • 6. Re: sql tuning advisor question on bind values
    937783 Newbie
    Currently Being Moderated
    I think I understand now. This was not an automated task. What we did was take the sqlID from a long running top query and then we ran sql tuning wizard manually from the command line using the sqlID to reference the query we wanted to analyze. Let me know if this helps.
  • 7. Re: sql tuning advisor question on bind values
    937783 Newbie
    Currently Being Moderated
    Hi Dom,

    It is not an automated query. Its coming from the sqlID of an identified long running query from an AWR report. Let me know if this would cause this false recommendation on the tuning report.

    Thanks!
  • 8. Re: sql tuning advisor question on bind values
    sb92075 Guru
    Currently Being Moderated
    dbpadawan wrote:
    Hi Dom,

    It is not an automated query. Its coming from the sqlID of an identified long running query from an AWR report. Let me know if this would cause this false recommendation on the tuning report.

    Thanks!
    I suggest that you file a Bug Report; since nobody here can change your reality.
  • 9. Re: sql tuning advisor question on bind values
    Dom Brooks Guru
    Currently Being Moderated
    I've seen this message maybe once before but I've never investigated it - I don't tend to run the SQL tuning advisor.

    The best thing you could do is investigate the circumstances yourself, maybe trace the tuning advisor and see where it's getting its data from internally.

    As mentioned, where it gets its data from depends on how you populate the tuning advisor - from cursor cache, awr, etc.

    If it's from AWR as you mentioned, have a look at DBA_HIST_SQLSTAT.BIND_DATA for example? Or maybe DBA_HIST_SQLBIND? Etc.

    You can check what the advisor probably pulls out of AWR using something like this:
    select * 
    from   table(dbms_Sqltune.select_workload_repository
                 (begin_snap   => 6441, 
                  end_snap     => 6443, 
                  basic_filter => 'sql_id = ''gr5tqfnz07sxk'''));
    If the bind_list is empty then you know what it's complaining about.

    I just tried it with an example sql it that I can see has bind data in DBA_HIST_SQLSTAT and DBA_HIST_SQLBIND and the BIND_LIST in the SQLSET returned by the SQL set above is null so that raises some questions ( I might be doing something wrong but as mentioned, it's not really something I tend to use).

    The BIND_DATA raw is populated for me (although the bind_list collection isn't hence why original comment above).


    If you know a representative set of binds, you can kick off your own tuning task manually and supply your own set of binds - see DBMS_SQLTUNE.CREATE_TUNING_TASK.

    You can also extract and inspect the RAW BIND_DATA into a set of binds using DBMS_SQLTUNE.EXTRACT_BINDS.

    e.g. to inspect:
    select x.*
    from   v$sql s
    ,      table(dbms_sqltune.extract_binds(s.bind_data)) x 
    where  sql_id = '<sql_id>';
    Edited by: Dom Brooks on May 17, 2012 5:59 PM
  • 10. Re: sql tuning advisor question on bind values
    937783 Newbie
    Currently Being Moderated
    Hi Dom,

    Thanks for these tips. This is helpful. I'll go check this out.

    -dbpadawan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points