Find the Bind Variable for SQLID of SQL query in Oracle

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