Strategies for Minimising SQL Execution Plan Instability

Execution Plan Instability – What is the problem?

The Oracle Optimizer is a complex piece of software and with every release it becomes more complex.

RBO

In the beginning, the Optimizer was rule-based.

The Optimizer had a ranked list of heuristics used to optimize a query, picking the lowest ranked rule available.

This rule-based mode, whilst still in use with some internal Oracle dictionary queries, has been unsupported since version 10.1.

This means that no code changes have been officially made to the RBO and no bug fixes are provided. There are many features that the RBO is unaware of.

Applications should not still be using the rule-based optimizer.

CBO

The Cost-Based Optimizer is designed to evaluate a number of execution plans and pick the one estimated to be the fastest.

Many of the CBO features are designed to combat common optimization problems and many of these problems occur where development teams are not necessarily aware of the full implications of the features that they are using.

These built-in default behaviours roughly conform to the infamous 80%:20% rule in that most of the time they do a good job but they are not infallible.

Bind Variables, Literals and Histograms

Most of the features which are deliberately designed such that plan instability is difficult to avoid stem from the decision to use bind variables or literals (NOT that they should be mutually exclusive) and the interaction of the former with histograms.

In article 1 of his Philosophy series, Jonathan Lewis neatly sums this up:

Histograms and bind variables exist for diametrically opposed reasons – they won’t work well together without help

Fundamentally, bind variables exist to provide shareable plans.

Bind variables should be used where we are not interested in getting specific optimizations for differing parameters.

Literals should be used where we want the Optimizer to pay particular attention to data distribution and skew for the specific parameters supplied.

A SQL statement will/should often have some parameters which should be literals and some which should be binds.

From this point onwards, there has been a whole raft of features designed to treat the misuse of one or the other.

In Oracle 8.1.6, histograms were introduced.

Histograms exist to provide specific data distribution information, particularly relevant to specific parameters.

Also, in that version, we got CURSOR_SHARING – targeted at applications using literals instead of binds such that SQL which was identical part from the use of binds was rewritten to use sytem-generated bind variables.

Then in 9.2, we got bind variable peeking.

This feature was introduced so that the optimizer could peek at the values supplied at parse time and use data distribution information specific to these parse-time values to generate an execution plan which suited those values.

In addition and at the same time, through these various versions to present day, we have had the default behaviour of DBMS_STATS statistic gathering to let the database decide which columns it will create histograms on, based on the SQL which has been running.

This means that new histograms can suddenly spring up – or existing histograms unexpectedly disappear – on all sorts of columns. This can be problematic on columns with large numbers of distinct values AND particularly so on join cardinalities where there may be a mismatch of histograms on both sides of the join.

Ever since this point, we have had a conflict of interest in feature usage and an ever increasing number of additional functionality to battle against this conflict – adaptive cursor sharing, cardinality feedback, etc, etc

Finally, the education message got blurred or lost somewhere along the line to the extent that a lot of systems blindly overuse bind variables because of the perceived performance impact of using literals.

This situation is not helped by the fact PL/SQL is designed to encourage bind variables.

Using supplied parameters as literals means using some construct of dynamic SQL, not difficult but nevertheless an added complexity and also another feature which is often blindly discouraged.

SQL Execution Plan Instability – Is SPM a viable approach?

SPM Overview

In Oracle 11g, Oracle introduced SQL Plan Baselines as a SQL Plan Management feature.

The core attraction of this functionality is that you can “lock in” a particular plan or plans for a SQL statement. This stores a set of outline hints and a specific plan hash value in relation to a specific SQL signature.

The Optimizer then uses that set of hints to try to reproduce the desired plan. If it can it will, if it can’t it will reject the hintset.

Additionally, the Optimizer completes its best-cost execution plan optimization anyway so that it can provide the ability to do a controlled evolution of baselined plans in the event that the lower-cost plan that it would have used is different and better performing than the baselined plan.

To use this the database parameters (session or system) just need to be configured to capture plans into a baseline and then use them.

There is flexibility to this capture/use. You can capture everything as it is generated; you could capture everything from memory now and/or at regular intervals and import into a baseline; you could capture everything from AWR into a SQL Tuning Set and then import into a baseline; or you could capture everything from another environment and export/import it into another.

And at any point, you can turn off capture and continue to use those which you currently have – this usage continues to capture any lower cost plans that the optimizer would have generated for any existing baselined plans

For a more detailed look at SPM and a comparison with SQL Profiles, see documentation.

Sounds good – why isn’t it the silver bullet for system-wide stability?

This approach might be good enough, but it is not a cast-iron guarantee of system-wide stability.

There are a number of reasons why not.

New & Changed SQL

Firstly, you need to have captured all your plans already. If you get brand new SQL, then there will be nothing in SPM.

Depending on your application, this may be a significant concern.

For example, consider an application making heavy usage of Hibernate which generates SQL statements.

A minor change in the domain model can mean a change of system-generated table alias in many statements.

As a result, you may suddenly get a lot of brand new SQL and significant numbers of baselined statements which you will now never see again.

What are the baselined plans based on? Are they the best plans? The only plans ever used?

If you suddenly baseline the plans for a large number of SQL statements, you are dictating which plan is to be used.

The plans will be based on the parsed plans in memory or in AWR at the time.
Are these the best plans?

Does/should this SQL statement always use this plan?

Are there normally multiple plans for different bindsets?

What if you normally get benefit from adapative cursor sharing?

ACS and baselines

What if your application benefits from adapative cursor sharing?

Sure, you can baseline multiple plans but these plans have no ACS information.

As soon as that ACS information is no longer in memory (as happens), there is no shortcut in a baseline to regain that, you still have to have the multiple executions required for the optimizer to recognize that which plans to use for which bindsets.

Parsing overhead

Widespread usage of baselines might, depending on your system performance profile, have a significant impact on the parsing resources.

This is because it always generates a best-cost plan anyway.

Then if that is not the baselined plan, it will use the baselined hintset to try to generate the specific plan hash.

In addition, it that is not possible, it will use just the optimizer_features_enable hint to try to generate the required plan.

So, you might in a heavily-baselined system to be doing 2x the parse work of a non-baselined system.

This might well be easily accommodated but there are systems where this would cause a problem.

Integration with development and testing processes

A SQL Plan Baseline is tied to a SQL statement based on the statement’s EXACT_MATCHING_SIGNATURE – a hash of the SQL statement which has been case and space normalized.

If a SQL statement materially changes, the baseline no longer applies.

How aware are developers of the presence of baselines?

And how to integrate with the development process?

How will our release process deal with baselines?

And if baselining large numbers of plans is being considered, then we have to think about where these will be generated.

The natural implication (of widespread baseline usage) is that new functionality being promoted to Prod would have a set of tested, baselined plans accompanying it and these would presumably have to be generated in a Prod-like environment which included Prod-like volumes.

SQL Execution Plan Instability – Decision Time

There is a decision to be made.

(And/or perhaps there is often a conceptual over-simplification by senior management to combat? Or at least a lack of deep understanding of the beast that we’re dealing with here?)

Do you want the Optimizer to try to get a better execution plan sometimes?

If the answer is yes, then you have to accept that it will get it wrong from time to time.

In particular, the various feedback and adaptive mechanisms are designed to recognize that they have got it wrong.

BUT they need that problematic execution in the the first place – sometimes more than one – to recognize that fact.

That one problematic execution could be your next Priority 1 incident.

In addition, the feedback mechanism is not perfect and it still can make subsequent executions worse in some circumstances.

SQL Execution Plan Instability – Turn it off?

IF your primary goal is plan stability – and I think many teams would claim this is this their goal but they do not embrace the implications of this – then perhaps a better decision is to turn off the various features which cause or combine to cause most of the problems of instability.

Appropriate usage of binds, appropriate usage of literals

Choose whether to use a bind variable or a literal as is appropriate for the value / column / SQL statement.

A SQL statement might have a mix of both.

DBMS_STATS defaults

A METHOD_OPT of FOR ALL INDEXED COLUMNS SIZE AUTO is an immediate red flag. This is never a good setting.

FOR ALL COLUMNS SIZE AUTO without table-level preferences (SET_TABLE_PREFS) is another red flag.

As an interim step, consider use FOR ALL COLUMNS SIZE REPEAT to lock in the current histogram usage.

The end goal should be to have table level preferences set for all tables.

This relies on knowing your data, your data distribution, your code, and knowing which histograms make sense (i.e. for skewed columns) – it will be far fewer than gathered by default.

For columns with significant numbers of distinct skew, it may be necessary to manually craft the histograms.

Volatile tables

Volatile tables should have stats set to an appropriate setting to generate appropriate plans for all situations and then those stats should be locked.

Stats which are gathered at any point during the volatility cycle may be good or may be problematic.

Similarly dynamic sampling can only see the data at the time of hard parse – you might be lucky and this is better than stats which say 0 rows but it can be a time bomb.

Turn off optimizer features

Turning off optimizer features might be best done via a LOGON trigger and turning such off for a subset of application users. These features include:

  • Bind Variable Peeking – off via _optim_peek_user_binds = false
  • Cardinality Feedback and ACS – should be disabled by turning off bind variable peeking but off via _optimizer_use_feedback = false, _optimizer_adaptive_cursor_sharing = false, _optimizer_extended_cursor_sharing_rel = “none”
  • Dynamic Sampling – optimizer_dynamic_sampling to 0
  • 12c additional adaptive features – e.g. adaptive execution plans

Additionally it probably makes sense to turn off the adaptive direct path read behaviour or anything with the word adaptive or dynamic in it or associated to it

This functionality decides on whether to do full segment scans via the buffer cache or not and the behaviour is a runtime decision depending on the size of the object relative to the buffer cache AND depending on how much of the segment is currently in the cache.

  • Adaptive direct path reads – _serial_direct_read = always

All too often I’ve seen a concurrently executed SQL statement switch to a “bad” plan involving a full table scan delivered via direct path reads stress out the IO subsystem because of the number of concurrent executions of that query which then affects performance across the DB.

Caveat

The actions above are still not sufficient to guarantee plan stability but, for this goal above all else, this is likely to be the most appropriate action.

However, to further guarantee stability it is still likely that some degree of hinting – whether via manual hints, sql profiles or baselines – might be necessary for small numbers of SQL statements where the intial cost-based plan is not appropriate e.g. GTTs and other situations but it should be small number of statements.

SQL Execution Plan Instability – Summary & Opinion

The actions discussed above are made on the basis that we want to minimise the chance of execution plan instability at all costs.

By making this decision, we are prioritizing stability over all the features within Oracle designed to generate better plans for specific situations, sets of binds, etc.

Personally, I always recommend going with the default behaviour until such time as it causes a significant problem.

I also always recommend matching the scope of a solution to the scope of a problem.

For example, if we have a problem with one or two SQL statements, the potential solutions should be limited to those SQL statements.

We should never be making changes with a potential system-wide impact for the sake of a couple of SQL statements.

And even parameter changes can be injected to specific SQL statements either via a SQL Patch or via manual hints.

In my opinion, having multiple plans for a SQL statement is expected.

But what is under-appreciated is the extent to which this is normal.

These are normally only noticed when they cause a problem and the significant number of plans which regularly change plans without impact tend to go unnoticed.

It is also my opinion that SQL execution issues occur mostly when the SQL is badly written or when incompatible features are combined – e.g. peeked binds together with histograms – and I prefer to tackle that root cause rather than a generic concept of plan instability being inherently problematic.

A future path?

Might it be a good idea for there to be a built-in choice of optimizer “umbrella” modes – maximum performance vs maximum stability which would turn on/off some of the features above and/or adjust their sensitivity/aggressiveness?

Poll: SQL Plan Management

I’m interested in what SQL Plan Management features people are actively using.
Read more of this post

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

(or Part II Addendum)

In a comment on one of my previous articles about SQL Patches, a reader (hurrah!) effectively asks the question “Why is my SQL Patch being applied but the hint not obeyed?”

The original article itself was long enough without me adding a long comment so I’d like to break out into a fresh post.

Here is the test case.

Setup:

SQL> create table a_test as select * from dba_tables;

Table created.

SQL> create index a_test_idx on a_test(table_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'a_test');

PL/SQL procedure successfully completed.

SQL> select table_name from a_test where table_name='xxx';

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  d4knkaxjhqpgw, child number 0
-------------------------------------
select table_name from a_test where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("TABLE_NAME"='xxx')

Create a patch to hint a FULL TABLE SCAN:

SQL> begin
  2     sys.dbms_sqldiag_internal.i_create_patch
  3     (sql_text  => 'select table_name from a_test a where table_name=''xxx''',
  4      hint_text => 'full(a)',
  5      name      => 'patch_test');
  6  end;
  7  /

PL/SQL procedure successfully completed.

See that the patch is applied but the hint apparently not obeyed:

SQL> select table_name from a_test a where table_name='xxx';

no rows selected

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("TABLE_NAME"='xxx')

Note
-----
   - SQL patch "patch_test" used for this statement

The issue is that to use a SQL Patch to hint a statement, it seems that you have to be very careful to use the complete hint specification.

The complete hint specification is an ugly beast and should be considered a strong indicator that you really want to think twice before doing any hinting, and especially as Oracle versions march onwards and the Optimizer gets more complicated, the complete hint specification becomes ever more important.
See Jonathan Lewis’s How to Hint post for a good illustration.

Back to the test case…

If we drop the existing sql patch:

SQL> begin
  2   sys.dbms_sqldiag.drop_sql_patch('patch_test');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Use the +OUTLINE format option for DBMS_XPLAN to get a nudge in the right direction:

SQL> select table_name from a_test a where table_name='xxx';

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(format=> '+OUTLINE'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 2434419982

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| A_TEST_IDX |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 4)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "A"@"SEL$1" ("A_TEST"."TABLE_NAME"))
      END_OUTLINE_DATA
  */

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

   1 - access("TABLE_NAME"='xxx')

And create our patch with the complete specification:

SQL> begin
  2     sys.dbms_sqldiag_internal.i_create_patch
  3     (sql_text  => 'select table_name from a_test a where table_name=''xxx''',
  4      hint_text => 'FULL(@"SEL$1" "A"@"SEL$1")',
  5      name      => 'patch_test');
  6  end;
  7  /

PL/SQL procedure successfully completed.

We should find that it is more effective:

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  frmrwzdcc9p65, child number 0
-------------------------------------
select table_name from a_test a where table_name='xxx'

Plan hash value: 3679270240

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |    40 (100)|          |
|*  1 |  TABLE ACCESS FULL| A_TEST |     1 |    18 |    40   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("TABLE_NAME"='xxx')

Note
-----
   - SQL patch "patch_test" used for this statement


22 rows selected.

SQL> 

Bear in mind that the above is a test case only and consider what I said in the original article about what I consider to be the difference between a SQL Patch and a SQL Plan Baseline is and the different use cases.

Adaptive Cursor Sharing with SQL Plan Baselines

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.

SQL Patch III / Parallel Baselines

From my previous investigation, I had one outstanding question at the back of my mind:

Can we apply more than one hint with a SQL Patch?

In addition, I had previously noted that SQL Plan baselines have issues if you have a plan that has to use a parallel hint to get parallel operations.

By investigating the latter question, we can answer the former.

Initial setup, same as examples in previous post.

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

Table created.

SQL> 
SQL> insert into t1  
  2  select rownum  
  3  ,      lpad('X',200,'X')  
  4  ,      case when mod(rownum,10000) = 0  
  5              then 'Y'  
  6              else 'N'  
  7         end  
  8  from   dual  
  9  connect by rownum <= 100000;  

100000 rows created.

SQL> create index i1 on t1 (col1);

Index created.

SQL> exec dbms_stats.gather_table_stats(USER,'T1'); 

PL/SQL procedure successfully completed.

SQL> 

Let’s run a piece of code that does an index access:

SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   208 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)


19 rows selected.

SQL> 

What about if I want to hint this to a parallel full table scan?

SQL> select /*+ full(t1) parallel(t1 4) */ * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  chhkmc32mdkak, child number 0
-------------------------------------
select /*+ full(t1) parallel(t1 4) */ * from t1 where col1 = 1

Plan hash value: 2494645258

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   264 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | P->S | QC
|   3 |    PX BLOCK ITERATOR |          |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

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

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1"=1)


22 rows selected.

SQL> 

As expected.

Now, can I preserve this with a baseline?

SQL> declare     
  2    sqltext clob;     
  3    spm_op pls_integer;     
  4  begin     
  5    sqltext := 'select * from t1 where col1 = 1';     
  6    spm_op  :=     
  7    dbms_spm.load_plans_from_cursor_cache     
  8    (sql_id => 'chhkmc32mdkak',     
  9     plan_hash_value => 2494645258,     
 10     sql_text => sqltext);     
 11  end;     
 12  /

PL/SQL procedure successfully completed.

SQL> 

What happens when we re-run the original SQL?

SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 1
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   208 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)


19 rows selected.

SQL> 

Nothing. No baseline used.

The reason for this is that parallel is not in the plan hints but is essential to force our parallel plan and get the appropriate plan hash value.

Remember that a baseline must reproduce the stored plan hash or it will be ignored and the stored plan hash is SQLOBJ$.PLAN_ID which maps to the PLAN_HASH_2 value found in V$SQL_PLAN.OTHER_XML.

Let’s try to get the hints in the baseline:

SQL> select to_char(exact_matching_signature) from v$sql where sql_id = '81qv4d7vkb571';

