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
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
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.
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
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
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.
Pingback: Tweaked bind variable script | Bobby Durrett's DBA Blog