How to find bind variable name and value of a SQL Id

Scope:

Some times it is required by a DBA to find explain plan of a query which might be responsible for bad performance of your database.


But when we try to get explain plan it requires the value for bind variables used in the query. You can find the bind variable information for a SQL ID of that query. Now lets see how.

Solution:

In Oracle database a dynamic view is available 'V$SQL_BIND_CAPTURE' which has information of the bind variable name and its string values in 'name' and 'VALUE_STRING' columns for the relative SQL Id.

select sql_id,name, value_string from v$SQL_BIND_CAPTURE;

For RAC database, gv$SQL_BIND_CAPTURE view can also be used.

select inst_id, sql_id,name, value_string from gv$SQL_BIND_CAPTURE;

The above case is defined for the current sessions, but what if the SQL ID is from the history?

The relative history view for SQL_BIND_CAPTURE is DBA_HIST_SQLBIND which can be queried in same way as SQL_BIND_CAPTURE. 
DBA can access to find the values of bind variables in a defined snap id.

select instance_number, snap_id, sql_id, name,string_value 
from DBA_HIST_SQLBIND  where SQL_ID='&SQLID' 
-- and snap_id = '&SNAP_ID'                           -- For a specific snap id.
--and snap_id between '&BEGIN_SNAP' and '&END_SNAP'   -- For a range of snap id.
;

In DBA_HIST_SQLBIND time information is not available. If the time interval range is required for a defined SQL ID, another view DBA_HIST_SNAPSHOT can be joined to add time range.

select sh.instance_number, sh.snap_id, sq.sql_id, sq.name,sq.string_value 
from DBA_HIST_SQLBIND  sql , DBA_HIST_SNAPSHOT sh 
where SQL_ID='&SQLID' 
sq.snap_id=sh.snap_id
and sq.instance_number=sh.instance_number
-- and sq.instance_number='&INST_NO'     -- For info from a specific instance
-- and snap_id = '&SNAP_ID'                           -- For a specific snap id.
--and snap_id between '&BEGIN_SNAP' and '&END_SNAP'   -- For a range of snap id.
and sh.begin_interval_time between to_date('12/12/2012 10:00,'DD/MM/YYYY HH24:MI') and to_date('12/12/2012 12:00,'DD/MM/YYYY HH24:MI')
;