Check the Bind Variable for SQLID of SQL query in Oracle

Check the Bind Variable for SQLID of SQL query in Oracle

Fetch the bind variable values from SQL ID

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
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'))

ChecK from AWR history tables

select sql_text,other_xml
from $dba_hist_sqlplan
where sql_id='&sqlid'
and id=0;
OR
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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.