Oracle

March 4, 2012

“Bind Variable Peeking” vs “Bind Variable Capture”

Filed under: Uncategorized — srivenu @ 6:35 am

Bind variable peeking is a feature introduced in 9i. The CBO looks (peeks) at the underlying values of bind variables to generate a better execution plan. The bind values peeked can be seen in the OTHER_XML column in V$SQL_PLAN or BIND_DATA column in V$SQL. The space used for peeked binds in V$SQL_PLAN is determined by the parameter “_xpl_peeked_binds_log_size”. In some circumstances (ex – sql with large inlists of bind variables) the size may be exceeded and we might not see all the peeked bind values. But that doesnt mean that the variable value is not peeked.

Bind variable capture is a feature introduced in 10g. It is just a periodic capture of the underlying values of bind variables. It had nothing to do with plan generation(i.e the plan is not generated for this set of bind values). It only helps us to periodically look at new set of bind values for that sql. The captured values are available from V$SQL_BIND_CAPTURE. The capturing interval is determinded by the parameter “_cursor_bind_capture_interval”. The space used for captured binds is determined by the parameter “_cursor_bind_capture_area_size”. In some circumstances (ex – sql with large inlists of bind variables) the size may be exceeded and we might not see all the captured bind values. In such cases setting “_cursor_bind_capture_interval” to the max value of 3999 helps in looking at most of the captured values.

I got a sql (with sql_id – ac95hnhh878hc) like this


select 	......
from 	SUB
where 	(
		(
		ID in 	(
			:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, 
			:21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38,
			.......................
			.......................
			:993, :994, :995, :996, :997, :998, :999, :1000
			) 
		and 	CIRCLE = :1001
		) 
	and 	STATUS in (:1002, :1003,:1004, :1005)
	)
/


To look at the peeked binds for the above sql, you could use any of the following sql.

select 	* 
from 	table(DBMS_XPLAN.DISPLAY_CURSOR('ac95hnhh878hc', null, 'advanced'))
/

or

select 	* 
from 	table(DBMS_XPLAN.DISPLAY_CURSOR('ac95hnhh878hc', null, '+peeked_binds'))
/

or 

(i had to write the case for bind_data in below sql in a funny sort of way due to some issue on my db version)

col sql_id head "SQL ID" form a13
col child_number head "Chi|ld|No" form 9999
col pos  head "Bind|Posi|tion" form a4
col nam head "Bind|Name" form a10
col dty head "Data|type|ID" form a4
col datatype_string head "Datatype" form a14
col mxl head "Max Bind|Length" form a4
col was_captured head "Bind|value|Capt|ured|?" form a5
col bind_data_fm head "Actual Value of Bind" form a25
col bind_data_raw head "Raw Value of Bind" form a25

select	'&1' sql_id,
	decode('&2',null,0,'&2') child_number,
	pos,
	nam,
	dty,
	case 	when dty = 1 then 'VARCHAR2'
		when dty = 2 then 'NUMBER'
		else dty 
	end 	datatype_string,
	mxl,
	case 	when dty = 1
			then utl_raw.cast_to_varchar2(bind_data)
	end	||
	case 	when dty = 2
			then utl_raw.cast_to_number(bind_data)
	end 	bind_data_fm,
	bind_data bind_data_raw
from 	(
	select	extractvalue(value(d), '/bind/@pos') as pos,
		extractvalue(value(d), '/bind/@nam') as nam,
		extractvalue(value(d), '/bind/@dty') as dty,
		extractvalue(value(d), '/bind/@mxl') as mxl,
		extractvalue(value(d), '/bind') as bind_data
	from	xmltable('/*/*/bind' passing (
			select	xmltype(other_xml) as xmlval
			from	v$sql_plan
			where	sql_id = '&1'
			and	child_number = decode('&2',null,0,'&2')
			and 	other_xml is not null
			)
		) d
	)
/


The output was something like this for DBMS_XPLAN.DISPLAY_CURSOR('ac95hnhh878hc', null, 'advanced'))

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |       |       | 18795 (100)|          |       |       |
|   1 |  PARTITION RANGE SINGLE|               |  1167 | 59517 | 18795  (18)| 00:03:46 |   KEY |   KEY |
|   2 |   PARTITION HASH ALL   |               |  1167 | 59517 | 18795  (18)| 00:03:46 |     1 |    29 |
|*  3 |    TABLE ACCESS FULL   |           SUB |  1167 | 59517 | 18795  (18)| 00:03:46 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :V1 (VARCHAR2(30), CSID=31): '1234567890'
   2 - :V2 (VARCHAR2(30), CSID=31): '2345678910'
   3 - :V3 (VARCHAR2(30), CSID=31): '3456789120'
.................
.................
 511 - :V511 (VARCHAR2(30), CSID=31): '9876543210'
 512 - :V512 (VARCHAR2(30), CSID=31): '9999999999'
 513 - :V513 (VARCHAR2(30), CSID=31, Not Captured)
 514 - :V514 (VARCHAR2(30), CSID=31, Not Captured)
.................
.................
1001 - :v1001 (NUMBER, Not Captured)
1002 - :v1002 (VARCHAR2(30), CSID=31, Not Captured)
1003 - :v1003 (VARCHAR2(30), CSID=31, Not Captured)
1004 - :v1004 (VARCHAR2(30), CSID=31, Not Captured)
1005 - :v1005 (VARCHAR2(30), CSID=31, Not Captured)

The output shows that the bind variable :v1001 is “Not Captured”. This does not mean that it is not peeked.
I had confirmed this by using 2 different executions with different CIRCLE values.
For CIRCLE value 14, the CBO uses a INDEX RANGE SCAN & for CIRCLE value 23, the CBO uses a Full Table Scan.
So even though the output showed 1001 – :v1001 (NUMBER, Not Captured), there was a change in the plan, which meant that the value was peeked.
As mentioned at the start, the max value of binds peeked is determined by the parameter “_xpl_peeked_binds_log_size” and it maxes out at 8192.

To look at the captured binds, i use the following sql. (“_cursor_bind_capture_interval” is set to 3999)


col sql_id head "SQL ID" form a13
col child_number head "Chi|ld|No" form 9999
col name head "Bind|Name" form a10
col position head "Bind|Posi|tion" form 9999
col datatype head "Data|type|ID" form 9999
col datatype_string head "Datatype" form a14
col precision head "Precision" form 9999
col scale head "Scale" form 9999
col max_length head "Max Bind|Length" form 9999
col was_captured head "Bind|value|Capt|ured|?" form a5
col lc head "Last|Captured" form a18
col value_string head "Value|of Bind" form a15

select 	SQL_ID,
	CHILD_NUMBER,
	NAME, POSITION,
	DATATYPE, DATATYPE_STRING,
	PRECISION, SCALE, MAX_LENGTH, 
	WAS_CAPTURED, to_char(LAST_CAPTURED,'dd-mon-yy hh24:mi:ss') lc, 
	VALUE_STRING
from 	V$SQL_BIND_CAPTURE
where  	sql_id='&1'
order	by 1,2,4
/


                                                                                   Bind
                                                                                   value
                Chi             Bind  Data                                         Capt
                 ld Bind        Posi  type                                Max Bind ured  Last               Value
SQL ID           No Name        tion    ID Datatype       Precision Scale   Length ?     Captured           of Bind
------------- ----- ---------- ----- ----- -------------- --------- ----- -------- ----- ------------------ ---------------
1yuk67czbhum1     2 :1             1     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 0123456789
1yuk67czbhum1     2 :2             2     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 1111111111
1yuk67czbhum1     2 :3             3     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 2222222222
1yuk67czbhum1     2 :4             4     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 3333333333
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     2 :398         398     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 6767676767
1yuk67czbhum1     2 :399         399     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 7878787878
1yuk67czbhum1     2 :400         400     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :401         401     1 VARCHAR2(32)                         32 NO
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     2 :999         999     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1000       1000     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1001       1002     2 NUMBER                               22 YES   17-feb-12 18:58:44 99
1yuk67czbhum1     2 :1002       1002     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL1
1yuk67czbhum1     2 :1003       1003     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL2
1yuk67czbhum1     2 :1004       1004     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL3
1yuk67czbhum1     2 :1005       1005     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL4

Advertisements

1 Comment »

  1. great post. was looking for the same in simplified manner

    Comment by Harmandeep Singh — May 23, 2012 @ 2:48 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: