SQL Patch IV – Why is the SQL Patch applied but not effective? →

Adaptive Cursor Sharing with SQL Plan Baselines

March 22, 2012 17 Comments

Maybe you, like me, have read documentation that says SQL Plan Baselines work together Adaptive Cursor Sharing and wondered what this means? This is something lower down the priority list that I’ve been meaning to take a closer look at for some time.

Executive Summary
ACS and baselines do work 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 Coskan has shown in his follow-up post.

1. The bind awareness will kick back in eventually.
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 to the bind awareness i.e. three runs to have one bind aware plan, four runs to have the two bind aware plans that we preserved initially.

Is that a big deal?

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 unless you can guarantee that the information will not age out of the cache.

More Detail
Let me show you what I looked at.

Recap on ACS

Adaptive Cursor Sharing refers to cursors being bind sensitive and then bind aware.

First, a quick recap on bind sensitivity from the Performance Tuning Guide:

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.
The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
– The optimizer has peeked at the bind values to generate selectivity estimates.
– A histogram exists on the column containing the bind value
….
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.

When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values.

Setup

First up, here’s the table and data I’m going to be working with:

SQL> create table t1  
  2  (col1  number  
  3  ,col2  varchar2(50)  
  4  ,flag  varchar2(2));

Table created.

SQL> 
SQL> insert into t1  
  2  select rownum  
  3  ,      lpad('X',50,'X')  
  4  ,      case when rownum = 1  
  5              then 'Y1'
  6              when rownum = 2  
  7              then 'Y2'  
  8              when mod(rownum,2) = 0
  9              then 'N1'
 10              else 'N2'  
 11         end  
 12  from   dual  
 13  connect by rownum <= 100000;  

100000 rows created.

SQL>   
SQL> select flag, count(*) from t1 group by flag;

FL   COUNT(*)
-- ----------
N2      49999
N1      49999
Y1          1
Y2          1

SQL> create index i1 on t1 (flag);

Index created.

SQL>

(The ‘N2’ and ‘Y2’ values in the test data are not relevant to this article – something else I was looking at…)

Show that bind sensitivity requires histogram (and index so that we have an alternative execution plan)

I’ve some skew, but no stats. No stats, means no histogram. No histogram should mean no bind sensitivity.

SQL> var n varchar2(2)  
SQL> exec :n := 'N1'

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 44032 |  1290K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)

Note
-----
   - dynamic sampling used for this statement (level=4)


23 rows selected.

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 N Y 1292784087274697613             1      3724264953

SQL>

So, not bind-aware, not bind-sensitive.

Show that if we gather histogram we get bind sensitivity

If we gather some stats, then repeat the previous test:

SQL> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE 5',no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49257 |  1443K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)


19 rows selected.

SQL> 
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 Y 1292784087274697613             1      3724264953

SQL>

We have a cursor that IS bind sensitive but IS NOT bind aware.

Baselines, preserve us

If we stick that in a baseline:

SQL> declare
  2   l_op pls_integer;
  3  begin
  4   l_op :=
  5   dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>

And delete the stats:

SQL> exec dbms_stats.delete_table_stats(USER,'T1',no_invalidate=>FALSE); 

PL/SQL procedure successfully completed.

SQL>

And repeat the query:

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> select * from table(dbms_xplan.display_cursor); 

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 44032 |  1290K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)

Note
-----
   - dynamic sampling used for this statement (level=4)
   - SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement


24 rows selected.

SQL> 
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 N Y 1292784087274697613             1      3724264953

SQL>

then we have a plan that is NOT bind sensitive

And if we regather stats and repeat, it is back to being bind sensitive:

SQL> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE 5',no_invalidate=>FALSE); 

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 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.

SQL> 
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 Y 1292784087274697613             1      3724264953

SQL>

So, in summary this clarifies that bind sensitivity is not an attribute that is preserved in a SQL Plan Baseline.

How then do ACS and SQL Plan Baselines work together?

From reading the documentation, you might get the impression – or at least I did – that there’s more to the combination of baselines and ACS than there is.

Let’s get two BIND AWARE plans.

Continuing on from above, let me delete the baseline, flush and start again with just the table and the data.

We start with one query that selects a large proportion of the data with a FTS:

SQL> var n varchar2(2)  
SQL> exec :n := 'N1' 

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)


19 rows selected.

SQL>

Then, let’s use a different bind that selects a much smaller proportion of the data, initially with a FTS as well because that’s what’s parsed and shareable:

SQL> exec :n := 'Y1'

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)


19 rows selected.

SQL>

But after a couple of executions we get a more appropriate plan, thanks to ACS:

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |     9 |   270 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     9 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("FLAG"=:N)


20 rows selected.

SQL>

And we see in V$SQL that we have the original cursor marked as NOT SHAREABLE and a new cursor marked now as BIND AWARE.

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      3724264953
731b98a8u0knf            1 Y Y Y 1292784087274697613             1      3625400295

SQL>

And if we execute the SQL with the initial bind that favoured a FTS, we do get that FTS as is appropriate but we have a new child cursor that is also bind aware:

SQL> exec :n := 'N1'  

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)


19 rows selected.

SQL> 
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      3724264953
731b98a8u0knf            1 Y Y Y 1292784087274697613             1      3625400295
731b98a8u0knf            2 Y Y Y 1292784087274697613             1      3724264953

SQL>

Stick the bind-aware plans in a baseline

Now, let’s baseline the two plans:

SQL> declare
  2   l_op pls_integer;
  3  begin
  4   l_op :=
  5   dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>

And validate that that was successful because DBMS_SQLTUNE.LOAD_PLANS_FROM_CURSOR_CACHE does not error if no plans were loaded:

SQL> select to_char(signature) signature, sql_handle, plan_name, enabled, accepted, reproduced
  2  from   dba_sql_plan_baselines 
  3  where   signature = 1292784087274697613;

SIGNATURE                SQL_HANDLE               PLAN_NAME                      ENA ACC REP
------------------------ ------------------------ ----------------------------
1292784087274697613          SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES YES
1292784087274697613          SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES YES

SQL>

Rinse and repeat

Now lets flush the shared pool and see what happens when we run those statements which are meant to be
bind sensitive.

Firstly, if we initially run with the bind for the FTS, that’s what we get. It’s no surprise:

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';

no rows selected

SQL> exec :n := 'N1'

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> select * from table(dbms_xplan.display_cursor); 

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 0

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 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.

SQL>

And if we follow-up with the index-favouring value:

SQL> exec :n := 'Y1'

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |      |       |       |   322 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 51829 |  1518K|   322   (3)| 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.

SQL> 
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 Y 1292784087274697613             2      3724264953

SQL>

We’re back where we started. We’re using one of our baseline plans but we’ve lost our bind-awareness.

What do you mean we’ve lost our bind-awareness?

Well, previously we did all that hard work with multiple executions to get our bind-awareness. The baselines have preserved and are enforcing our ACCEPTED plans but we have to repeat the multiple executions to get back the bind-awareness.

Maybe it helps if we remind ourselves what the optimizer does when baseline plans are present.

At hard parse time, the optimizer will peek at the binds and generate a best cost plan anyway, regardless of the presence of a baselined plan.

If the best cost plan matches one that’s in a baseline, then we get a note in an optimizer trace file along the lines of:

SPM: cost-based plan found in the plan baseline, planId = 2239163167

If we don’t have the best cost plan in the baseline then we get a line in the optimizer trace along the lines of:

SPM: planId's of plan baseline are: 1634389831
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1292784087274697613
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 1634389831
  Bind List follows:
bind value pos=1 name=
    bind(0x2ad9a1c7cd40): flags=0 typ=1 data=0x2ad9a285af88 len=2
      bind_strval="Y2"
      bind in binary format:
2AD9A285AF80                   09E83259                   [Y2..]

and a new plan is added to the baseline as UNACCEPTED and stored for future evolution whilst a baseline is then used to generate the parsed plan.

So, whilst we do have our two baselined plans, the initial hard parse peeks at the binds and gets ‘N1’, generates the best cost plan which matches one of the baselines – job done. (And if the initial hard parse had peeked and found value ‘Y1’ we would have had our baselined index plan).

The subsequent execution of the cursor with a different value falls into the standard shareable SQL scenario – the initial cursor was parsed with peeked binds, the values of which do not suit our subsequent execution until ACS kicks in as previously.

If we return to our sql statement above and re-execute with the same bind that should be using an index, we see the switch from baseline plan SQL_PLAN_13w748wknkcwd616acf47 to SQL_PLAN_13w748wknkcwd8576eb1f:

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> select * from table(dbms_xplan.display_cursor); 

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  731b98a8u0knf, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

SQL> 
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      3724264953

SQL> select count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 
SQL> 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   |     9 |   270 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     9 |       |     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.

SQL> 
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      3724264953
731b98a8u0knf            1 N Y Y 1292784087274697613              1      3625400295

SQL> exec :n := 'N1' 

PL/SQL procedure successfully completed.

SQL>

Again, then marking of child 0 as NOT SHAREABLE, etc, etc.

So, what are you saying?

Well, not a lot really.

In summary, from the documentation, you may get the impression that there’s more to the combination of ACS and baselines than there really is.

ACS and baselines work alongside each other but somewhat independently.
ACS can provide SPM with bind-sensitive plans to evolve.
Whilst SPM can store the multiple plans that ACS requires.
But ACS attributes are not stored in SPM.

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.

BOTTOM LINE: if you’re using binds when you should be using literals, there’s no silver bullet

I’ve made no mention here of views:

V$SQL_CS_HISTOGRAM
V$SQL_CS_SELECTIVITY
V$SQL_CS_STATISTICS

but these are worthy of further observation if you want to investigate ACS. However that was not the point of this post.

Filed under 11g, 11gR2, acs, adaptive cursor sharing, oracle, sql baselines, sql plan management

17 Responses to Adaptive Cursor Sharing with SQL Plan Baselines

Pingback: Adaptive Cursor Sharing with SQL Plan Baselines – Bind Sensitiveness « Coskan’s Approach to Oracle
coskan says:

March 23, 2012 at 11:21 am

Instead of dealing with limits of wordpress commenting I wrote a blog post as a reply to mention the behavior I observe for bind awareness.

Adaptive Cursor Sharing with SQL Plan Baselines – Bind Sensitiveness

Reply
- Dom Brooks says:
  
  March 23, 2012 at 11:59 am
  
  I’ve added some random bolded words for you 🙂
  
  I’m pretty sure you’ve proved my point in your post – i.e. we need to do all the multiple executions again to get back the bind awareness, etc.
  
  It was difficult in this long post to put across the point I was trying to make. I think you’re probably right that the documentation says that ACS is in memory only and so all I’ve really done is shown that that is the case.
  
  Cheers,
  Dominic
  
  Reply
Dom Brooks says:

March 23, 2012 at 12:18 pm

I’ved edited the article and incorporated this comment into the initial summary.

Reply
Lazar says:

April 12, 2012 at 8:10 pm

Once pinned sql baselines are bind sensitive and optimizer knows whish SPM to use for with bind variable. The only way to get real information is from trace and event 1053 level 1. Once SMB is set we can’t rely on cursor cache. Here is my example

Reply
- Dom Brooks says:
  
  April 12, 2012 at 8:17 pm
  
  Lazar,
  
  Thanks for the comment.
  
  I’m interested to see your example particulary as I think I’ve shown above that the bind sensitivity is in memory (in the shared pool) and as soon as you lose that, you lose the sensitivity until you execute enough times with the appropriate bind sets to regain it. SPM contains nothing about bind sensitivity.
  
  If you put your example in the previous comment then it didn’t make it through. Please try again or otherwise email to me – dombrooks@hotmail.com – and I’ll get it in.
  
  Cheers,
  Dominic
  
  Reply
Lazar says:

April 13, 2012 at 4:49 pm

Dominic, you can see example at http://oracleperformances.blogspot.com/
Thanks,
Lazar

Reply
- Dom Brooks says:
  
  April 13, 2012 at 9:43 pm
  
  Hi Lazar,
  
  I’ve left a comment on your blog.
  
  Interesting test case but it doesn’t show what you think it shows. The cursor cache does not lie.
  The initial cursor with bind 50000 is invalidated, bind 25000 does a further hard parse, both hard parses peek at their relevant bind variables and therefore get the appropriate plan.
  
  Baselines do not store bind awareness/sensitivity.
  
  Cheers,
  Dominic
  
  Reply
Lazar says:

April 17, 2012 at 5:56 pm

Hi Dominic,
I’ve spent some more time testing this. You are absolutely right. More then likely cursor aged out before I captured it. This is true for first execution after flushing shared pool and with baseline created.
Thanks for clarifying this,
Lazar

Reply
- Dom Brooks says:
  
  April 17, 2012 at 6:10 pm
  
  Lazar,
  
  Thanks for the further investigation and the feedback – excellent.
  
  Cheers,
  Dominic
  
  P.S. Shame you took your original article down.
  
  Reply

Neil Johnson says:

January 30, 2013 at 10:41 pm

Hi Dom,
Nice post.
As far as bind peeking threats go I’ve always looked on partition keys in the same way as histograms. Your post got me thinking about whether adaptive cursor sharing also saw them as a threat. So I adapted (no pun intended) your test code slightly by partitioning “T1”.