TO_CHAR(EXACT_MATCHING_SIGNATURE)
----------------------------------------
9005682359107037619

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints  
  2  from   xmltable('/outline_data/hint'  
  3         passing (select xmltype(comp_data) xml  
  4                  from   sys.sqlobj$data  
  5                  where  signature = 9005682359107037619)) x; 
       passing (select xmltype(comp_data) xml
                *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

SQL> 

Is that interesting? There’s now more than one baseline – should not be surprising if you are familiar with the baseline mechanism.

DBA_SQL_PLAN_BASELINES doesn’t give us everything we need to investigate so back under the covers:

SQL> SELECT TO_CHAR(so.signature) signature
  2  ,      so.plan_id
  3  ,      DECODE(ad.origin, 1, 'MANUAL-LOAD',
  4                        2, 'AUTO-CAPTURE',
  5                        3, 'MANUAL-SQLTUNE',
  6                        4, 'AUTO-SQLTUNE',
  7                        5, 'STORED-OUTLINE',
  8                           'UNKNOWN') origin
  9  ,      DECODE(BITAND(so.flags, 1), 1, 'YES', 'NO') enabled
 10  ,      DECODE(BITAND(so.flags, 2), 2, 'YES', 'NO') accepted
 11  ,      DECODE(BITAND(so.flags, 64), 64, 'NO', 'YES') reproduced
 12  FROM   sys.sqlobj$        so
 13  ,      sys.sqlobj$auxdata ad
 14  WHERE  ad.signature = so.signature 
 15  AND    ad.plan_id   = so.plan_id
 16  AND    so.signature = 9005682359107037619;

SIGNATURE                                   PLAN_ID ORIGIN         ENA ACC REP
---------------------------------------- ---------- -------------- --- --- ---
9005682359107037619                       263533726 MANUAL-LOAD    YES YES NO
9005682359107037619                      3860916006 AUTO-CAPTURE   YES NO  YES

SQL> 

The plan for our manually created baseline was not reproducible so the optimizer ignores it, generates its best plan and stores that as an AUTO_CAPTURE plan for future evolution and acceptance. The AUTO_CAPTURE plan gives the outline for the original index scan that makes the most sense but we’re not interested in that.

Here are the hints for our manually loaded baseline that failed to reproduce the plan:

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints  
  2  from   xmltable('/outline_data/hint'  
  3         passing (select xmltype(comp_data) xml  
  4                  from   sys.sqlobj$data  
  5                  where  signature = 9005682359107037619
  6                  and    plan_id   = 263533726)) x;  

OUTLINE_HINTS
---------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 4)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")

7 rows selected.

SQL> 

You can see the FULL hint but no PARALLEL.
With no PARALLEL hint, we can’t get the hash for the parallel plan that our baseline requires.

Shall we just check a SPM trace to verify what we see when a baseline cannot be reproduced?

SQL> alter session set tracefile_identifier = 'dom_spm';

Session altered.

SQL> alter session set events 'trace[RDBMS.SQL_Plan_Management.*]';

Session altered.

SQL> 
SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


SQL> 
SQL> alter session set events 'trace[RDBMS.SQL_Plan_Management.*] off';

Session altered.

SQL> 

And in the trace:

SPM: statement found in SMB
SPM: planId's of plan baseline are: 263533726
SPM: using qksan to reproduce, cost and select accepted plan, sig = 9005682359107037619
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 263533726
SPM: planId in plan baseline = 263533726, planId of reproduced plan = 3688435342
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : RIMS
  plan_baseline signature  : 9005682359107037619
  plan_baseline plan_id    : 263533726
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
    hint num  3 len 22 text: DB_VERSION('11.2.0.3')
    hint num  4 len 41 text: OPT_PARAM('optimizer_dynamic_sampling' 4)
    hint num  5 len  8 text: ALL_ROWS
    hint num  6 len 22 text: OUTLINE_LEAF(@"SEL$1")
    hint num  7 len 27 text: FULL(@"SEL$1" "T1"@"SEL$1")
SPM: generated non-matching plan:
...
SPM: plan reproducibility round 2 (hinted OFE only)
SPM: using qksan to reproduce accepted plan, planId = 263533726
SPM: planId in plan baseline = 263533726, planId of reproduced plan = 3860916006
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : RIMS
  plan_baseline signature  : 9005682359107037619
  plan_baseline plan_id    : 263533726
  plan_baseline hintset    :
    hint num  1 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
SPM: generated non-matching plan:
...
------- END SPM Plan Dump -------
SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planId = 3860916006

The Optimizer has two attempts are reproducing the required plan – the first one with the full set of hints, the second with just the OFE hint before reverting back to the best cost plan and storing that as mentioned as an AUTO-CAPTURE plan.

So, it seems as if using just a baseline to preserve parallelism does not work.

And perhaps that’s not surprising if you know what the PARALLEL hint does – it doesn’t force parallelism.

I understand what’s going here but but I’m not convinced by this baseline behaviour.

If a PARALLEL hint is required to get a specific plan, I’d expect it to be preserved in the baseline but the bottom line, I suppose, is that it’s not preserved in the hints in V$SQL_PLAN.OTHER_XML.

So, can SQL Patch help here?

Leaving our baseline in place and just adding a SQL Patch (i’ve said before that a SQL Patch can seem quite fussy
about using the proper hint syntax):

SQL> begin  
  2    sys.dbms_sqldiag_internal.i_create_patch  
  3    (sql_text  => 'select * from t1 where col1 = 1',  
  4     hint_text => 'PARALLEL(@"SEL$1" "T1"@"SEL$1" 4)',  
  5     name      => 'patch_test');   
  6  end;  
  7  /  

PL/SQL procedure successfully completed.

SQL> 

Shall we try the SQL again?

SQL> select * from t1 where col1 = 1;

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N


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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   208 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

Note
-----
   - SQL patch "patch_test" used for this statement


23 rows selected.

SQL> 

So SQL Patch + SQL Baseline does not work as desired – the Baseline by itself can’t reproduce the desired plan and the Patch hint is irrelevant once the best cost plan is picked.

So, how about just using a SQL Patch with multiple hints? Can that work?

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

PL/SQL procedure successfully completed.

SQL> begin  
  2    sys.dbms_sqldiag.drop_sql_patch('patch_test');   
  3  end;  
  4  / 

PL/SQL procedure successfully completed.

SQL> begin  
  2    sys.dbms_sqldiag_internal.i_create_patch  
  3    (sql_text  => 'select * from t1 where col1 = 1',  
  4     hint_text => 'PARALLEL(@"SEL$1" "T1"@"SEL$1" 4) FULL(@"SEL$1" "T1"@"SEL$1")',  
  5     name      => 'patch_test');   
  6  end;  
  7  / 

PL/SQL procedure successfully completed.

SQL> select * from t1 where col1 = 1;  

      COL1 COL2                                                                  F
---------- --------------------------------------------------------------------- -
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX N

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 2494645258

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   264 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | P->S | QC
|   3 |    PX BLOCK ITERATOR |          |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

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

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1"=1)

Note
-----
   - SQL patch "patch_test" used for this statement


26 rows selected.

SQL>

Success!

So, in summary, it looks like SQL Plan baselines cannot preserve PARALLEL but a SQL Patch can apply it.

And, at the same time, this shows that you can apply multiple hints in one SQL Patch.

By the way, above I made a throwaway comment above that the PARALLEL hint is not preserved in V$SQL_PLAN.OTHER_XML which is why it’s not preserved in a baseline. We can use the ‘+OUTLINE’ format to prove this from that last example above with the SQL Patch:

SQL> select * from table(dbms_xplan.display_cursor(format=>'+OUTLINE'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  81qv4d7vkb571, child number 0
-------------------------------------
select * from t1 where col1 = 1

Plan hash value: 2494645258

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   264 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | P->S | QC
|   3 |    PX BLOCK ITERATOR |          |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       |     1 |   208 |   264   (1)| 00:00:02 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 4)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1"=1)

Note
-----
   - SQL patch "patch_test" used for this statement


41 rows selected.

SQL> 

And finally…

If you’ve been looking at the undocumented DBMS_SQLDIAG_INTERNAL package that you need to create a SQL Patch, you might have noticed that alongside I_CREATE_PATCH there is an I_CREATE_HINTSET procedure.

This isn’t another new type of SQL Object but an internal api that probably all these SQL Objects use. So, if you try to create a hintset with I_CREATE_HINTSET then by default it will create a SQL Profile that can be dropped via DBMS_SQLTUNE.DROP_SQL_PROFILE.
Note that DBMS_SPM_INTERNAL has an interface to GET_SPM_HINTSET that amongst others takes an OBJ_TYPE parameter.

SQL Patch II

Following on from my previous post, let’s look at some of the workings of a SQL patch to try to get a bigger picture about this little-known feature.

Warning – this is quite a long post.

Let’s start with a similar example to that on the Oracle Optimizer blog article.

First, let’s setup a table that might work with the BIND_AWARE hint as per the Optimizer blog example.

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

Table created.

SQL> insert into t1
  2  select rownum
  3  ,      lpad('X',200,'X')
  4  ,      case when mod(rownum,10000) = 0
  5              then 'Y'
  6              else 'N'
  7         end
  8  from   dual
  9  connect by rownum <= 100000;

100000 rows created.

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

F   COUNT(*)
- ----------
Y         10
N      99990

SQL> create index i1 on t1 (flag);

Index created.

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

PL/SQL procedure successfully completed.

And now a query with some bind variables.
Let’s execute this three or four times per bind variable:

SQL> var n varchar2(1)
SQL> exec :n := 'N'

PL/SQL procedure successfully completed.

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

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
     99990 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> /
.... etc...

SQL> exec :n := 'Y';

PL/SQL procedure successfully completed.

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

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> /
.... etc...

Let’s double check that we have a sql statement that the BIND_AWARE hint might be applicable to:

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 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     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)


