Mohamed Houri’s Oracle Notes

November 8, 2014

Bind Aware – Part II

Filed under: cursor sharing — hourim @ 1:49 pm

In part I we saw how the count of the bucket_id in the v$cs_histogram view is incremented according to the number of rows their corresponding child cursor execution has processed depending on the bind variable value. We saw that this count-bucket_id relationship follows the following algorithm

  • If the number of processed rows < 1,000 then increment count of bucket_id n°0
  • If the number of processed rows < 1,000,000 then increment count of bucket_id n°1
  • If the number of processed rows > 1,000,000 then increment count of bucket_id n°2

In part II we will see how the decision to mark a cursor bind-aware is made. I have found three situations in which the CBO is triggered to compile a new execution plan:

    • increment the count of two adjacent bucket_id (0-1 or 1-2)
    • increment the count of the two distant bucket_id (0-2)
    • increment the count of the three bucket_id (0-1-2)

1. Two neighbour bucket_ids

Let’s start with the first situation. For this I will be using ‘j100’ and ‘j10000’ bind variable values as far as these two values increment two adjacent bucket_id which are 0 and 1. I will be using the same table and the same query as in part I.

SQL> exec :lvc := 'j100'

SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43 -- see script at the end

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          1
           0          1          0
           0          2          0

Next, I will execute the same query 6 extra times so that I will get the following cursor monitoring picture:

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          7
           0          1          0
           0          2          0

7 executions using the same bind variable value (‘j100’) which has incremented the count of bucket_id n° 0 as expected.
Now, I will change the bind variable value so that it is the bucket_id n°1 that will be incremented and execute the same query 7 times

SQL> exec :lvc := 'j10000'

SQL>select count(1) from t_acs where vc2 = :lvc; -- repeat this 7 times

SQL> @cntbukt 6f6wzmu3yzm43 

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          7
           0          1          7
           0          2          0

After 7 executions using the new bind variable we are still sharing the same child cursor n°0. However,Oracle has, as expected too, incremented the bucket_id n°1 7 times. Let’s add an extra run of the same query and see what happens:

SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          0
           1          1          1
           1          2          0
           0          0          7 -- equals its neighbour
           0          1          7 -- equals its neighbour
           0          2          0

Finally after at the 8th execution Oracle has decided that it is now time to compile a new plan (new child cursor n°1). This allows me to write the first below observation:

For the same child cursor, when the count of a bucket_id reaches the count of its neighbour bucket_id, the next execution will mark the original cursor as bind aware and a new child cursor is compiled.

This conclusion is of course valid only before the apparition of the first bind aware cursor in the cursor cache.

2. Two distant bucket_ids

In order to investigate the second case I will start by flushing the shared pool and repeat the same experiment with two distant (bucket_id) bind variables, ‘j100’ and ‘j>million’ starting by 6 cursor executions using the first bind variable.

SQL> alter system flush shared_pool;
SQL> exec :lvc := 'j100';
SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          6
           0          1          0
           0          2          0

As expected, the  count of bucket_id number 0 has been incremented 6 times. Now, I will execute the same query two times using the ‘j>million’ bind variable which favours the non-adjacent bucket_id n°2

SQL> exec :lvc := 'j>million'
SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43
CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          6
           0          1          0
           0          2          2

As expected too, the count of bucket_id number 2 has been incremented 2 times. But I am still sharing the same child cursor n°0. Let’s try an extra execution with the same bind variable

SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          0
           1          1          0
           1          2          1
           0          0          6
           0          1          0
           0          2          2

And finally a new child cursor n°1 has been created indicating that the cursor has been marked bind aware.

At this step I can make the second conclusion:

For the same child cursor, when the count of a bucket_id is greater or equal to  (trunc(count/2) – 1) of its distant non-adjacent bucket_id, the next execution will mark the original cursor as bind aware and a new child cursor is compiled.

We have 6 executions at bucket_id 0 and 2 executions at bucket_id n° 2 of the same child cursor n°0. We reaches the situation where 2>= (trunc(6/2)-1). This has the tendency to indicate that the the next execution (the 3rd one) will compile a new execution plan (child cursor n°1)

What happens when the number of executions is odd?

By flushing the shared pool and setting again the bind variable to ‘j100’ and executing the same query 11 times I obtained the following picture:

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         11
           0          1          0
           0          2          0

Then by setting the bind variable value to ‘j>million’ and executing the same query 4 times I arrived to the following picture:

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         11
           0          1          0
           0          2          4

The above pictures indicates that  4 >= trunc(11/2) – 1 = 4. This means that the next execution(5th one)  will compile a new plan:

SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          0
           1          1          0
           1          2          1
           0          0         11
           0          1          0
           0          2          4

The two observations seems to be valid in both directions.i.e. the initial compiled cursor can belong to the bucket_id n°0 or 1 or 2. I have experimented the above tests starting by bind variable ‘j>million’ (or ‘j10000’) and have came up with the same conclusions.

In my incessant desire to make my article short and simple, I’ve decided to treat the last case (mixture bucket_ids) in Part III

PS:  cntbukt script

select
        child_number
        ,bucket_id
        ,count
    from
         v$sql_cs_histogram
    where sql_id = '&amp;1' ;

Update 11/11/2014

I did today an extra bunch of test with two distant bucket id and have updated the corresponding conclusion. When the number of cursor executions at bucket_id 2 reaches ceil(number of execution at bucket_id 0)/3) then the next execution at bucket_id 2 will mark the cursor as bind aware and compile a new execution plan

I have also attached here a Bind Aware test file on which I gathered my tests related to two distant bucket_ids (0 and 2)

1 Comment »

  1. […] 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 […]

    Pingback by Bind aware secret sauce (again) | Mohamed Houri’s Oracle Notes — September 5, 2015 @ 8:21 am | 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: