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.