[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<=100 ;

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的變數。