Mohamed Houri’s Oracle Notes

December 10, 2014

Bind Aware – Part III

Filed under: Oracle — hourim @ 12:30 pm

After having explained

  • In Part I, how Oracle is monitoring a cursor candidate for a bind aware status via the number of processed rows each bind variable value is generating and how this is linked with the count of the three different bucket_id
  • In Part II, how Oracle is exploiting the tandem (bucket_id, count) to decide when time has come to make a cursor bind aware.Part in which I think I have succeeded to figure out the secret sauce the Adaptive Cursor Sharing Layer code is using to mark a cursor bind aware in two cases which are (a) when only to adjacent bucket_id are concerned (the count of the third bucket_id = 0) and (b) when only to distant bucket_id are concerned (that is the count of bucket_id n°1 = 0).

I left the third case which concerns all bucket_id  (that is the count of all bucket_id != 0) for the current blog article.

I will immediately temper your enthusiasm and say that unfortunately I didn’t succeed to figure out the secret sauce Oracle is using to mark a cursor bind aware in this third case. Nevertheless, this failure didn’t pre-empted me to share with you all the observations and tests I have done hoping that someone will take over the task and come up with a nice conclusion

You will have pointed out that I’ve also made an exploit by reserving two articles on the Adaptive Cursor Sharing without showing a single execution plan! This is because I aimed through this series of articles to show how Oracle is internally managing and monitoring this feature rather than to show its goal which is to produce several optimal execution plans for a SQL statement using bind variables and fulfilling particular pre-requisites.

Finally, you might have also noticed that, despite 3 articles, I still have not exposed nor explain what happens when the cursor is finally made bind aware. In fact once a cursor is made bind aware there is a new piece of internal Oracle code that takes over the monitoring job. This is the Extended Cursor Sharing (ECS) feature which monitors the selectivity (or a range of selectivities) for each child cursor that has been previously made bind aware by ACS internal code. While the ACS feature uses the tandem (bucket_id, count) for its internal secret sauce (half secret from now and on), the ECS feature is based on the v$sql_cs_selectivity view.

Back now to the content of this third and last part of the series which exposes my tests when trying to decipher the ACS working mechanism when all bucket_id are concerned

I have decomposed my tests in 3 categories (all bucket_id have a count != 0)

  • The first case corresponds to a cursor marked bind aware following an increment of the count of bucket_id n°0
  • The second case is when a cursor is marked bind aware following an increment of the count of bucket_id n°1
  • The third category corresponds to a cursor marked bind aware following an increment the count of the bucket_id n°2

Based on those different observations, I was aiming to derive an empirical algorithm that might explain how Oracle is taking its decision to mark a cursor bind aware when all bucket-ids are concerned. Unfortunately, in contrast to the case exposed in Part II of this series, I have been unable to derive that algorithm. Nevertheless, I’ve decided to share with you find all my tests.

1.    First category of tests

As exposed above the first category of my tests concerns a cursor marked bind aware following an execution done using a bind variable that increments the count of bucket_id n°0. For example I have the below situation:

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID  COUNT
------------ ---------- ----------
0             0          5 ---> +1 execution
0             1          8
0             2          3

Looking at the above picture, you can understand that I’ve managed to execute my query in the following execution order

  • bind variable value ‘j10000’ 8 times  (increment count of bucket_id 1 to 8)
  • bind variable value ‘j100’ 5 times    (increment count of bucket_id 0 to 5)
  • bind variable value ‘j>million’ 3 times (increment count of bucket_id 2 to 3)

Of course these executions have been done in a way to avoid two adjacent bucket_id reaching the same count while the remaining bucket_id has a count =0. This is why I managed to have all bucket_id incremented to avoid bind aware situations exposed in Part II. To accomplish this you can for example start by executing your query using the ‘j10000’ bind variable 8 times then jump to the adjacent bucket_id n°0 using bind variable ‘j100’ execute it 5 times then go to the other bind variable value ‘j>million’ and execute it 3 times. Proceeding as such you will arrive exactly at the above situation exposed in v$sql_cs_histogram for the 6f6wzmu3yzm43 sql_id.

At this stage of the experiment, if you execute the same query using the first bind variable value ‘j100’, your will see that Oracle has made your cursor bind aware as shown below (a new child cursor n°1 appears):

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID COUNT
------------ ---------- ----------
1             0          1
1             1          0
1             2          0
0             0          5
0             1          8
0             2          3

2.    Second category of tests

The second category of my tests is the one that corresponds to a cursor becoming bind aware following an execution at bucket_id n° 1

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID COUNT
------------ ---------- ----------
0             0          8
0             1          3 + 1 execution
0             2          2

CHILD_NUMBER  BUCKET_ID COUNT
------------ ---------- ----------
1             0          0
1             1          1
1             2          0
0             0          8
0             1          3
0             2          2

The same precaution have been taken so that bind aware reasons exposed in Part II of the series are avoided before having the count of all bucket_id incremented

3.    Third category of tests

Finally the third category corresponds to a cursor becoming bind aware following an execution at bucket_id n° 2 as shown below:

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID  COUNT
------------ ---------- ----------
0             0          8
0             1          3
0             2          2  ----> + 1 execution

CHILD_NUMBER  BUCKET_ID  COUNT
------------ ---------- ----------
1             0          0
1             1          0
1             2          1
0             0          8
0             1          3
0             2          2

Looking at the above situation there is no apparent clue indicating what secret sauce ACS is using to mark a cursor bind aware when all bucket_id are concerned.

I have attached this tests with 3 bucket document where you can find several similar cases I have done and that have not put me in the right direction as well.

I hope that these 3 articles help someone.

Advertisements

2 Comments »

  1. 1.
    count[bucket_id=1]<count[bucket_id=0]+count[bucket_id=2]

    2.
    ceil((max(count[bucket_id=0],count[bucket_id=2])-count[bucket_id=1])/3)<min(count[bucket_id=0],count[bucket_id=2])

    Comment by zhwsh — December 16, 2014 @ 1:31 am | Reply

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

    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

Create a free website or 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: