Bind Variables

I have made a few comments in previous articles about the use of bind variables and some of the peripheral details that can introduce surprises; and in the article on superfluous updates I made a throwaway comment about getting multiple child cursors for a single statement if you had columns of varchar2() or nvarchar2() defined to be longer than 32 bytes. It’s worth expanding on this point.

Here’s an extract from a 15-minute statspack report that I had to examine recently. It was from a system running Oracle 10.2.0.2, using snapshots at level 5, and the extract shows just the top entry from the “Version Count” section of the report:

 Version
   Count  Executions   Old Hash Value
-------- ------------ ---------------
     103       13,726    186168523
UPDATE tableX SET col1 = :1, col2 = :2, ...
col21 = :21 ...  {where rowid = :61}

Obviously I have changed the table and column names – essentially the statement was a simple update by rowid of about 60 columns in a single table.

Further checks on v$sql, v$librarycache, the data definitions and so on showed that the high version count (number of child cursors) was not related to name resolution issues, fine grained access control, cursor invalidation, or any of the other common structural or coding reasons for multiple child cursors. So where were the versions coming from ? The data itself.

There were 18 columns in the table declared as varchar2(4000), and the users’ inputs could be highly variable, so any one of these columns might actually be null, or hold data of any length up to the full 4,000 characters (although sizes in excess of 500 bytes were rare).

The problem is this – part of the identifying characteristic for a child cursor is the memory allocation for the input bind variables and, at some layer in the library code, character columns are allowed to have 4 different allocation sizes, namely 32, 128, 2000, or 4000 bytes. [Update Aug 2017 – actually 5, I forgot the possibility of zero lengths: see comment 36]

So, when the front-end code was calling this statement, part of the definition for the incoming bind variables was set by the actual values supplied by the end user. Since there were 18 columns declared as varchar2(4000), and each of these columns could (in principle) be declared with one of 4 different bind allocation sizes, then there could have been (in principle) power(4,18) – roughly 64 billion – different child cursors needed to cope with all possible variations in the data supplied.  Luckily we only got a few hundred in the worst possible cases.

Does it matter if you get lots of (valid) versions of a single cursor ? Yes, it does, because all the versions are covered by the same library cache latch; and if you have to spend time checking which version is the right one for your specific data set, then you will be holding the latch and blocking other users for longer and longer periods as the version count grows. Indeed, one of the most significant performance problems on this system was latch contention for the library cache latches. (As I write this, it’s just occurred to me that this might also be a limiting issue with the new mutex strategy for 10gR2 – it’s all very well using mutexes to protect a child cursor, but if your session has to pick the right child cursor of several dozen on the same latch will there be any side effects?)

Our solution: in this case the main issue was the “one statement fits all updates” strategy. By recoding to have several different statements (and this is not always possible, of course) we ended up spreading the updates across many different library cache latches, and that reduced the contention. We also included logic that minimised the total number of versions (i.e. the sum of all versions of all statements) because we split the 18 columns up in several different ways.

If you want to demonstrate the effect here’s a simple test for Oracle 9i (it doesn’t give the same results on 10gR2, possibly because of a change in some layer of the SQL*Plus code).

rem
rem     Script: bind_issue_2.sql
rem     Dated:  Dec 2006
rem     Author: J.P.Lewis
rem

drop table t1;
create table t1 (v1 varchar2(4000), n1 nvarchar2(2000));               

alter session set events '10046 trace name context forever, level 4';               

variable b1 varchar2(5);
variable b2 nvarchar2(5);
exec :b1 := 'abc'; :b2 := 'abc';
insert into t1 values (:b1, :b2);               

variable b1 varchar2(18);
variable b2 nvarchar2(18);
exec :b1 := 'abc'; :b2 := 'abc';
insert into t1 values (:b1, :b2);               

variable b1 varchar2(33);
variable b2 nvarchar2(33);
exec :b1 := 'abc'; :b2 := 'abc';
insert into t1 values (:b1, :b2);               

variable b1 varchar2(129);
variable b2 nvarchar2(129);
exec :b1 := 'abc'; :b2 := 'abc';
insert into t1 values (:b1, :b2);               

alter session set events '10046 trace name context off';               

I’ve included an nvarchar2() column in the example simply to demonstrate that the effect depends on byte count, not character count (on my system, nvarchar2() uses a two-byte fixed length format).

In the trace file, you will find sections like the following, which is the section relating to the insert where the two bind variables were declared as 18 characters long:

PARSING IN CURSOR #3 len=32 dep=0 uid=62 oct=2 lid=62 tim=1121585657476 hv=340431440 ad='6fd220d0'
insert into t1 values (:b1, :b2)
END OF STMT
PARSE #3:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1121585657468
BINDS #3:
 bind 0: dty=1 mxl=32(18) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=160 offset=0
   bfp=0344fb30 bln=32 avl=03 flg=05
   value="abc"
 bind 1: dty=1 mxl=128(36) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=0 offset=32
   bfp=0344fb50 bln=128 avl=06 flg=01
   value=""
EXEC #3:c=0,e=1125,p=0,cr=1,cu=1,mis=1,r=1,dep=0,og=1,tim=1121585659132

Note the declarations of maximum bind length followed by the actual byte count mxl=32(18) for the varchar2 and mxl=128(36) for my nvarchar2.

Note also the report of a miss in the library cache mis=1 that appears in the EXEC line that follows the BINDS section.

Each variation of the variable sizing in the test case will introduce another library cache miss – which means another child cursor.