Oracle

February 20, 2012

Setting _cursor_bind_capture_area_size for large inlists

Filed under: Uncategorized — srivenu @ 10:23 am

In one of the databases i was working on (Oracle 10.2.0.4.0 – 64bit on AIX6.1), there is a sql 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)
	)
/

I checked the bind variables captured for this sql


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     1 :1             1     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 0123456789
1yuk67czbhum1     1 :2             2     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 1111111111
1yuk67czbhum1     1 :3             3     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 2222222222
1yuk67czbhum1     1 :4             4     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 3333333333
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     1 :39           39     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 6767676767
1yuk67czbhum1     1 :40           40     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 7878787878
1yuk67czbhum1     1 :41           41     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :42           42     1 VARCHAR2(32)                         32 NO
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     1 :999         999     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :1000       1000     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :1001       1002     2 NUMBER                               22 NO
1yuk67czbhum1     1 :1002       1002     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :1003       1003     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :1004       1004     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :1005       1005     1 VARCHAR2(32)                         32 NO

Since the parameter _cursor_bind_capture_area_size was set to the default value of 400, the values of only 40 bind variables (each 10 bytes in size) have been captured.
But I wanted to see the value for bind variable :1001 (in the predicate CIRCLE = :1001). Since it is at the 1001 position, i thought i needed to set _cursor_bind_capture_area_size to something above 10003. When i tried to set it to a value of 10003, i got an error. The max allowed value for that parameter is 3999. I wanted to check what would happen if i set it to the max value and following is what is captured.


                                                                                   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

I then tried setting it to 3998 and this is what is captured.


                                                                                   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 19:19:01 0123456789
1yuk67czbhum1     2 :2             2     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 1111111111
1yuk67czbhum1     2 :3             3     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 2222222222
1yuk67czbhum1     2 :4             4     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 3333333333
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     2 :398         398     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 6767676767
1yuk67czbhum1     2 :399         399     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 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 NO
1yuk67czbhum1     2 :1002       1002     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1003       1003     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1004       1004     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1005       1005     1 VARCHAR2(32)                         32 NO

So looks like setting parameter _cursor_bind_capture_area_size to the max value has some significance.
If you set _cursor_bind_capture_area_size to 3999, it is going to capture all the single element bind variables and small inlists and try to cut down from storing all values in large inlists. This would be useful if you have large inlists (I think it would be very rare to see large number of bind variables without inlists).

Blog at WordPress.com.