Mohamed Houri’s Oracle Notes

September 5, 2015

Bind aware secret sauce (again)

Filed under: Oracle — hourim @ 8:21 am

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

Advertisements

4 Comments »

  1. Hi Mohamed,

    nice that you kept on trying to find out the rules when a new child cursor will be generated or not.

    Could you try to express the function’s code in words, so what is the logic behind it?

    Also, since the function doesn’t include the information what bucket the next execution will fall into – the assumption is that it doesn’t matter – the combination of values in those three buckets determine that a new child cursor will be created or not at next execution, no matter what value will be used for the next execution, correct?

    Thanks,
    Randolf

    Comment by Randolf Geist — January 21, 2016 @ 11:40 am | Reply

  2. Hi Randolf

    Thanks for your comment.

    Yes you are right. When the function returns Y then the next execution at any bucket will mark the cursor bind aware.

    I have to confess though that this function has been tested on gentle cases and have never been confronted with practical cases.

    Here’s below for example a case taken from a running system:

    SQL> select
            sql_id
           ,child_number
           ,is_bind_aware
           ,is_bind_sensitive
          ,executions
        from gv$sql
        where sql_id = 'chdspybxxa74w';
    
    SQL_ID        CHILD_NUMBER I I EXECUTIONS
    ------------- ------------ - - ----------
    chdspybxxa74w            1 N Y     359854
    chdspybxxa74w            2 Y Y     195828
    
    SQL> select
             sql_id
            ,child_number
            ,bucket_id
            ,count
          from gV$SQL_CS_histogram
          where sql_id = 'chdspybxxa74w';
    
    SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
    ------------- ------------ ---------- ----------
    chdspybxxa74w            2          0       3800
    chdspybxxa74w            2          1       6146
    chdspybxxa74w            2          2         54
    chdspybxxa74w            1          0       3797
    chdspybxxa74w            1          1       6149
    chdspybxxa74w            1          2         54
    
    

    The child_number 2 is bind aware and the previous child cursor n°1 has the count of the three buckets > 0. Let’s apply the function to this practical case

    SQL> select fv_will_cs_be_bind_aware(3797,6149,54) ACS from dual;
    
    ACS
    ------
    N
    

    The function is saying that the next execution will not mark the cursor bind aware but it has marked it bind aware. And the reason for this new execution plan compilation is indeed due to ACS (bind_equivalent_failure = ‘Y’)

    
    SQL> select  sql_id, child_number, bind_equiv_failure
         from gv$sql_shared_cursor
        where  sql_id = 'chdspybxxa74w'
        ;
    
    SQL_ID        CHILD_NUMBER B
    ------------- ------------ -
    chdspybxxa74w            1 N
    chdspybxxa74w            2 Y
    

    So I am having serious doubt about the reliability of this function.

    As per regards to the logic of the function, depending on the number of rows processed by the cursor, each execution is associated with a bucket id (0, 1 and 2).
    To qualify for the function, execution at all buckets should be >0

    First I concentrate myself on the executions done at the two distant buckets that is 0 and 2. Among these executions I identify the smallest and the greatest number of executions.

    The function follows the following logic:
    IF the sum of executions done at the two distant buckets (0 and 2) is >= to the number of executions done at bucket n° 1
    THEN
    IF the smallest number of executions done at the two distant buckets (0 and 2) >=
    CEIL (the greatest number of executions done at the two distant buckets (0 and 2) MINUS the number of executions done at bucket n°1)/3
    THEN
    the next execution will mark the cursor bind aware
    ELSE
    the next execution will NOT mark the cursor bind aware
    END IF

    Best regards

    Comment by hourim — January 21, 2016 @ 1:21 pm | Reply

    • Hi Mohamed,

      thanks for the logic description of the function and the confirmation regarding the irrelevance of the bucket count increased at next execution.

      I wonder about your example from a running system: Don’t the two child cursors already exist when you call your function with the current counts from the V$SQL_CS_HISTOGRAM? So isn’t the outcome of the function correct in that sense that no *third* child cursor got created at next execution? Maybe I missed something obvious…

      Thanks,
      Randolf

      Comment by Randolf Geist — January 22, 2016 @ 9:55 pm | Reply

  3. Randolf

    Sorry for this delay in my answer,

    I completely forget to mention that the function has no sense once a cursor has been marked bind aware. It is only during the “warming” period that Oracle monitors the number of executions before marking a cursor bind aware and where the function is applicable. Once a cursor is marked bind aware in v$sql Oracle will then start using v$sql_cs_selectivity to decide whether to share an existing cursor or to create a new one. So when I applied the function to child_number n°1 I was wrong because the cursor child_number 1 was very probably already bind aware. And the reason (Bind_equivalent_failure) for which cursor n°2 has been created confirms that cursor n°1 was already bind aware.

    Of course there is no sense also to apply the function to child_number 2 in order to see if the next execution will compile a third cursor because Oracle in this case will use v$sql_cs_selectivity.

    I am sorry for the confusion I have made with my comment. And I have to re test my function in the client running system when there is no bind aware cursor in v$sql.

    In this upcoming book (http://www.amazon.com/Oracle-Problem-Solving-Troubleshooting-Handbook/dp/0134429206) I wrote a complete chapter (Chapter 4) on ACS where I have explained what I wrote above.

    I will put a comment when I will re-test again my function in the client runing system during the “warming” period.

    Best regards
    Mohamed

    Comment by hourim — January 23, 2016 @ 7:44 pm | Reply


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

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: