As you know bind variables usage is very important for Oracle performance. If application or user do not use bind variables then Oracle start to create execution plan for same sql for different values. Its mean that our database will have high hard parse values and use our system resource will be increase. Those topic one other topic for performance.

Let us back to our question? How we can find bind values for specific sql

Let us say one sql is running on our database and we want to tunning that sql. But when we checked it we just see tablename.columnname=:1 ,tablename.columnname=:2 etc. on our sql query. We need those field values to can run or solve performance bottleneck for that sql.

Here is the same usefull sql to can find related bind variables:

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

or

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

SELECT ‘var b’||SUBSTR(B.NAME,2,3)||’ ‘||B.DATATYPE_STRING||’;’||CHR(10)|| ”||’exec :b’||SUBSTR(B.NAME,2,3)||’ := ‘||””||B.VALUE_STRING||”’ ;’||CHR(10)||’ ‘
FROM V$SQL_BIND_CAPTURE B,V$SQLAREA A WHERE B.SQL_ID = A.SQL_ID AND B.SQL_ID = ‘&sqlid’;

Reklam