Mohamed Houri’s Oracle Notes

April 1, 2013

Sql Plan Mangement(SPM) and Adaptive Cursor Sharing(ACS) : My résumé

Filed under: Sql Plan Managment — hourim @ 9:40 am

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:

SPM Selectin

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 

About these ads

9 Comments »

  1. 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 | Reply

  2. 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 | Reply

    • Thanks Hourim . Thank you for the help .

      Comment by SysDatabase Oracle — April 2, 2013 @ 9:14 am | Reply

  3. [...] 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 | Reply

  4. [...] 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 | Reply

  5. 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 | Reply

  6. 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 | Reply

  7. 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 | Reply

  8. 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 | 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

The Rubric Theme Blog at WordPress.com.

Oracle Database 11g

Oracle Database

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

Oracle SQL Tuning Tools and Tips

SQLTXPLAIN (SQLT), TRCANLZR (TRCA), SQL Health-Check (SQLHC) and SQL Tuning Topics

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 64 other followers

%d bloggers like this: