Separate Child Cursor with varying bind allocation length

Although usage of bind variables can mean that the SQL statement does not need to be re-parsed at every execution, if the length of the bind changes, Oracle may create a seperate child cursor.

For example, if I run this test code :

declare
instring varchar2(2000);
  begin
   for i in 1..1999 loop
     instring := rpad('X',i);
     execute immediate 'insert into abc values (:instring) ' using instring ;
   end loop;
end;
/
commit;

I am making 1999 executions of the statement with a bind length that starts at 1 and ends at 1,999. The resultant SQL has 3 Child Cursors as :

SQL> select sql_id, child_number, executions  from v$sql where sql_id = '5p20kaht8s5bc';

SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
5p20kaht8s5bc            0         32
5p20kaht8s5bc            1         96
5p20kaht8s5bc            2       1871

SQL> 

The first child was executed 32 times with a bind length of upto 32 bytes. The second child was executed 96 times for the next bind length "group" of upto 128 bytes. The third child was executed for the "group" of upto 2000 bytes.

 If I then run the loop as :

declare
instring varchar2(4000);
  begin
   for i in 1..2001 loop
     instring := rpad('X',i);
     execute immediate 'insert into abc values (:instring) ' using instring ;
    end loop;
end;
/
commit;

I see Child Cursor executions as :

SQL> select sql_id, child_number, executions  from v$sql where sql_id = '5p20kaht8s5bc';

SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
5p20kaht8s5bc            0         32
5p20kaht8s5bc            1         96
5p20kaht8s5bc            2       1872
5p20kaht8s5bc            3          1

SQL> 

So the next "group" is 2000 bytes.

See Jonathan Lewis's reference to this behaviour of Bind Variables.

.
.
.