[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以后的变量都没有获得。
主要是里面的参数太多了。另外还受隐含参数_bind_capture_area_size的影响:
--_bind_capture_area_size=400,这样超出的部分无法抓取到。
自己在11G上做一个测试:
1.测试环境建立:
--正常情况下第一次执行如果没有超出限制,绑定变量都会捕获。
--可以发现并没有捕获绑定变量的值。
2.修改参数看看:
--要做一次flush,不然可能无法capture。
--可以发现增大_cursor_bind_capture_area_size=1000,可以捕获绑定变量长度等于401的变量。
今天想优化一天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的变量。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-758175/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-758175/