dba_hist_sqlbind tips

                          Oracle Database Tips by Donald BurlesonApril  18, 2015

Question:  How do I use the dba_hist_sqlbind table to get historical data values for a SQL statement? I know that the dba_hist_sqlbind table keeps a running tally of bind variable values but I want to get a script that will show me the actual bind variables that are used by a sigen SQL ID. What is the script to display bind variables for SQL?

Answer:  Oracle's AWR keeps track of bind variables in SQL statements in the dba_hist_sqlbind view. Here is a script that will display the values of all bind variables for a given SQL statement. Note that dba_hist_sqlbind is valuable for system testing when you want to re-create a SQL statement along with its bind variables.

Also see my notes on historical bind variable usage"

 

select
   sn.end_interval_time,
   sb.name,
   sb.value_string
from
   dba_hist_sqlbind sb,
   dba_hist_snapshot sn
where
   sb.sql_id='jkbkjgctbjjkbk' -- Your SQL ID here
and
sb.was_captured='YES'
and
sn.snap_id=sb.snap_id
order by
   sb.snap_id,
   sb.name;

 

Get Complete Oracle Tuning Details  The landmark book "Oracle Tuning: The Definitive Reference Third Edition" has been updated with over 800 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle performance and you can buy it for 40% off directly from the publishers.