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.