20 rows selected.

SQL> select sql_id, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id = '731b98a8u0knf';

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf N 1292784087274697613                               6
731b98a8u0knf Y 1292784087274697613                               1

So far so good – we know we have a table and a statement that can be bind aware.

The next steps – as per the Optimizer blog – are to show how we can get the BIND_AWARE hint to apply from the first execution.

So, how about we flush this statement above and use the SQL Patch functionality to inject the BIND_AWARE hint.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id = '731b98a8u0knf';

no rows selected

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'BIND_AWARE',
  5    name      => 'patch_test'); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

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

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     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 patch "patch_test" used for this statement


24 rows selected.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id = '731b98a8u0knf';

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf Y 1292784087274697613                               1

SQL> 

Great – it all works as per the Optimizer team told us it would.

The execution plan has one of those invaluable Notes confirming that a SQL patch was used.

So, next up, why can’t we use a baseline to do this?

Well, as mentioned previously, it’s not because baselines can’t change execution plans in a similar way.

Au contraire, they can and, in most circumstances, they are probably the best placed feature to do this using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.

What they can’t do is inject the BIND_AWARE hint.

Allow me to demonstrate.

The approach with a baseline is slightly more complicated because we have to generate the required plan with one bit of sql and then, while it’s still in the cursor cache, transfer the whole plan into a baseline for the target sql statement.

First, let me delete that SQL Patch and verify that nothing is affecting our target statement.

SQL> exec dbms_sqldiag.drop_sql_patch('patch_test');

PL/SQL procedure successfully completed.

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

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  5ya8tff6fdrsa, child number 0
-------------------------------------
select count(*), max(col2) from t1 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 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     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)


20 rows selected.

SQL> 

Then let’s get a plan for a statement with the BIND_AWARE hint.

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

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  7qzu8gp22qr0v, child number 0
-------------------------------------
select /*+ bind_aware */ count(*), max(col2) from t1 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 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     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)


20 rows selected.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id in ('731b98a8u0knf','7qzu8gp22qr0v');

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
7qzu8gp22qr0v Y 17219147247362199511                              1

SQL> 

Our new hinted statement is instantly bind aware when hinted as such.

Now, let’s load this plan from the cursor cache to the unhinted target statement using the sql_id and plan_hash_value from the DBMS_XPLAN output above.

SQL> declare   
  2    sqltext clob;   
  3    spm_op pls_integer;   
  4  begin   
  5    sqltext := 'select count(*), max(col2) from t1 where flag = :n';   
  6    spm_op  :=   
  7    dbms_spm.load_plans_from_cursor_cache   
  8    (sql_id => '7qzu8gp22qr0v',   
  9     plan_hash_value => 3625400295,   
 10     sql_text => sqltext);   
 11  end;   
 12  /   

PL/SQL procedure successfully completed.

And let’s see what difference the baseline makes:

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

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     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


24 rows selected.

Great! So our baseline is in full effect.

But, is the statement bind aware as it was the SQL Patch was applied?

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id in ('731b98a8u0knf');

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf N 1292784087274697613                               1

No, it’s not.

Now, why should this be?

Let’s recap the different intentions of SQL Baselines and SQL Patches

SQL Baselines exist to reproduce a specific plan. In fact, a plan hash exists as part of the baseline.
If, on application of the baseline, the Optimizer is unable to reproduce the desired plan, the baseline is rejected outright.

On the other hand, SQL Patches have been developed primarily to get the Optimizer to avoid a particular problem path in an execution plan, specifically to avoid failures due to certain access methods, join methods, etc.

Or to put it another way, the answer is in the internals.

Let me recreate the patch again:

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'BIND_AWARE',
  5    name      => 'patch_test'); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

You could look at DBA_SQL_PATCHES but it doesn’t tell you very much.

Or at least it does – for example the FORCE_MATCHING column indicates that SQL Patches have the same force matching ability as SQL Profiles and which Baselines don’t have – but it doesn’t show what I want you to see.

It can be a bit more revealing to look around under the hood at SYS.SQLOBJ$, SYS.SQLOBJ$DATA and SYS.SQLOBJ$AUXDATA.

Using the signature from V$SQL above:

SQL> select signature, obj_type, plan_id, name, flags, last_executed
  2  from sys.sqlobj$ 
  3  where signature = 1292784087274697613;

 SIGNATURE   OBJ_TYPE    PLAN_ID NAME                                FLAGS LAST_EXECUTED
---------- ---------- ---------- ------------------------------ ---------- -------------------------
1.2928E+18          2 2239163167 SQL_PLAN_13w748wknkcwd8576eb1f         11 06-MAR-12 03.01.19.000000 PM
1.2928E+18          3          0 patch_test                              1

Profiles have an OBJ_TYPE of 1.

Our Baseline has a type of 2.

The Patch has a type of 3.

The flags translate to ENABLED/DISABLED for SQL Profiles and SQL Patches and to values for ENABLED, ACCEPTED, FIXED, REPRODUCED and AUTOPURGE for SQL Plan Baselines.

Another important aspect is that PLAN_ID.
I mentioned that Baselines are designed to reproduce a specific plan and if the optimizer is unable to reproduce the specific plan when applying the baseline then it won’t use it. Plan_id is another hash, not plan_hash_value but plan_hash_2 from V$SQL.OTHER_XML.

There’s a contrast of information elsewhere.
For example, SQLOBJ$AUXDATA will highlight the performance metrics that are tracked for Baselines to aid evolution.

But it’s the hints that really should be interesting us, for that we need to look at SQLOBJ$DATA.

Here’s what our SQL Patch uses:

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
  2  from   xmltable('/outline_data/hint'
  3         passing (select xmltype(comp_data) xml
  4                  from   sys.sqlobj$data
  5                  where  signature = 1292784087274697613
  6                  and    obj_type  = 3)) x;

OUTLINE_HINTS
-------------------------------------------------------------------------------------------
BIND_AWARE

SQL> 

Just what we asked it to do – only inject BIND_AWARE

And our baseline contains all that is required to reproduce the exact plan it was asked to baseline.

SQL> select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
  2  from   xmltable('/outline_data/hint'
  3         passing (select xmltype(comp_data) xml
  4                  from   sys.sqlobj$data
  5                  where  signature = 1292784087274697613
  6                  and    obj_type  = 2)) x;

OUTLINE_HINTS
---------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 4)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG"))

7 rows selected.

SQL> 

No sign of BIND_AWARE – only the hints that are required to reproduce the exact plan we asked for. And in fact BIND_AWARE might be a threat to that plan stability.

And so to the question of whether a SQL Patch and a Baseline can work together?

And the answer is yes PROVIDED that the hints are such that the pair can happily co-exist:

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

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     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 patch "patch_test" used for this statement
   - SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement


25 rows selected.

SQL> select sql_id, sql_text, is_bind_aware, to_char(exact_matching_signature) sig, executions
  2  from   v$sql
  3  where  sql_id in ('731b98a8u0knf');

SQL_ID        I SIG                                      EXECUTIONS
------------- - ---------------------------------------- ----------
731b98a8u0knf Y 1292784087274697613                               1

SQL> 

What happens if we try to create a SQL Patch that conflicts with the instructions of the Baseline?

Well, first note that only one SQL Patch can exist for statement at any one time (in any one category as per sql profile functionality):

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'FULL(t1)',
  5    name      => 'patch_test2'); 
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-13830: SQL profile with category DEFAULT already exists for this SQL statement
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16167
ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 204
ORA-06512: at line 2

So, let’s delete the existing SQL Patch and check that we’ve still got a baseline working (I’ve recreated the baseline since the demo started so it’s got a different name):

SQL> exec dbms_sqldiag.drop_sql_patch('patch_test');

PL/SQL procedure successfully completed.

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

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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: 3625400295

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |  1872 |     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


24 rows selected.

SQL> 

And let’s try to create one that will conflict with the index hints in the Baseline (note that you seem to have to be very particular about the full and proper hint syntax, i.e. just a FULL(t1) wasn’t cutting it):

SQL> begin
  2   sys.dbms_sqldiag_internal.i_create_patch
  3   (sql_text  => 'select count(*), max(col2) from t1 where flag = :n',
  4    hint_text => 'FULL(@"SEL$1" "T1"@"SEL$1")',
  5    name      => 'patch_test2'); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 

And in this scenario, it seems that Patch trumps Baseline:

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

  COUNT(*) MAX(COL2)
---------- -------------------------------------------------------------------
        10 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


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   |      |       |       |   956 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    18 |  1872 |   956   (2)| 00:00:05 |
---------------------------------------------------------------------------

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

   2 - filter("FLAG"=:N)

Note
-----
   - SQL patch "patch_test2" used for this statement


23 rows selected.

which is probably what we’d expect.

That’ll do for now.

Follow

Get every new post delivered to your Inbox.

Join 79 other followers