Capturing bind information using v$sql_bind_capture, v$sql_bind_metadata

Starting with 10g view v$sql_bind_capture displays bind variable metadata and values for a SQL cursor .

Suppose to be faster than sql_trace or  event 10046 at level 4

Demo:

SQL> var test number

SQL> exec :test :=10

PL/SQL procedure successfully completed.
SQL> select * from scott.emp where deptno=:test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL>  alter system set  “_cursor_bind_capture_interval”=1;

System altered.
SQL> select * from v$sql_bind_metadata where bind_name = ‘TEST’;

ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
—————- ———- ———- ———- ———- ——————————
00000000FD78FA38          1          2         22          0 TEST
00000000FD34E3F0          1          2         22          0 TEST
00000000FD7CF7F0          1          2         22          0 TEST
SQL>  select sql_text from v$sql where child_address in (’00000000FD34E3F0′,’00000000FD7CF7F0′,’00000000FD78FA38′);

SQL_TEXT
———————————————————————————————————————————————————————–
select * from scott.emp where deptno=:test
SELECT :test test FROM DUAL
BEGIN :test :=10; END;
select sql_text,name,value_string,datatype_string
    from v$sql_bind_capture join v$sql using (hash_value)
    where sql_text like
      ‘%select * from scott.emp where deptno=:test%’;

SQL_TEXT                                           NAME                 VALUE_STRING    DATATYPE_STR WAS LAST_CAPT
————————————————– ——————– ————— ———— — ———
select * from scott.emp where deptno=:test         :TEST                10              NUMBER       YES 09-JAN-09

 Demo  for v$sql_bind_metadata

SQL> alter system flush shared_pool;

System altered.

SQL> var test number
SQL> set linesize 167
SQL> select * from v$sql_bind_metadata where bind_name = ‘TEST’;

no rows selected

Now trick using PRINT

SQL> print test

      TEST
———-
SQL> select * from v$sql_bind_metadata where bind_name = ‘TEST’;

ADDRESS                         POSITION   DATATYPE   MAX_LENGTH  ARRAY_LEN  BIND_NAME
—————-                    ———- ———- ———- ———- ——————————
00000000FD34E3F0              1                                   2         22             0                                             TEST

or directly from x$ table

SQL> select * from x$kksbv where KKSBVNNAM=’TEST’;

ADDR                   INDX    INST_ID KGLHDADR           POSITION   KKSCBNDT   KKSCBNDL   KKSCBNDA KKSBVNNAM
—————- ———- ———- —————- ———- ———- ———- ———- ——————————
00002AAAAC18A558        489          1 00000000FD34E3F0          1          2         22          0 TEST

SQL> select sql_text from v$sql where child_address=’00000000FD34E3F0′;

SQL_TEXT
——————————————-
SELECT :test test FROM DUAL

PRINT  use SELECT FROM DUAL …. 

Oracle recommends use of bind variables for your applications versus non-shared SQL. However too much binds can cause  library latch contention  (bug 3517658).

To check if there are many bind variables being used in system, the following query can be run:

select avg(bind_count) AVG_NUM_BINDS  from
(select sql_id, count(*) bind_count
from v$sql_bind_capture
where child_number = 0
group by sql_id);

But there is catch: Read more of this post