Miladin Modrakovic’s Blog: Oraclue

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:

From the documentation: v$sql_bind_capture

“One of the bind values used for the bind variable during a past
execution of its associated SQL statement. Bind values are not always
captured for this view. Bind values are displayed by this view only
when the type of the bind variable is simple (this excludes LONG, LOB,
and ADT datatypes) and when the bind variable is used in the WHERE or
HAVING clauses of the SQL statement.”

Does not capture binds for all data types..

Bind capture is also disabled  when STATISTICS_LEVEL = BASIC.

Also there are two underscore parameters that controls binds capture.

SQL> set linesize 125 pagesize 50 newpage 0

SQL> col param_name form a33 head ‘Parameter’
SQL> col param_value form a20 head ‘Value’
SQL> col DESCP format a6
SQL> select  ksppinm param_name,ksppdesc descp, ksppstvl param_value
   from x$ksppi, x$ksppcv
   where x$ksppi.indx = x$ksppcv.indx
    and ksppinm like ‘%bind_capture%’
    order by ksppinm;

Parameter                         DESCP                                                                                                               Value
——————————— —————————————————————-        ——————–
_cursor_bind_capture_area_size    maximum size of the cursor bind capture area                              400
_cursor_bind_capture_interval     interval (in seconds) between two bind capture for a cursor      900

Using alter system I can make capture interval smaller ( keep in mind overhead and performance impact).Always test on dev servers before messing with prod.

SQL> alter system set  “_cursor_bind_capture_interval”=100;