[20130410]v$sql_bind_capture和隐含参数_bind_capture_area_size.txt

[20130410]v$sql_bind_capture和隐含参数_bind_capture_area_size.txt

今天想优化一天sql语句，使用视图v$sql_bind_capture想取出里面的绑定变量。
SELECT NAME, was_captured, POSITION, datatype_string, last_captured, value_string
  FROM v$sql_bind_capture
 WHERE sql_id = '&sql_id' and position>=161;

发现position>=162以后的变量都没有获得。

NAME                 WAS   POSITION DATATYPE_STRING                LAST_CAPTURED       VALUE_STRING

-------------------- --- ---------- ------------------------------ ------------------- --------------------

:SYS_B_160           YES        161 VARCHAR2(32)                   2013-04-10 10:33:19 108

:SYS_B_161           NO         162 VARCHAR2(32)

:SYS_B_162           NO         163 VARCHAR2(32)

:SYS_B_163           NO         164 VARCHAR2(32)

:SYS_B_164           NO         165 VARCHAR2(32)

...

主要是里面的参数太多了。另外还受隐含参数_bind_capture_area_size的影响：

SQL> host cat /home/oracle11g/sqllaji/hide.sql

col name format a40

col description format a66

col session_value format a22

col default_value format a22

col system_value format a22

select

   a.ksppinm  name,

   a.ksppdesc DESCRIPTION,

   b.ksppstdf DEFAULT_VALUE,

   b.ksppstvl SESSION_VALUE,

   c.ksppstvl SYSTEM_VALUE

from x$ksppi a, x$ksppcv b, x$ksppsv c

where a.indx = b.indx

 and a.indx = c.indx

 and lower(a.ksppinm) like lower('%&1%')

order by 1;

SQL> set veri off

SQL> @hide _bind_capture_area_size

NAME                            DESCRIPTION                                   DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE

------------------------------- --------------------------------------------- -------------- -------------- --------------

_cursor_bind_capture_area_size  maximum size of the cursor bind capture area  TRUE            400           400

--_bind_capture_area_size=400,这样超出的部分无法抓取到。


自己在11G上做一个测试：

1.测试环境建立:

SQL> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> create table t1 as select rownum id,lpad('a',2000,'a') name from dual connect by level

SQL> variable x varchar2(2000)

SQL> exec :x := lpad('b',401,'b')

PL/SQL procedure successfully completed.

SQL> select * from t1 where name=:x;

no rows selected

--正常情况下第一次执行如果没有超出限制，绑定变量都会捕获。

SQL> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  2gcbcptdqh47a, child number 0

-------------------------------------

select * from t1 where name=:x

Plan hash value: 3617692013

--------------------------------------------------------

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

--------------------------------------------------------

|   0 | SELECT STATEMENT  |      |        |    12 (100)|

|*  1 |  TABLE ACCESS FULL| T1   |      1 |    12   (0)|

--------------------------------------------------------

SQL> column value_string format a20

SQL> SELECT NAME, was_captured, POSITION, datatype_string, last_captured, value_string  FROM v$sql_bind_capture WHERE sql_id = '&sql_id';

Enter value for sql_id: 2gcbcptdqh47a

NAME                 WAS   POSITION DATATYPE_STRING                LAST_CAPTURED       VALUE_STRING

-------------------- --- ---------- ------------------------------ ------------------- --------------------

:X                   NO           1 VARCHAR2(2000)

--可以发现并没有捕获绑定变量的值。

2.修改参数看看：
--要做一次flush，不然可能无法capture。

SQL> alter system flush shared_pool;

System altered.

SQL> alter system  set "_cursor_bind_capture_area_size"=1000 scope=memory;

System altered.

SQL> select * from t1 where name=:x;

no rows selected

SQL> column value_string format a50

SQL> SELECT NAME, was_captured, POSITION, datatype_string, last_captured, value_string  FROM v$sql_bind_capture WHERE sql_id = '&sql_id';

Enter value for sql_id: 2gcbcptdqh47a

NAME                 WAS   POSITION DATATYPE_STRING                LAST_CAPTURED       VALUE_STRING

-------------------- --- ---------- ------------------------------ ------------------- --------------------------------------------------

:X                   YES          1 VARCHAR2(2000)                 2013-04-10 14:52:32 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

                                                                                       bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

                                                                                       bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

                                                                                       bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

                                                                                       bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

                                                                                       bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

                                                                                       bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

                                                                                       bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

                                                                                       b

--可以发现增大_cursor_bind_capture_area_size=1000,可以捕获绑定变量长度等于401的变量。