Finding bind variable values using DBA_HIST_SQLBIND

Whenever I need to test a query that has bind variables I usually go to the development team to ask them what typical values are or what the values were the last time the query ran.  I’m pretty sure that in the past when I went looking for a DBA_HIST view that held bind variables that I came up empty.  Today I’m working on tuning a query with a bind variable and I tried to find the value of the bind variable last Sunday using the DBA_HIST_SQLBIND view.  Strangely enough it had exactly what I wanted so I’m not sure if there are cases where this doesn’t capture the variables and cases where it does, but it worked for me today so it may be useful to others in certain cases.

I had the sql_id of the query from an AWR report spanning the period of high CPU usage on Sunday: 40wpuup08vws6.  I ran this query to get the bind variable for all executions of this sql_id.

select 
sn.END_INTERVAL_TIME,
sb.NAME,
sb.VALUE_STRING 
from 
DBA_HIST_SQLBIND sb,
DBA_HIST_SNAPSHOT sn
where 
sb.sql_id='40wpuup08vws6' and
sb.WAS_CAPTURED='YES' and
sn.snap_id=sb.snap_id
order by 
sb.snap_id,
sb.NAME;

It produced this output for the bind variable B1.

END_INTERVAL_TIME          NAM VALUE_STRING
-------------------------- --- -----------------
03-FEB-13 02.00.32.733 AM  :B1 02/02/13 00:00:00
03-FEB-13 03.00.36.316 AM  :B1 02/02/13 00:00:00
10-FEB-13 02.00.29.975 AM  :B1 02/09/13 00:00:00
10-FEB-13 03.00.23.292 AM  :B1 02/09/13 00:00:00
17-FEB-13 02.00.36.688 AM  :B1 02/16/13 00:00:00
17-FEB-13 03.00.06.374 AM  :B1 02/16/13 00:00:00
24-FEB-13 01.00.33.691 AM  :B1 02/23/13 00:00:00
24-FEB-13 02.00.20.269 AM  :B1 02/23/13 00:00:00
24-FEB-13 03.00.16.811 AM  :B1 02/23/13 00:00:00
03-MAR-13 02.00.17.974 AM  :B1 03/02/13 00:00:00
03-MAR-13 03.00.33.340 AM  :B1 03/02/13 00:00:00
10-MAR-13 10.00.10.356 PM  :B1 03/09/13 00:00:00
10-MAR-13 11.00.43.467 PM  :B1 03/09/13 00:00:00
11-MAR-13 12.00.12.898 AM  :B1 03/09/13 00:00:00

So, you can see what value this date type bind variable B1 contained each weekend that the query ran.  The query runs for multiple hours so that is why it spanned multiple AWR snapshot intervals.

Here is a zip of my test script and its log: zip.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

6 Responses to Finding bind variable values using DBA_HIST_SQLBIND

  1. goutham says:

    Hi Sir
    the topics you cover regarding performance tuning are very interesting.I want some information regarding monitoring database , application and os level.Is there any tools oracle is providing to monitor complete stack.from front end i want to monitor the memory consumed at particular time in the form of report.

    thank you

  2. Bobby says:

    Thanks for your comment. In our case we have different tools that monitor each part of the stack such as HP-UX tools for the Unix level monitoring on the database server. You might look at the new Oracle Enterprise Manager (OEM) 12c. A coworker of mine has installed it and is looking at it and apparently it is completely rewritten from the previous version and is supposed to monitor various parts of the stack.

    • goutham says:

      hi Sir
      thanks for your reply.In terms of performance tuning from where we can start in order to get good command in oracle performance issues.If you could give some suggestions regarding this.

      thank you
      K.Goutham

  3. Bobby says:

    If you want to improve your Oracle performance tuning skills I’d suggest three sources: Oracle manuals, Oracle’s support web site, and books/web sites by Oracle experts.

    Manuals: The most important manuals for tuning are the Concepts and Performance Tuning Guide manuals.

    Oracle support: There are lots of great articles on tuning on support.oracle.com. It isn’t just a place to go when you hit a bug. One example: Limitations of the Oracle Cost Based Optimizer [ID 212809.1]

    Experts: I’ve found books, web sites, talks by these people to be helpful: Craig Shallahamer, Don Burleson, Cary Millsap, Jonathan Lewis, Tom Kyte.

    – Bobby

  4. Deepak Sharma says:

    I am running into a similar issue. Searching for one of the top CPU SQLs, I went back as far as 7 days to get its bind data, but the ‘value_string’ shows null.

  5. Pingback: Tweaked bind variable script | Bobby Durrett's DBA Blog

Leave a Reply to Bobby Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.