I am sure many of you have already become bored by my posts on adaptive cursor sharing. I hope this article will be the last one :-). In part III of the installment I was unable to figure out the secret sauce Oracle is using to mark a cursor bind aware when **count of the 3 buckets** of a bind sensitive cursor are **greater than 0**. For those who want to know what bucket and count represent they can start by reading part I and part II

Thanks to a comment dropped by an anonymous reader coupled with my understanding of the adaptive cursor sharing mechanism I think I have succeeded to figure out that secret sauce. My goal is to publish it and let readers break it and invalidate it.

To make things simple I created the following function

------------------------------------------------------------------------------
-- File name: fv_will_cs_be_bind_aware
-- Author : Mohamed Houri (Mohamed.Houri@gmail.com)
-- Date : 29/08/2015
-- Purpose : When supplied with 3 parameters
-- pin_cnt_bucket_0 : count of bucket_id n°0
-- pin_cnt_bucket_1 : count of bucket_id n°1
-- pin_cnt_bucket_2 : count of bucket_id n°2
--
-- this function will return a status:
--
-- 'Y' if the next execution at any bucket_id will mark the cursor bind aware
--
-- 'N' if the next execution any bucket_id will NOT mark the cursor bind aware
--
--------------------------------------------------------------------------------
create or replace function fv_will_cs_be_bind_aware
(pin_cnt_bucket_0 in number
,pin_cnt_bucket_1 in number
,pin_cnt_bucket_2 in number)
return
varchar2
is
lv_will_be_bind_aware
varchar2(1) := 'N';
ln_least_0_2 number := least(pin_cnt_bucket_0,pin_cnt_bucket_2);
ln_great_0_2 number := greatest(pin_cnt_bucket_0,pin_cnt_bucket_2);
begin
if pin_cnt_bucket_0 + pin_cnt_bucket_2 >= pin_cnt_bucket_1
and ln_least_0_2 >= ceil ((ln_great_0_2-pin_cnt_bucket_1)/3)
then
return 'Y';
else
return 'N';
end if;
end fv_will_cs_be_bind_aware;

If you have a cursor with a combination of 3 buckets having a count > 0 and you want to know whether the next execution will mark the cursor bind aware or not then you have just to do this:

SQL> select fv_will_cs_be_bind_aware(10,1,3) acs from dual;
ACS
---
Y

Or this

SQL> select fv_will_cs_be_bind_aware(10,1,2) acs from dual;
ACS
---
N

In its first call the function is indicating that the next cursor execution will compile a new optimal plan while the second call indicates that the existing child cursor will still be shared.

It’s now time to practice:

SQL> alter session set cursor_sharing=FORCE;
SQL> select count(1) from t_acs where n2 = 100;
COUNT(1)
----------
100
SQL> select
2 child_number
3 ,bucket_id
4 ,count
5 from
6 v$sql_cs_histogram
7 where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 1
0 1 0
0 2 0
SQL> select count(1) from t_acs where n2 = 100;
COUNT(1)
----------
100
SQL> /
-- repeat this 7 times
SQL> select
2 child_number
3 ,bucket_id
4 ,count
5 from
6 v$sql_cs_histogram
7 where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 10 --> 10 executions at bucket_id 0
0 1 0
0 2 0

Now change the bind variable value so that the bucket_id n°1 will be incremented

SQL> select count(1) from t_acs where n2 = 10000;
COUNT(1)
----------
100000
SQL> select
2 child_number
3 ,bucket_id
4 ,count
5 from
6 v$sql_cs_histogram
7 where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 10
0 1 1 --> 1 executions at bucket_id 1
0 2 0

Now change again the bind variable value so that the bucket_id n°2 will be incremented

SQL> select count(1) from t_acs where n2 = 1000000;
COUNT(1)
----------
1099049
SQL> select count(1) from t_acs where n2 = 1000000;
COUNT(1)
----------
1099049
SQL> select
2 child_number
3 ,bucket_id
4 ,count
5 from
6 v$sql_cs_histogram
7 where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 10
0 1 1
0 2 2 --> 2 executions at bucket_id 2

If, at this stage, you want to know whether the next execution at bucket id n°2 will mark the cursor bind aware or not then make a call to the function:

SQL> select fv_will_cs_be_bind_aware(10,1,2) acs from dual;
ACS
----
N

No, it will not and here’s below the proof:

SQL> select count(1) from t_acs where n2 = 1000000;
COUNT(1)
----------
1099049
SQL> select
2 child_number
3 ,bucket_id
4 ,count
5 from
6 v$sql_cs_histogram
7 where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 10
0 1 1
0 2 3

And what about the next execution, say at bucket_id n° 0?

SQL> select fv_will_cs_be_bind_aware(10,1,3) acs from dual;
ACS
----
Y

The function is indicating that the next execution will compile a new child cursor; let’s check:

SQL> select count(1) from t_acs where n2 = 100;
COUNT(1)
----------
100
SQL> select
2 child_number
3 ,bucket_id
4 ,count
5 from
6 v$sql_cs_histogram
7 where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 1
1 1 0
1 2 0
0 0 10
0 1 1
0 2 3

Yes it did.

Can we assume from a single test that this function is reliable? No.

You want another example? Here it is:

SQL> -- run the following sql 19 times at bucket_id n°0
SQL> select count(1) from t_acs where n2 = 100;
SQL> -- run the same sql 6 times at bucket_id n°1
SQL> select count(1) from t_acs where n2 = 10000;
SQL> -- run the same sql 2 times at bucket_id n°2
SQL> select count(1) from t_acs where n2 = 1000000;

And here’s the resulting cursor sharing picture:

SQL> select
2 child_number
3 ,bucket_id
4 ,count
5 from
6 v$sql_cs_histogram
7 where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 19
0 1 6
0 2 2

Will the next execution compile a new execution plan?

SQL> select fv_will_cs_be_bind_aware(19,6,2) acs from dual;
ACS
---
N

No, it will not as proofed here below:

SQL> select count(1) from t_acs where n2 = 1000000;
COUNT(1)
----------
1099049
SQL> select
2 child_number
3 ,bucket_id
4 ,count
5 from
6 v$sql_cs_histogram
7 where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 19
0 1 6
0 2 3

And what about the next execution at the same bucket_id n°2? And the next next execution?

SQL> select fv_will_cs_be_bind_aware(19,6,3) acs from dual;
ACS
---
N
SQL> select fv_will_cs_be_bind_aware(19,6,4) acs from dual;
ACS
---
N

And the next execution?

SQL> select fv_will_cs_be_bind_aware(19,6,5) acs from dual;
ACS
----
Y

At the (bucket_id, count) situation shown below the function is indicating that the next execution will mark the cursor bind aware and compile a new execution plan:

SQL> select
2 child_number
3 ,bucket_id
4 ,count
5 from
6 v$sql_cs_histogram
7 where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 19
0 1 6
0 2 5

Isn’t it?

SQL> select count(1) from t_acs where n2 = 1000000;
COUNT(1)
----------
1099049
SQL> select
2 child_number
3 ,bucket_id
4 ,count
5 from
6 v$sql_cs_histogram
7 where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 0
1 1 0
1 2 1
0 0 19
0 1 6
0 2 5

Yes it did as you can point it out via the apparition of the new child cursor n°1

Want another example? Here’s

SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 3
0 1 1
0 2 11
SQL> select fv_will_cs_be_bind_aware(3,1,11) acs from dual;
ACS
---
N
SQL> select count(1) from t_acs where n2 = 10000;
COUNT(1)
----------
100000
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 3
0 1 2
0 2 11
SQL> select fv_will_cs_be_bind_aware(3,2,11) acs from dual;
ACS
---
Y
SQL> select count(1) from t_acs where n2 = 1000000;
COUNT(1)
----------
1099049
SQL> select
child_number
,bucket_id
,count
from
v$sql_cs_histogram
where sql_id = '7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 0
1 1 0
1 2 1
0 0 3
0 1 2
0 2 11

I am sure that someone will come up with a simple situation where the function is returning a wrong result. Bear in mind that this is what I want and you’re welcome.

**Footnote**

If you want to break this function then here’s the model you can use (you need to have histogram on n2 column)

create table t_acs(n1 number, n2 number);
BEGIN
for j in 1..1200150 loop
if j = 1 then
insert into t_acs values (j, 1);
elsif j>1 and j<=101 then insert into t_acs values(j, 100); elsif j>101 and j<=1101 then insert into t_acs values (j, 1000); elsif j>10001 and j<= 110001 then
insert into t_acs values(j,10000);
else
insert into t_acs values(j, 1000000);
end if;
end loop;
commit;
END;
/
create index t_acs_i1 on t_acs(n2);

**Update **: 08/09/2015 : added a supplementary if condition to the function