Find the Bind Variable for SQLID of SQL query in Oracle

Fetch the bind variable values from SQL ID -Latest DB SQL executed


RAC

SELECT sql_id, b.LAST_CAPTURED, t.sql_text sql_text,
b.HASH_VALUE, b.name bind_name, b.value_string bind_value
FROM
gv$sql t JOIN gv$sql_bind_capture b using (sql_id)
WHERE b.value_string is not null
AND sql_id='&sqlid';

OR

NON-RAC

SELECT NAME,SQL_ID,POSITION,DATATYPE_STRING,VALUE_STRING
FROM v$sql_bind_capture WHERE sql_id='&sqlid' order by Position;

Check plan for SQL Execution plan from SQLID


-- From Normal SQL if present as cursor in memory
select * from table(dbms_xplan.display_cursor('&sqlid',[child], format => 'TYPICAL +PEEKED_BINDS'));
Example:
select * from table(dbms_xplan.display_cursor('&sqlid',1, format => 'TYPICAL +PEEKED_BINDS'));

--From AWR report
select * from table(dbms_xplan.display_awr('&SQL_ID',NULL,NULL,'ADVANCED'))

Bind Value Check from AWR history tables



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




If you like please follow and comment