VMCD.ORG

Focus on database architecture

Capture binds always actually ?

Posted by admin on August 28th, 2012

我们可以通过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

Refhttp://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>