In the book I have co-authored I wrote chapter 4 on Adaptive Cursor Sharing which you can read here free of charge. In this chapter I said that Oracle uses an algorithm to transit a cursor from bind sensitive to bind aware. I have demonstrated that this algorithm handles three different cases:
- when only two adjacent buckets are involved
- when only two distinct buckets are involved
- when all three buckets are involved
In this article and in this one I have explained reliably how the two first cases are handled. But I have always failed to decipher how Oracle manages the third case. I think that this article will finally explain the nature of the algorithm used by Oracle to mark bind aware a bind sensitive cursor when all buckets are involved. Here’s a function I have implemented for that purpose:
create or replace function fv_is_bind_aware
(pin_first_bucket in number
,piv_sql_id in varchar2
)
return varchar2
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Date : January 2018
-- Scope : say whether the next execution at any bucket will
-- mark the underlying cursor bind aware or not.
-- This function is valid only when the bind sensitive
-- cursor has undergone executions at three buckets 0,1 and 2
-- without being already bind aware
-- Parameters:
-- 1) pin_first_bucket is the bucket at which the very first execution
-- has been done
-- 2) piv_sql_id is the sql_id of the underlying cursor
--
--Usage : select fv_is_bind_aware(0, 7ck8k47bnqpnv) from dual;
-- if 'Y' then the next execution will mark cursor bind aware
-- if 'N' then the cursor will not be hard parsed during the next
-- execution
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
is
ln_nbr_of_distantexecs number := 0;
ln_nbr_of_totalexecs number := 0;
begin
for x in (select
bucket_id
,count
from
sys.v_$sql_cs_histogram
where
sql_id = piv_sql_id
order by bucket_id
)
loop
ln_nbr_of_totalexecs := ln_nbr_of_totalexecs + x.count;
ln_nbr_of_distantexecs := ln_nbr_of_distantexecs
+ (x.count *abs(x.bucket_id - pin_first_bucket));
end loop;
--
if ln_nbr_of_distantexecs/ln_nbr_of_totalexecs >= 0.5
then
return 'Y';
else
return 'N';
end if;
end fv_is_bind_aware;
/
Using plain english the above function can be turned to:
- When the number of executions done at distant buckets reaches half that of total executions at all buckets then mark the cursor bind aware during the next execution at any bucket.
The only subtlety here is to figure out what a distant bucket represents in this context. When you know how ACS works internally you know the importance Oracle gives to the very first execution of the bind sensistive cursor. Since there are three buckets 0, 1 and 2, a distant bucket represents the two buckets at which the very first execution didn’t occur. So Oracle will always keep track of the first bucket at which the cursor has been executed and compute the total number of executions done at the other two buckets (the adjacent one and the distant one). And for the sake of completeness Oracle considers:
- total number of executions = sum(v_$sql_cs_histogram.count)
- the total number of execution done at distant(non adjacent) bucket is doubled
ln_nbr_of_distantexecs := ln_nbr_of_distantexecs
+ (x.count *abs(x.bucket_id – pin_first_bucket));
-- Since bucket id 0 and 2 are the two distant buckets we have
abs (0-2) = abs (2-0) = 2
All cases I have tested so far confirmed the reliability of this function. Here’s below few of those test cases (the model as always can be found here):
alter system flush shared_pool;
select count(1) from t_acs where n2 = 1; –- very fist execution at bucket 0
select count(1) from t_acs where n2 = 1;
select count(1) from t_acs where n2 = 1;
–-
select count(1) from t_acs where n2 = 1000;
select count(1) from t_acs where n2 = 1000;
–-
select count(1) from t_acs where n2 = 1e6;
SQL_ID 7ck8k47bnqpnv, child number 0
-------------------------------------
------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| T_ACS_IDX1 | 1 |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N2"=:SYS_B_1)
select
child_number
,bucket_id
,count
from
gv$sql_cs_histogram
where
sql_id ='7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 3 → 3 executions at bucket n°0
0 1 2 → 2 executions at bucket n°1
0 2 1 → 1 executions at bucket n°2
Will the next execution at any bucket mark the cursor bind aware and hard parse a new execution plan? Let’s ask the function
SQL> select fv_is_bind_aware(0, '7ck8k47bnqpnv') iba from dual; IBA --- Y
The function is saying that the next execution will mark the cursor bind aware. Let’s check
select count(1) from t_acs where n2 = 100;
select
child_number
,bucket_id
,count
from
gv$sql_cs_histogram
where
sql_id ='7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 1 –- a new bind aware child cursor
1 1 0
1 2 0
0 0 3
0 1 2
0 2 1
Indeed the function has correctly expected the transition from bind sensitive to bind aware.
Do you want another example? Here you are:
alter system flush shared_pool;
-- execute this 7 times
–- first execution occurred at bucket n°2
select count(1) from t_acs where n2 = 1e6;
–- execute this 2 times
select count(1) from t_acs where n2 = 1000;
-- execute this 2 times
select count(1) from t_acs where n2 = 1;
select
child_number
,bucket_id
,count
from
gv$sql_cs_histogram
where
sql_id ='7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 2
0 1 2
0 2 7
Since the very first execution occurred at bucket n°2 we are going to call the function as follows:
select fv_is_bind_aware(2, '7ck8k47bnqpnv') iba from dual;
IBA
---
Y
select count(1) from t_acs where n2 = 1e4;
select
child_number
,bucket_id
,count
from
gv$sql_cs_histogram
where
sql_id ='7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 0
1 1 1
1 2 0
0 0 2
0 1 2
0 2 7
Spot how the function has again correctly expected the transition.
Here’s another example where the very first execution has been done at bucket id n°1
alter system flush shared_pool;
-- run this 7 times
select count(1) from t_acs where n2 = 1000;
-- run this 2 time
select count(1) from t_acs where n2 = 1e6;
-- run this 2 times
select count(1) from t_acs where n2 = 100;
select
child_number
,bucket_id
,count
from
gv$sql_cs_histogram
where
sql_id ='7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 2
0 1 7
0 2 2
-- check the transition using the function
select fv_is_bind_aware(1, '7ck8k47bnqpnv') iba from dual;
IBA
---
N
According to the function the next execution will not mark the cursor bind aware:
select count(1) from t_acs where n2 = 1e4;
select
child_number
,bucket_id
,count
from
gv$sql_cs_histogram
where
sql_id ='7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 2
0 1 8
0 2 2
That’s again a correct expectation.
Let me emphasize here the importance of the first parameter of the function fv_is_bind_aware which represents the bucket id at which the very first execution of the cursor has been done under. Should I have used the wrong bucket id (0 and 2) the function would have done a wrong expectation as shown below(test have been done before the 8th execution above):
select fv_is_bind_aware(0, '7ck8k47bnqpnv') iba from dual; IBA --- Y select fv_is_bind_aware(2, '7ck8k47bnqpnv') iba from dual; IBA --- Y
Now will the next execution mark the cursor bind aware?
select fv_is_bind_aware(1, '7ck8k47bnqpnv') iba from dual;
IBA
---
N
select count(1) from t_acs where n2 = 100;
select
child_number
,bucket_id
,count
from
gv$sql_cs_histogram
where
sql_id ='7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 3
0 1 8
0 2 2
And the next execution?
select fv_is_bind_aware(1, '7ck8k47bnqpnv') iba from dual;
IBA
---
N
select count(1) from t_acs where n2 = 1e6;
select
child_number
,bucket_id
,count
from
gv$sql_cs_histogram
where
sql_id ='7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 3
0 1 8
0 2 3
And so on until the function says it time for the cursor to switch
select fv_is_bind_aware(1, '7ck8k47bnqpnv') iba from dual;
IBA
---
N
select count(1) from t_acs where n2 = 100;
select count(1) from t_acs where n2 = 1e6;
select fv_is_bind_aware(1, '7ck8k47bnqpnv') iba from dual;
IBA
---
Y
select count(1) from t_acs where n2 = 1;
select
child_number
,bucket_id
,count
from
gv$sql_cs_histogram
where
sql_id ='7ck8k47bnqpnv';
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
1 0 1
1 1 0
1 2 0
0 0 4
0 1 8
0 2 4
Summary
Adaptive Cursor Sharing in real life systems is known to cause performance pain rather than to solve the conflictual issue of sharing and optimizing it has been created for. But it is always nice to know how it works. Particularly to understand the transition of the cursor from bind sensistive to bind aware.
Mohamed Houri’s Oracle Notes 