Mohamed Houri’s Oracle Notes

March 31, 2018

From bind sensitive to bind aware

Filed under: adaptive cursor sharing,cursor sharing — hourim @ 1:20 pm

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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: