Coskan’s Approach to Oracle

March 2, 2007

How to find value of bind variables used by SQLs

Filed under: CBO, How To — coskan @ 10:02 am

There is one good useful feature in Oracle 10g to catch the value of bind variables without a trace.

This feature is the view V$SQL_BIND_CAPTURE. This view gives you information about the values binded to a specific sql_id with the capture time info. When we use this view with V$SESSION we can capture the values that are binded to the working sqls. This view is populated only when you set STATISTICS_LEVEL parameter to ALL/TYPICAL . Bind capturing is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC.

Here is an example of the usage;
col sid format a4
col username format a5
col sql_hash_value format 99999999
col sqlid format a14
col sql_child_number format 9
col name format a4
col value_string format a8
col last_captured format a9

select s.sid,
s.username,
–sq.sql_text,
s.sql_hash_value,
s.sql_id,
s.sql_child_number,
spc.name,
spc.value_string,
last_captured
from v$sql_bind_capture spc, v$session s,v$sql sq
where s.sql_hash_value = spc.hash_value
and s.sql_address = spc.address
and sq.sql_id=s.sql_id
and spc.was_captured=’YES’
and s.type<>’BACKGROUND’
and s.status=’ACTIVE';

SID USERN SQL_HASH_VALUE SQL_ID SQL_CHILD_NUMBER NAME VALUE_ST LAST_CAPT
———- —– ————– ————- —————- —- ——– ———
########## OKUR ######### 4s6c5w6zr0dub 0 :1 20070301 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :2 20070301 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :3 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :4 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :5 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :6 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :7 NULL 02-MAR-07

resources used:

Watching SQL Execute on Oracle – Part II James Koopmann

Oracle Documentation for 10g R1

About these ads

5 Comments »

  1. Hi,
    Can we write a PL/SQL procedure in Oracle 9i to capture the values of bind variables in the dba_fga_audit_trail table.
    Please note that if the shared_cursors init.ora parameter is set to similar FGA do not capture the exact values of the bind variables.

    Can a code be witten in the handler to capture them ?

    Regards

    Comment by Afzal — July 9, 2007 @ 8:37 am

  2. [...] How to find value of bind variables used by SQLs (10g) Filed under: Uncategorized — heliodias @ 12:54 am http://coskan.wordpress.com/2007/03/02/how-to-find-value-of-bind-variables-used-by-sqls/ [...]

    Pingback by How to find value of bind variables used by SQLs (10g) « Oracle Logbook — August 14, 2007 @ 12:54 am

  3. Hi,
    I believe this v$sql_shared_cursor, captures only the bind values used for the first execution/Parsing.
    I tried by changing the bind variables many time, but able to see only the first value.

    Thanks & Regards,
    Deepak.Sholapurkar

    Comment by Deepak Sholapurkar — June 24, 2010 @ 12:06 pm

  4. Please post full example of bind variable…..

    Comment by Pankaj Sharma — April 20, 2012 @ 9:32 am

  5. vay kardeşim süpersin google ellerinden öp dedi ;)

    Comment by Kemalettin — July 9, 2013 @ 9:09 am


RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers

%d bloggers like this: