[20150812]关于抓取绑定变量.txt
--通过视图v$sql_bind_capture以及DBA_HIST_SQLBIND可以抓取到sql语句的绑定变量。受到一些参数的限制，曾经写过一篇：
[20130410]v$sql_bind_capture和隐含参数_bind_capture_area_size.txt
http://blog.itpub.net/267265/viewspace-758175/
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> @hide bind_capture
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
_cursor_bind_capture_area_size maximum size of the cursor bind capture area TRUE 400 400
_cursor_bind_capture_interval interval (in seconds) between two bind capture for a cursor TRUE 900 900
cursor_bind_capture_destination Allowed destination for captured bind variables TRUE memory+disk memory+disk
--从参数可以发现_cursor_bind_capture_area_size表示抓取变量区域大小，如果你绑定变量很多，或者占用空间很大，400字节远远不足。可以参考我以前的链接。
-- _cursor_bind_capture_interval 很明显是时间间隔，设置太小估计对信息有一定影响，太大可能遗漏一些重要有问题的参数。
--不过第1次执行生成新的光标，oracle一定会抓取的。可以通过例子来验证：
1.建立测试环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select rownum id , cast('test' as varchar2(20)) name from dual connect by level<=20;
Table created.
--分析表。
2.测试：
SCOTT@test> variable x number ;
SCOTT@test> exec :x := 1;
PL/SQL procedure successfully completed.
SCOTT@test> select * from t where id=:x;
ID NAME
---------- ----------------------------------------
1 test
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3yxwagyspybax, child number 0
-------------------------------------
select * from t where id=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1
Predicate Information (identified by operation id):
1 - filter("ID"=:X)
$ cat bind_cap.sql
set verify off
column value_string format a50
column datatype_string format a15
break on sql_id on child_number skip 1
select replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1;
SELECT sql_id,
child_number,
was_captured,
name,
position,
max_length,
last_captured,
datatype_string,
DECODE (
datatype_string,
'DATE', TO_CHAR (TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss'),
'yyyy/mm/dd hh24:mi:ss'),
value_string)
value_string
FROM v$sql_bind_capture
WHERE sql_id = '&1' and was_captured='YES'
order by child_number,was_captured,position;
break on sql_id on child_number skip 0
SCOTT@test> @bind_cap 3yxwagyspybax
C200
----------------------------
select * from t where id=:x
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- ----- ---------- ---------- ------------------- --------------- ---------------
3yxwagyspybax 0 YES :X 1 22 2015-08-12 09:35:25 NUMBER 1
--很明显，我第一次执行一定会抓取。
3.如果我修改某个参数一定会生成新的光标，这样应该也会抓取变量：
SCOTT@test> alter session set optimizer_index_caching=10;
Session altered.
SCOTT@test> exec :x := 42;
PL/SQL procedure successfully completed.
SCOTT@test> select * from t where id=:x;
no rows selected
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3yxwagyspybax, child number 1
-------------------------------------
select * from t where id=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
SCOTT@test> @bind_cap 3yxwagyspybax
C200
---------------------------
select * from t where id=:x
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- ------ ---------- ---------- ------------------- --------------- --------------
3yxwagyspybax 0 YES :X 1 22 2015-08-12 09:35:25 NUMBER 1
1 YES :X 1 22 2015-08-12 09:41:25 NUMBER 42
--可以发现生成新的子关闭，oracle也会抓取。
4.其它参数:
SCOTT@test> @hide _optim_peek_user_binds
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
----------------------- ----------------------------- ---------------------- ---------------------- ----------------------
_optim_peek_user_binds enable peeking of user binds TRUE TRUE TRUE
--退出等上15分钟，也就是900秒。
SCOTT@test> variable x number ;
SCOTT@test> exec :x := 34;
PL/SQL procedure successfully completed.
SCOTT@test> select sysdate from dual;
SYSDATE
-------------------
2015-08-12 09:56:25
--已经过了900秒。
SCOTT@test> select * from t where id=:x;
no rows selected
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 3yxwagyspybax, child number 0
-------------------------------------
select * from t where id=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
SCOTT@test> @bind_cap 3yxwagyspybax
C200
---------------------------
select * from t where id=:x
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ------------------
3yxwagyspybax 0 YES :X 1 22 2015-08-12 09:56:46 NUMBER 34
1 YES :X 1 22 2015-08-12 09:41:25 NUMBER 42
--可以发现1个现象：
--使用dbms_xplan.display_cursor查看执行计划的绑定变量还是第一次执行的，并没有变化。
--而查询v$sql_bind_capture时已经发生了变化。
--实际上dbms_xplan.display_cursor看到的来源于v$sql_plan:
SCOTT@test> select child_number,other_xml from v$sql_plan where sql_id='3yxwagyspybax' order by 1;
CHILD_NUMBER OTHER_XML
------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0
<other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![CDATA["SCOTT"]]></info><info type="plan_hash">1601196873</info><info type="plan_hash_2">2498539100</info><peeked_binds><b
ind nam=":X" pos="1" dty="2" pre="0" scl="0" mxl="22">c102</bind></peeked_binds><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]
></hint><hint><![CDATA[DB_VERSION('11.2.0.3')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "T"@"SEL$1")]]></hint></outline_data
></other_xml>
1
<other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![CDATA["SCOTT"]]></info><info type="plan_hash">1601196873</info><info type="plan_hash_2">2498539100</info><peeked_binds><b
ind nam=":X" pos="1" dty="2" pre="0" scl="0" mxl="22">c12b</bind></peeked_binds><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]
></hint><hint><![CDATA[DB_VERSION('11.2.0.3')]]></hint><hint><![CDATA[OPT_PARAM('optimizer_index_caching' 10)]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><
hint><![CDATA[FULL(@"SEL$1" "T"@"SEL$1")]]></hint></outline_data></other_xml>
--c102 就是数字1.
SCOTT@test> select dump(1,16),dump(42,16) from dual ;
DUMP(1,16) DUMP(42,16)
----------------- ------------------
Typ=2 Len=2: c1,2 Typ=2 Len=2: c1,2b
--附：我的dpc.sql脚本：：
$ cat dpc.sql
set verify off
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));
prompt
prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
prompt
