我们可以通过v$sql_bind_capture 捕获到 bind values 这篇文章主要阐述v$sql_bind_capture与v$sql_plan的一些联系
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 28 14:07:13 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 889193656 bytes
Database Buffers 3372220416 bytes
Redo Buffers 12132352 bytes
Database mounted.
Database opened.
SQL> drop table liu;
Table dropped.
SQL> conn liu/liu
Connected.
SQL> select * from liu;
ID1 ID2 ID3
---------- ---------- ----------
13 14 15
7 8 9
10 11 12
1 2 3
4 5 6
SQL> variable N1 number;
variable N2 number;
variable N3 number;
begin
:N1 := 1;
:N2 := 2;
:N3 := 3;
END;
/
print N1;
print N2;
print N3; SQL> SQL> SQL> 2 3 4 5 6
PL/SQL procedure successfully completed.
SQL>
N1
----------
1
SQL>
N2
----------
2
SQL>
N3
----------
3
SQL> SELECT * FROM LIU WHERE ID1= :N1 AND ID2= :N2 AND ID3= :N3;
ID1 ID2 ID3
---------- ---------- ----------
1 2 3
SQL> @find_sql
Enter value for sql_text: %FROM LIU WHERE ID1%
old 6: where upper(sql_text) like upper(nvl('&sql_text',sql_text))
new 6: where upper(sql_text) like upper(nvl('%FROM LIU WHERE ID1%',sql_text))
Enter value for sql_id:
old 8: and sql_id like nvl('&sql_id',sql_id)
new 8: and sql_id like nvl('',sql_id)
SQL_ID CHILD_NUMBER PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
--------------------------------------- ------------ ---------- ---------- ---------- ---------- --------------------------------------------------------------------------------
2y32qkr40b82a 0 3882242470 1 .012357 47 SELECT * FROM LIU WHERE ID1= :N1 AND ID2= :N2 AND ID3= :N3
SQL> select * from table(dbms_xplan.display_cursor('2y32qkr40b82a',null,'typical +peeked_binds'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2y32qkr40b82a, child number 0
-------------------------------------
SELECT * FROM LIU WHERE ID1= :N1 AND ID2= :N2 AND ID3= :N3
Plan hash value: 3882242470
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| LIU | 1 | 39 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :N1 (NUMBER): 1
2 - :N2 (NUMBER): 2
3 - :N3 (NUMBER): 3
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter(("ID1"=:N1 AND "ID2"=:N2 AND "ID3"=:N3))
Note
-----
- dynamic sampling used for this statement (level=2)
29 rows selected.
SQL> select value_string from v$sql_bind_capture where sql_id='2y32qkr40b82a'
2 ;
VALUE_STRING
--------------------------------------------------------------------------------
1
2
3继续改变bind values ：
SQL> variable N1 number;
variable N2 number;
variable N3 number;
begin
:N1 := 4;
:N2 := 5;
:N3 := 6;
END;
/
print N1;
print N2;
print N3;SQL> SQL> SQL> 2 3 4 5 6
PL/SQL procedure successfully completed.
SQL>
N1
----------
4
SQL>
N2
----------
5
SQL>
N3
----------
6
SQL> SELECT * FROM LIU WHERE ID1= :N1 AND ID2= :N2 AND ID3= :N3;
ID1 ID2 ID3
---------- ---------- ----------
4 5 6
SQL> select * from table(dbms_xplan.display_cursor('2y32qkr40b82a',null,'typical +peeked_binds'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2y32qkr40b82a, child number 0
-------------------------------------
SELECT * FROM LIU WHERE ID1= :N1 AND ID2= :N2 AND ID3= :N3
Plan hash value: 3882242470
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| LIU | 1 | 39 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :N1 (NUMBER): 1
2 - :N2 (NUMBER): 2
3 - :N3 (NUMBER): 3
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter(("ID1"=:N1 AND "ID2"=:N2 AND "ID3"=:N3))
Note
-----
- dynamic sampling used for this statement (level=2)
29 rows selected.
SQL> select value_string from v$sql_bind_capture where sql_id='2y32qkr40b82a' ;
VALUE_STRING
--------------------------------------------------------------------------------
1
2
3没有变化，继续强制产生一个child cursor
SQL> insert into liu values (128000,128000,128000);
1 row created.
SQL> variable N1 number;
variable N2 number;
variable N3 number;
begin
:N1 := 128000;
:N2 := 128000;
:N3 := 128000;
END;
/SQL> SQL> SQL> 2 3 4 5 6
PL/SQL procedure successfully completed.
SQL> alter session set optimizer_mode=first_rows_100;
Session altered.
SQL> SELECT * FROM LIU WHERE ID1= :N1 AND ID2= :N2 AND ID3= :N3;
ID1 ID2 ID3
---------- ---------- ----------
128000 128000 128000
Enter value for sql_text: %FROM LIU WHERE ID1%
old 6: where upper(sql_text) like upper(nvl('&sql_text',sql_text))
new 6: where upper(sql_text) like upper(nvl('%FROM LIU WHERE ID1%',sql_text))
Enter value for sql_id:
old 8: and sql_id like nvl('&sql_id',sql_id)
new 8: and sql_id like nvl('',sql_id)
SQL_ID CHILD_NUMBER PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
--------------------------------------- ------------ ---------- ---------- ---------- ---------- --------------------------------------------------------------------------------
2y32qkr40b82a 0 3882242470 1 .002613 23 SELECT * FROM LIU WHERE ID1= :N1 AND ID2= :N2 AND ID3= :N3
2y32qkr40b82a 1 3882242470 2 .006787 65.5 SELECT * FROM LIU WHERE ID1= :N1 AND ID2= :N2 AND ID3= :N3
SQL> select * from table(dbms_xplan.display_cursor('2y32qkr40b82a',null,'typical +peeked_binds'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2y32qkr40b82a, child number 0
-------------------------------------
SELECT * FROM LIU WHERE ID1= :N1 AND ID2= :N2 AND ID3= :N3
Plan hash value: 3882242470
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| LIU | 1 | 39 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :N1 (NUMBER): 128000
2 - :N2 (NUMBER): 128000
3 - :N3 (NUMBER): 128000
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter(("ID1"=:N1 AND "ID2"=:N2 AND "ID3"=:N3))
Note
-----
- dynamic sampling used for this statement (level=2)
SQL_ID 2y32qkr40b82a, child number 1
-------------------------------------
SELECT * FROM LIU WHERE ID1= :N1 AND ID2= :N2 AND ID3= :N3
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3882242470
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| LIU | 1 | 39 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - :N1 (NUMBER): 1
2 - :N2 (NUMBER): 2
3 - :N3 (NUMBER): 3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ID1"=:N1 AND "ID2"=:N2 AND "ID3"=:N3))
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
58 rows selected.
SQL> select value_string from v$sql_bind_capture where sql_id='2y32qkr40b82a'
VALUE_STRING
--------------------------------------------------------------------------------
1
2
3
128000
128000
128000
6 rows selected.在产生子游标的情况下同时捕获到，我们来看下other_xml的值
select other_xml from v$sql_plan where sql_id=’2y32qkr40b82a’ and other_xml is not null
<other_xml><info type="db_version">11.2.0.3</info>
<info type="parse_schema"><![CDATA["LIU"]]></info><info type="dynamic_sampling">2</info>
<info type="plan_hash">3882242470</info><info type="plan_hash_2">281948136</info>
<peeked_binds><bind nam=":N1" pos="1" dty="2" pre="0" scl="0" mxl="22">c30d51</bind>
<bind nam=":N2" pos="2" dty="2" pre="0" scl="0" mxl="22">c30d51</bind>
<bind nam=":N3" pos="3" dty="2" pre="0" scl="0" mxl="22">c30d51</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[FIRST_ROWS(100)]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
<hint><![CDATA[FULL(@"SEL$1" "LIU"@"SEL$1")]]></hint>
</outline_data></other_xml>SQL> select display_raw('c30d51','NUMBER') from dual;
DISPLAY_RAW('C30D51','NUMBER')
--------------------------------------------------------------------------------
128000确实在other_xml中捕获到了这个值,这里要注意如果关闭”_optim_peek_user_binds” other_xml将无法捕获这些values
继续 我们开启两个窗口做loop循环：
session 1:
SQL> declare
N1 number;
N2 number;
N3 number;
v_count number;
begin
for i in 1..1000000000 loop
N1 := 4;
N2 := 5;
N3 := 6;
SELECT count(*) into v_count FROM LIU WHERE ID1= N1 AND ID2= N2 AND ID3= N3;
end loop;
end; 2 3 4 5 6 7 8 9 10 11 12 13
14 /
session 2:
SQL> declare
N1 number;
N2 number;
N3 number;
v_count number;
begin
for i in 1..1000000000 loop
N1 := 7;
N2 := 8;
N3 := 9;
SELECT count(*) into v_count FROM LIU WHERE ID1= N1 AND ID2= N2 AND ID3= N3;
end loop;
end; 2 3 4 5 6 7 8 9 10 11 12 13
14 /
session 3
SQL> @find_sql
Enter value for sql_text: %LIU%
old 6: where upper(sql_text) like upper(nvl('&sql_text',sql_text))
new 6: where upper(sql_text) like upper(nvl('%LIU%',sql_text))
Enter value for sql_id:
old 8: and sql_id like nvl('&sql_id',sql_id)
new 8: and sql_id like nvl('',sql_id)
SQL_ID CHILD_NUMBER PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
--------------------------------------- ------------ ---------- ---------- ---------- ---------- --------------------------------------------------------------------------------
33xdcqmh004pw 0 4104423748 889189 .000024552 7.00001574 SELECT COUNT(*) FROM LIU WHERE ID1= :B3 AND ID2= :B2 AND ID3= :B1
3s3f5r30shvz4 0 4104423748 1 .003918 7 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB
) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB)
NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAU
SE NO_PARALLEL("LIU") FULL("LIU") NO_PARALLEL_INDEX("LIU") */ 1 AS C1, CASE WHEN
"LIU"."ID1"=:B1 AND "LIU"."ID2"=:B2 AND "LIU"."ID3"=:B3 THEN 1 ELSE 0 END AS C2
FROM "LIU"."LIU" "LIU") SAMPLESUB
g8h776pgzzxym 0 0 0 41.31562 6161555 declare N1 number; N2 number; N3 number; v_count number; begin for i in 1..1
000000000 loop N1 := 4; N2 := 5; N3 := 6; SELECT count(*) into v_count FROM LIU
WHERE ID1= N1 AND ID2= N2 AND ID3= N3; end loop; end;
SQL_ID CHILD_NUMBER PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
--------------------------------------- ------------ ---------- ---------- ---------- ---------- --------------------------------------------------------------------------------
SQL> alter system set "_cursor_bind_capture_interval"=1;
System altered.
15:19:38 SQL> /
VALUE_STRING
--------------------------------------------------------------------------------
4
5
6
Elapsed: 00:00:00.00
15:19:40 SQL> /
VALUE_STRING
--------------------------------------------------------------------------------
7
8
9
Elapsed: 00:00:00.01
15:19:41 SQL> /
VALUE_STRING
--------------------------------------------------------------------------------
7
8
9
Elapsed: 00:00:00.00
15:20:58 SQL> /
VALUE_STRING
--------------------------------------------------------------------------------
7
8
9
Elapsed: 00:00:00.00
15:20:59 SQL> /
VALUE_STRING
--------------------------------------------------------------------------------
7
8
9
Elapsed: 00:00:00.00
15:20:59 SQL> /
VALUE_STRING
--------------------------------------------------------------------------------
4
5
6
可以看到Capture binds 在不停的变化，而此时通过dbms_xplan peeking binds 则不会产生变化,这也验证了JL的这篇文章。
这篇文章的主旨为 “Capture binds always actually?”.通过实验已经证明了 – No 对于这个问题衍生出了peeked binds,Dion也曾总结过这个问题 可以总结为
– Peeked binds value of dbms_xplan.display_cursor is extracted from v$sql_plan.other_xml
– Captured and Peeked binds will refresh when generating child cursors
– Captured bind value is extraced from X$KQLFBC view.
– Peeked binds and Captured bind are not same thing
另外还可以通过一些手段去tracking bind values ：10046,logminer等 下面给个示范：
SQL> oradebug setorapid 41; Oracle pid: 41, Unix process pid: 30705, image: oracle@db-2-15 (TNS V1-V3) SQL> SQL> SQL> oradebug dump errorstack 4; Statement processed. SQL> oradebug dump errorstack 4; Statement processed. SQL> oradebug tracefile_name; /home/oracle/diag/rdbms/yhdpri/yhd/trace/yhd_ora_30705.trc
—– Bind Info (kkscoacd) —–
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f3c6bfd2b78 bln=22 avl=03 flg=09
value=1231
Bind#1
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f3c6bfd2ba8 bln=22 avl=04 flg=09
value=21231
Bind#2
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f3c6bfd2bd8 bln=22 avl=03 flg=09
value=3455
Frames pfr 0x7f3c6bff26b0 siz=4400 efr 0x7f3c6bff25f8 siz=4384
Cursor frame dump
enxt: 5.0×00000010 enxt: 4.0×00000048 enxt: 3.0×00000078 enxt: 2.0×00000060
enxt: 1.0×00001000
pnxt: 1.0×00000010
kxscphp=0x7f3c6bff0898 siz=984 inu=864 nps=544
kxscdfhp=0x7f3c6bff0778 siz=984 inu=88 nps=0
kxscbhp=0x7f3c6bff0c90 siz=984 inu=184 nps=0
kxscwhp=0x7f3c6bff0db0 siz=18680 inu=8448 nps=0
kxscefhp=0x7f3c6bff0ed0 siz=4056 inu=40 nps=40
Starting SQL statement dump
SQL Information
user_id=0 user_name=SYS module=sqlplus@db-2-15 (TNS V1-V3) action=
sql_id=33xdcqmh004pw plan_hash_value=-190543548 problem_type=4
—– Current SQL Statement for this session (sql_id=33xdcqmh004pw) —–
SELECT COUNT(*) FROM LIU WHERE ID1= :B3 AND ID2= :B2 AND ID3= :B1
Ref：http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/
