Oracle: bind variable values and execution plan from a SQL_ID
Queries to retrieve bind variable values and the plan for a SQL_ID, from memory or from AWR.
Given a SQL_ID, you can retrieve the bind variable values captured by Oracle and the execution plan, either from the cursor cache or from the AWR history. &sqlid is a SQL*Plus substitution variable.
Bind variable values from memory
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, more simply:
SELECT NAME,SQL_ID,POSITION,DATATYPE_STRING,VALUE_STRING
FROM v$sql_bind_capture WHERE sql_id='&sqlid' order by Position;
Execution plan from a SQL_ID
If the statement is still in the cursor cache (the child number is optional):
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 the AWR repository:
select * from table(dbms_xplan.display_awr('&SQL_ID',NULL,NULL,'ADVANCED'));
Bind variables from the AWR history
The first query returns the plan data, including the peeked binds in other_xml:
select sql_text,other_xml
from dba_hist_sqlplan
where sql_id='&sqlid'
and id=0;
The second one lists the captured values per snapshot:
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;
Notes
The AWR views require the Oracle Diagnostics Pack licence.