Retrieving Query with its Bind Variable Value

To increase query performance, using bind variable is an important factor. In some cases we need to execute a SQL Query for multiple tomes and changes exist only in literal value, in such case experts suggest for using bind varibale; and results is significant on perspective of query performance.

For the case of retrieving query from the database,the value of the bind variable is not present in the query text. But we may need those values when we are working on that SQL query specially when we try to tune that query.

Here are two query that help you to find the valu of bind variable. The only thing you needed is the SQL ID:

1. If your query was run within 30 minutes:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING 

FROM v$sql_bind_capture WHERE sql_id='';

2. If your query was run more than 30 minutes ago. For this you also need a snapshot Id. By default oracle store snapshots of last 7 days:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING

FROM DBA_HIST_SQLBIND WHERE SQL_ID='' and SNAP_ID='';

Note: v$sql_bind_capture and DBA_HIST_SQLBIND stors only those bind values 

which are given in HWERE clause and they do not store timstamp,lob,clob type value