I read Dominic Brook’s interesting article about Adaptive Cursor Sharing and SQL Plan Baseline. I, then, have read the also interesting follow-up blog 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 entitled How do adaptive cursor sharing and SQL Plan Management interact
Let me start by presenting the conclusions of these articles respectively
Dominic’s conclusion
Even with multiple plans in the a baseline, if your ACS information is flushed or ages out of the cache, you’re going to have to repeat the relevant executions required to get that ACS information back. Baselines can’t act as a shortcut to bringing back in that ACS feedback.
Coskan’s conclusion
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.
Optimizer group conclusion
If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM. But once the query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer’s choice of plans, without regard to whether this query is being optimized due to ACS.
I like very much the optimizer group conclusion:
“SPM constrains the optimizer’s choice of plans, without regard to whether this query is being optimized due to ACS”
Yes, that’s very correct. Because ACS and SPM are playing different goals:
ACS is a feature that helps the CBO adapt itself to the input bind variable in order to generate an execution plan that best fits that bind variable. ACS, given certain conditions, kicks of independently of the presence or not of a SQL Baseline
SPM is a feature which guarantees plan stability and allow plan evolution. It ensures only accepted plan will be executed whatever the technology used by the CBO to generate the best execution plan: ACS or Cardinality Feedback (even thought that Dominic and Kerry Osborne have already investigated the Interaction of SPM and Cardinality Feedback where they both demonstrated that this interaction is not as simple as it looks).
The logic of plan selection when SPM is used follows the following diagram:
In which we can see that when the best generated CBO plan is not already inside the SQL plan baseline (i.e. plan is ENABLED and ACCEPETD) then it will not be used. Instead, it will be inserted into the SQL plan history (i.e. ENABLED and not ACCEPTED) waiting to be evolved either manually using DBMS_SPM package or automatically when the Tuning Advisor consent to do so.
What does this means all in all?
In my opinion, in order to have a good collaboration between ACS and SPM, we need to load ACS plans (we have better to do that manually than automatically because they will be immediately ENABLED and ACCEPTED) and hope that all plans generated by the CBO via ACS will match the plans we have already loaded into the SPM baseline. When the CBO comes up with a plan that is not into the SPM baseline it will not be used. Instead all ENABLED and ACCEPTED plans will compete against each other and the best plan from the Baseline will be selected for use.
The optimizer group example is largely sufficient to explain what I have stated above. The goal of this article is to start from the Coskan’s article end and present a curious observation.
A picture is worth a thousand words (in order to make this post as short as possible, select against dba_sql_plan_baseline will be referred to as pbaseline)
SQL> > select 2 to_char(signature) signature 3 , sql_handle 4 , plan_name 5 , enabled 6 , accepted 7 from dba_sql_plan_baselines 8 where signature = 1292784087274697613; SIGNATURE SQL_HANDLE PLAN_NAME ENA ACC ---------------------------------------- ------------------------------ --------------------------------- 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES -> FULL SCAN 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES -> INDEX RANGE SCAN SQL> select sql_id 2 , child_number 3 , is_bind_aware 4 , is_bind_sensitive 5 , is_shareable 6 , to_char(exact_matching_signature) sig 7 , executions 8 , plan_hash_value 9 from v$sql 10 where sql_id = '731b98a8u0knf'; SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ------------------- ----------- ----------------- 731b98a8u0knf 0 N Y N 1292784087274697613 2 3625400295 731b98a8u0knf 1 Y Y Y 1292784087274697613 2 3724264953 -> bind aware 731b98a8u0knf 2 Y Y N 1292784087274697613 1 3625400295 731b98a8u0knf 3 Y Y Y 1292784087274697613 1 3625400295 -> bind aware
Two plan baselines and two shareable sql child (1 and 3) that are bind sensitive and bind aware so that when FULL bind variable (‘N1’) is used we get a FULL TABLE SCAN and when INDEX bind variable (‘Y1’) is used we get an INDEX RANGE SCAN.
- FULL scan: n=’N1’
SQL > select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL_ID 731b98a8u0knf, child number 3
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 275 (100) | |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49872 | 1461K | 275 (2) | 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
- INDEX scan : n=’Y1’
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL_ID 731b98a8u0knf, child number 4
-------------------------------------
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 | 18 | 540 | 2 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 18 | | 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
ACS produces the plan which best fits the input bind variable and SPM used that plan because it found it into its SPM baseline.
SQL> @pbaseline SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - -------------------- ----------- ----------------- 731b98a8u0knf 0 N Y N 1292784087274697613 2 3625400295 731b98a8u0knf 1 N Y N 1292784087274697613 2 3625400295 731b98a8u0knf 2 Y Y N 1292784087274697613 1 3625400295 731b98a8u0knf 3 Y Y Y 1292784087274697613 6 3724264953 -> bind aware 731b98a8u0knf 4 Y Y N 1292784087274697613 2 3625400295 731b98a8u0knf 5 Y Y Y 1292784087274697613 1 3625400295 -> bind aware
So far so good.
Let’s disturb a little bit this situation by creating an extra index on t1.
SQL> create index I2 on t1(flag, col2) compress;
Index created.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL_ID 731b98a8u0knf, child number 3
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 275 (100) | |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 50110 | 1468K | 275 (2) | 00:00:04 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
That’s nice. The FULL TABLE SCAN baseline (cf47) kicks off appropriately. But let see what plan the CBO comes up with
SQL > @pbaseline SIGNATURE SQL_HANDLE PLAN_NAME ENA ACC ------------------- ------------------------ --------------------------- ------ ---- 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd495f4ddb YES NO 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES
The CBO comes up with a new execution plan (SQL_PLAN_13w748wknkcwd495f4ddb) which has been constrained(discarded) by the SPM baseline. This new plan has been inserted into the SPM plan history (ACCEPTED =’NO’) for future evolution. The newly generated execution plan uses a INDEX FAST FULL SCAN of the new I2 index and it resembles to:
select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_13w748wknkcwd495f4ddb'));
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_11f0e4472549338d
SQL text: select count(*), max(col2) from t1 where flag = :n
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_13w748wknkcwd495f4ddb Plan id: 1230982619
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2348726875
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 46 (5) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | INDEX FAST FULL SCAN| I2 | 25000 | 732K | 46 (5) | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
But what looks strange it this
SQL> @pbaseline SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ------------------- ----------- --------------- 731b98a8u0knf 0 N Y N 1292784087274697613 2 3625400295 731b98a8u0knf 1 N Y N 1292784087274697613 2 3625400295 731b98a8u0knf 2 Y Y N 1292784087274697613 1 3625400295 731b98a8u0knf 3 N N Y 1292784087274697613 1 3724264953 -> Shareable but not bind aware 731b98a8u0knf 4 Y Y N 1292784087274697613 2 3625400295
The child cursor number 5 has gone!!! I still have only one shareable child cursor (number 3 the one for FULL TABLE SCAN) which became no bind sensitive and no bind aware. Let’s execute the case of an INDEX RANGE SCAN
SQL> exec :n := 'Y1';
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL_ID 731b98a8u0knf, child number 3
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 275 (100) | |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 50110 | 1468K | 275 (2) | 00:00:04 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
SQL> @pbaseline
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ------------------- ----------- ---------------
731b98a8u0knf 0 N Y N 1292784087274697613 8 3724264953
731b98a8u0knf 1 Y Y N 1292784087274697613 2 3625400295
731b98a8u0knf 2 N Y N 1292784087274697613 6 3625400295
731b98a8u0knf 3 N N Y 1292784087274697613 15 3724264953 -> Shareable not bind aware
731b98a8u0knf 4 Y Y N 1292784087274697613 2 3625400295
No way to make a shareable cursor bind sensitive and bind aware in order for the ACS to kick off and generate a plan that is in the SPM baseline.
Don’t tell me that this is due to the new index I2 I have created.
Will you?
SQL> drop index i2;
Index dropped.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL_ID 731b98a8u0knf, child number 3
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 275 (100) | |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49872 | 1461K | 275 (2) | 00:00:04 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL_ID 731b98a8u0knf, child number 5
-------------------------------------
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 | 18 | 540 | 2 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 18 | | 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
SQL> @pbaseline
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ------------------------------- ------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3625400295
731b98a8u0knf 1 N Y N 1292784087274697613 2 3625400295
731b98a8u0knf 2 Y Y N 1292784087274697613 1 3625400295
731b98a8u0knf 3 N Y Y 1292784087274697613 6 3724264953
731b98a8u0knf 4 Y Y N 1292784087274697613 2 3625400295
731b98a8u0knf 5 Y Y Y 1292784087274697613 1 3625400295 -> bind aware
When I dropped the index, after a warm up execution my ACS is back.
How could a creation of an extra index disturb the ACS behavior? That’s the subject of my next investigation
Mohamed Houri’s Oracle Notes 
This is a nice article . Good learning .
How did you paste the code in this blog .
Please share .
Comment by SysDatabase Oracle — April 2, 2013 @ 3:05 am |
You have to use the sql tag:
sourcecode language=””sql””
your code goes here…
/sourcecode
But you have to enclose sourcecode between [].
Comment by hourim — April 2, 2013 @ 9:03 am |
Thanks Hourim . Thank you for the help .
Comment by SysDatabase Oracle — April 2, 2013 @ 9:14 am |
[…] ended my last post about the interaction between ACS and SPM by the following […]
Pingback by What can impeach Adaptive Cursor Sharing kicking off? | Mohamed Houri’s Oracle Notes — April 4, 2013 @ 10:55 am |
[…] my last post about SQL Plan Management (SPM) I investigated the behavior of Adaptive Cursor Sharing (ACS) […]
Pingback by SPM baseline selection: how it works? | Mohamed Houri’s Oracle Notes — May 5, 2013 @ 4:20 pm |
hello hourim,
Great article and thanks for sharing this info, i had a question with regards to this. Lets say if i have ACS and bind peeking turned off(SAP system recommendation), and i have a similar situation where i have a SQL that is very bind sensative…i go put 2 baseline in place(one for full scan and other for index scan, both of the enabled and accepted)…now i run the same SQL with bind variable (which likes full scan) and that gets picked up with the baseline(full scan baseline)….next i run same SQL with different bind value(which should favor index scan)…but when i run the SQL it always tends to go towards full scan, even though i have 2 baseline for it(enabled and accepted)….
is that by design or am i missing something here? is oracle not picking up my index scan baseline(with diff bind value which favro index scan) because i have ACS tunred off?
Comment by max — December 3, 2013 @ 6:22 pm |
Max,
Forget a little bit your two SPM baselines. Think about the situation you have. The initial plan the CBO has produced has been optimized with the bind variable favoring table full scan. As far as this plan has been found in the SPM baseline it has been used. The second run using a bind variable favoring the index range scan has shared the previous plan (table full scan). Thanks to the use of bind variable there were no new plan optimization. When a new optimization (hard parse) will occur and this optimization will be done against a bind variable favoring the index range scan then the CBO will come up with a index scan plan which will be obviously found in your SPM and hence will be used. And so on. The next run of the same query using bind variable favoring full table scan will share the index range scan plan until a hard parse of the query.
ACS has been developed so that, under certain conditions, the above plan sharing situation, stop to share the same execution plans for different bind variables values.
Your case has nothing to do with SPM nor with ACS. It is a classical problem of plan sharing when using bind variables
Best regards
Comment by hourim — December 4, 2013 @ 12:00 pm |
Thanks for your feedback Hourim. One other question. you mentioned my case is nothing to do with SPM nor with ACS, are you saying that because i have ACS turned off? As if i do have ACS on(obvisouly with histogram in place for that column) then i do get what i should(when i put in bind that favors index scan i get that and when i change the bind that favors full scan i get full scan)….if what you said is because i have ACS turned off, is there any other way of making it the way the it suppose to with ACS turned off ( i mean index scan on varaible that favor index scan and same with FTS)….i was thinking can we put a SQL Patch on that SQL and make it bind_aware, would that fix this situation ? or do i have this competly wrong. Sorry for my ignorance, i come from a SQL Server background and do oracle on the side(only 2 years of experience). so trying to get around learning new stuff.
Comment by max — December 4, 2013 @ 7:44 pm |
i ran a quick test ACS turned off and bind peeking ON with a SQL Patch(bind_aware) and it behaves as it suppose to with bind that favors index scan i get that and same with FTS…
if i turn off ACS and bind peeking off then it goes back to the problem of plan sharing when using bind variables…
so with ACS and bind peeking off, any other ideas on how to fix this? or there is no solution around it ?
Comment by max — December 4, 2013 @ 8:13 pm |
Excellent Post…Hats Off…
Comment by Shadab — September 20, 2014 @ 11:35 am |
Hourim,
Before reading your blog do you recommend readers to read first the post written by Dom Brooks & Cosgan Gundogar to get the feel of the scenario about SPM & ACS. Post this blogs are understood then we can read you blog on SPM & ACS.
Regards,
Shadab
Comment by Shadab — October 6, 2014 @ 3:47 pm |
Hourim, This is not a complete test in order to prove that Optimizer wouldn’t choose a wrong plan when plans are baselined in SPM. Let us forget about ACS for a while. Have two plans (Index Scan and FTS) baselined and ensure the right plans are being used with two different input parameters. Then flush out the cursors from the shared area and then repeat the test. This is where I had faced performance issues. Optimizer was choosing FTS when Index scan was expected.
Comment by Antony — January 28, 2017 @ 11:45 am |