create table t1
(col1  number
,col2  varchar2(50) 
,flag  varchar2(2))
partition by list (flag)
(
 partition flagy1 values ('Y1')
,partition flagy2 values ('Y2')
,partition flagn1 values ('N1')
,partition flagn2 values ('N2')
);

As expected with this table I saw the same execution plan as you but my cursor was not marked as bind sensitive:

var n varchar2(2)
exec :n := 'N1'
select count(*), max(col2) from t1 where flag = :n;

SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 1293629841

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |       |       |   296 (100)|          |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    30 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      | 36684 |  1074K|   296   (1)| 00:00:04 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | T1   | 36684 |  1074K|   296   (1)| 00:00:04 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)



SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - -------------------- ---------- ---------------
731b98a8u0knf            0 N N Y 1292784087274697613           1      1293629841

This was disappointing. So I collected statistics on the table with NO histograms and ran the query again.

exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1',no_invalidate=>false);

SQL_ID        CHILD_NUMBER I I I INVALIDATIONS SIG                  EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ------------- -------------------- ---------- ---------------
731b98a8u0knf            0 N Y Y             1 1292784087274697613           1      1293629841

And now the cursor is marked as bind sensitive.

I played around with this a little more and as long as I have column statistics in user_tab_columns then I get bind sensitivity, even if the partition I am hitting has no stats. If I gather stats at the partition level only so user_tab_columns is empty then IS_BIND_SENSITVE=N.

So this is pleasing as I’m unlikely to have no global stats on any tables and I’ll get ACS when I most need it – when new partitions are added.

Thanks for your post and the thought it stimulated

Rgds

Neil

Dom Brooks says:

January 31, 2013 at 11:05 am

Thanks for the feedback Neil.

I stuck to th more obvious setup of ACS in order to look at the combination of ACS and baselines which was ultimately disappointing.

Partitioning and different levels of partition stats sounds like another interesting aspect of ACS worthy of further investigation / more detail.

Cheers,
Dominic

Reply

Pingback: Sql Plan Mangement(SPM) and Adaptive Cursor Sharing(ACS) : My résumé | Mohamed Houri’s Oracle Notes
Pingback: What can impeach Adaptive Cursor Sharing kicking off? | Mohamed Houri’s Oracle Notes
Pingback: Oracle SQL Plan Management, Literal Values, and Cursor Sharing | The OLD PRO - DBA Blog
Shadab says:

October 8, 2014 at 3:13 pm

Hi Dom,

Please can you confirm why the optimizer is favoring the index.

SQL> var n varchar2(2)
SQL>
SQL> exec :n := ‘N1’

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
———- ————————————————–
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> 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 | | | | 273 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 50738 | 1486K| 273 (1)| 00:00:04 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“FLAG”=:N)

Note
—–
– SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement

23 rows selected.

SQL>
SQL> exec :n := ‘Y1’

PL/SQL procedure successfully completed.

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
———- ————————————————–
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL>
SQL>
SQL> 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 | | | | 273 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 50738 | 1486K| 273 (1)| 00:00:04 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“FLAG”=:N)

Note
—–
– SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement

23 rows selected.

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
———- ————————————————–
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL>
SQL> 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 | | | | 273 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 50738 | 1486K| 273 (1)| 00:00:04 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“FLAG”=:N)

Note
—–
– SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement

23 rows selected.

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
———- ————————————————–
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID 731b98a8u0knf, child number 2

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 2
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)

8 rows selected.

Regards,
Shadab

Reply
- Dom Brooks says:
  
  October 9, 2014 at 4:27 pm
  
  > Please can you confirm why the optimizer is favoring the index.
  For value Y1?
  Because there’s only 1 row in 100,000 to fetch, compared to 49,999 for value N1.
  
  Reply

OraStory

17 Responses to Adaptive Cursor Sharing with SQL Plan Baselines

Leave a comment Cancel reply

About Me

You Might Like

Recent Posts

Recent Comments

Top Posts

Blogroll

Documentation

Forums

Metalink Articles (requires login)

Other Websites

Disclaimer

OraStory

Adaptive Cursor Sharing with SQL Plan Baselines

Share this:

Related

17 Responses to Adaptive Cursor Sharing with SQL Plan Baselines

Leave a comment Cancel reply

About Me

You Might Like

Recent Posts

Recent Comments

Top Posts

Blogroll

Documentation

Forums

Metalink Articles (requires login)

Other Websites

Disclaimer