LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon