Today I saw a post from my friend Dominic Brooks (orastory) whom I work together at the same place for different environments (Wish he is working on the environments I look after together we could have made a great performance team :(). His post is about how Adaptive Cursor Sharing and SQL Plan Baselines work together . Post looks long but easy to read and enlightening about the issue with one small addition/correction/observation needed. I think he covered well enough but finished his tests a bit soon. I will not repeat what he has done but I need to cover the additional observation/correction part. After the introduction, half way through wrote a section “How then do ACS and SQL Plan Baselines work together?” (wish he uses some marking for subtopics – thats why we have bold and headings Dom). At the end of that section he says “We’re using one of our baseline plans but we’ve lost our bind-awareness.” which is not complete truth according to my tests on 11.2.0.1.
I assume table is created, index is created, stats are gathered, two sql_plan_baseline is generated and cursor is flushed as below
HR@ORACOS> select sql_id
, child_number
, is_bind_aware
, is_bind_sensitive
, is_shareable
, to_char(exact_matching_signature) sig
, executions
, plan_hash_value,
sql_plan_baseline
from v$sql
2 3 4 5 6 7 8 9 10 11 where sql_id = '731b98a8u0knf';
no rows selected
HR@ORACOS> select to_char(signature) signature, sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
2 3 where signature = 1292784087274697613;
SIGNATURE SQL_HANDLE PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ ------------------------------ --- ---
1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES
1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES
First Run — Full table scan —To make the post short I named v$sql query as sqlstat
var n varchar2(2)
exec :n := 'N1'
select count(*), max(col2) from t1 where flag = :n;
HR@ORACOS>
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49298 | 1444K| 203 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
23 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y Y 1292784087274697613 1 3724264953 SQL_PLAN_13w748wknkcwd616acf47
Statement is not bind aware which is expected but is bind sensitive and sharable and using baseline
Second Run — Index Scan
exec :n := 'Y1'
select count(*), max(col2) from t1 where flag = :n;
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49298 | 1444K| 203 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
23 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y Y 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
Statement is not bind aware which is “normal” as Optimizer group already wrote about in their optimizer blog and cursor is still sharable.
Optimizer group said
"Oracle monitors the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different. Based on this difference, Oracle "adapts" its behavior so that the same plan is not always shared for this query. Hence on the next execution, a new plan is generated based on the current bind value."
Lets see if it is like that when baselines are in place
Third Run — Index Scan
exec :n := 'Y1'
select count(*), max(col2) from t1 where flag = :n;
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement
24 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y Y 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 1 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
Now on the second run of index favouring bind, new cursor is generated and bind aware also using the index scan baseline which is correct behaviour according to the optimizer groups statement.
Fourth Run — Table Scan
exec :n := 'N1'
select count(*), max(col2) from t1 where flag = :n;
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49298 | 1444K| 203 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
23 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 1 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf 2 Y Y Y 1292784087274697613 1 3724264953 SQL_PLAN_13w748wknkcwd616acf47
Now on the second run of table scan again new cursor is generated and again bind aware also using the right full table scan baseline. Please also look at the first cursor which is not sharable anymore.
Fifth Run — Index Scan
exec :n := 'Y1'
select count(*), max(col2) from t1 where flag = :n;
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement
24 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 2 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf 2 Y Y Y 1292784087274697613 1 3724264953 SQL_PLAN_13w748wknkcwd616acf47
ORacle uses correct child cursor which is first child and hasn’t got any of is bind awareness or sensitivity changed
Sixth Run — Table Scan
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49298 | 1444K| 203 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
23 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 2 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf 2 Y Y Y 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
ORacle uses correct child cursor which is second child and hasn’t got any of is bind awareness or sensitivity changed
Bonus Run with different Bind N2 which should again favour full scan
exec :n := 'N2'
select count(*), max(col2) from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);
@sqlstat
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS>
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49298 | 1444K| 203 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
23 rows selected.
HR@ORACOS> HR@ORACOS>
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 2 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf 2 Y Y Y 1292784087274697613 3 3724264953 SQL_PLAN_13w748wknkcwd616acf47
Oracle is doing the right thing by using the child no 1 with right baseline
Bonus Run with different Bind Y2 which should again favour Index Scan
exec :n := 'Y2'
select count(*), max(col2) from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);
HR@ORACOS>
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS>
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement
24 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 3 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf 2 Y Y Y 1292784087274697613 3 3724264953 SQL_PLAN_13w748wknkcwd616acf47
Oracle is doing the right thing by using the second child no 2 with right baseline.
Long story short I personally think they work perfectly fine together but I also wish if Oracle gives option to hold this runtime monitoring info in SYSAUX for env where people can accommodate more data in SYSAUX. This will save a lot of time for the initial loads.
To do a fair comparison I needed to do the tests on 11.2.0.1 but Don’t have 11.2.0.2 right now but I’m assuming it should be the same (I think I lost my appetite of installing new oracle version just for fun of it 🙂 )
Coskan's Approach to Oracle 
They do work fine together.
But I know when I read the documentation, I came away with some questions that I wanted to look at. I wondered if they did more together than they do.
For example, did baselines preserve some of the ACS not-so-secret sauce such that if the ACS information aged or was flushed out of the cache, we didn’t have to repeat the multiple executions to get the bind awareness back?
And the answer to that is no. Which I’m glad you’ve shown above.
1. The bind awareness will kick back in.
2. The baseline mechanism will allow the optimizer to use the ACS feedback and consider both plans in the baseline.
3. But we need to repeat the executions to get back i.e. three runs to have one bind aware plan, four runs to have the two bind aware plans that we preserved initially.
I know it’s not necessarily a massive deal is it?
We’ve had to do one extra execution each of the statement but if that initial execution with the “wrong” plan was a big enough performance problem, then the implications could be significant.
And the two mechanisms that I thought might be designed to work together to address it, don’t.
Comment by Dom Brooks — March 23, 2012 @ 12:10 pm
[…] And the answer to that is no. Which I’m glad Coskan has shown in his follow-up post. […]
Pingback by Adaptive Cursor Sharing with SQL Plan Baselines « OraStory — March 24, 2012 @ 8:13 am
[…] is how he did it with a little bit of polishing. (I will be using the baseline generated on this post – dbms_xplan.display_sql_plan_baseline works for this but not important for the post […]
Pingback by When dbms_xplan.display_sql_plan_baseline fails to show the plan « Coskan’s Approach to Oracle — April 11, 2012 @ 8:45 pm
I do agree with Dom Brooks as I have seen personally many such a performance issues due to bind variable peeking and ACS can’t address it at the very first execution of the SQL with a poor plan.I have a history of SQLs used to run in seconds took many hours due to different bind values with poor plan.
To me,ACS is helpless during those fire fighting time.
Comment by anthonydba — May 2, 2012 @ 11:07 am
[…] article written by one of those modest and smart Oracle guys Coskan Gundogar which he has entitled Adaptive Cursor Sharing with SQL Plan Baselines – Bind Sensitiveness. Finally, I have ended up my “SPM-ACS collaboration Giro” with the Optimizer blog article […]
Pingback by Sql Plan Mangement(SPM) and Adaptive Cursor Sharing(ACS) : My résumé | Mohamed Houri’s Oracle Notes — April 1, 2013 @ 9:41 am
Great blog here! Additionally your web site rather a
lot up very fast! What web host aare you using?
Can I am getting your associate link for your host? I
desire my site loaded up as fast as yours lol
Comment by Casimira — June 25, 2014 @ 8:00 am
Actually this information was quite interesting on content marketing. As we heard about the quality content fresh content bla bla like that. Yes i agree with that one but apart from that we have to make the news in which they are looking for. Okay i am satisfied with your posts. Thank you
Comment by jeslin — February 7, 2017 @ 12:17 pm