Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

SPD State Does Not Change If Adaptive Statistics Are Disabled

28 November 2017 4 Comments Written by Christian Antognini

The aim of this post is to point out an issue (bug?) that I recently discovered. But, before talking about it, I need to go through a rather long introduction about the state of SQL plan directive (SPD).

As of version 12.1.0.2, an SPD has two state information. You can see both of them through the CDB/DBA_SQL_PLAN_DIRECTIVES data dictionary views:

  • The STATE column, which takes the values USABLE and SUPERSEDED, informs us on whether the query optimizer actively uses the SPD.
  • The NOTES column contains an XML fragment that, among other information, provide the INTERNAL_STATE element. This element is set to either NEW, MISSING_STATS, PERMANENT or HAS_STATS.

The internal state is the actual state of the SPD (and the only one available in version 12.1.0.1). Oracle introduced the other one to hide some implementation details that they consider irrelevant for most users. As a result, the mapping between the two states is rather simple: except in case of redundant SPDs, the internal states NEW, MISSING_STATS and PERMANENT are mapped to USABLE; otherwise, SUPERSEDED is used.

So, for the rest of this discussion, let’s consider the internal state only. That, for simplicity, is referred to as “state”.

Depending on the state of an SPD and the database configuration, the database engine triggers different operations. And, while doing so, it can also change the state itself. For example, with a version 12.1.0.2 default configuration, the lifecycle of a specific SPD can be like the following (let me stress that this is just an example, a number of different scenarios are also possible):

  • The SPD is created. Its state is set to NEW.
  • The query optimizer, while generating an execution plan, notices that a matching SPD exists. Since the SPD state is NEW, it triggers dynamic sampling and changes the SPD state to MISSING_STATS.
  • The DBMS_STATS package, while gathering object statistics, notices that the table it is working on has an associated SPD. Because of that, it creates an extension and gathers statistics for it.
  • The query optimizer, while generating an execution plan, notices that a matching SPD exist. Since the SPD state is MISSING_STATS and that an extension for it exists, it triggers dynamic sampling and changes the SPD state to HAS_STATS.
  • From now on, neither the query optimizer nor the DBMS_STATS package considers the SPD.

The essential thing I wanted to emphasize by describing such a scenario is that an SPD state is expected to change over time. And, depending on it and on the database configuration, the database engine can trigger different operations.

Now that the introduction part is over, let us focus on the issue I recently found while working for one of my customers. The particular thing about that customer (and, as a result, why I never noticed it before) is that it uses 12.1.0.2 with the patch 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES), but without the patch 21171382 (Add Control for Automatic Creation of Stats Extensions). If you do not know what those patches are, have a look to this blog post.

Since the customer was using OPTIMIZER_ADAPTIVE_STATISTICS set to FALSE, I expected the SPD to be created without being actually used. But, what I discovered is that while SPD do not trigger dynamic sampling, they do trigger the creation of extensions. And, even worse (and that is the real issue), they do so without changing the SPD state that remains set to NEW!

If you are interested to see an example, here is the test case I wrote to reproduce it. Note that the issue can also be reproduced with version 12.2.0.1.

Finally, if you want to track this issue, refer to bug 27178368 (SPD STATE DOES NOT CHANGE IF ADAPTIVE STATISTICS IS FALSE).

12.1.0.2, 12cR2, Adaptive Query Optimization, Bug, Object Statistics, Query Optimizer, SQL Plan Directives
The APPROX_MEDIAN Function – A Test Case
SQL_ADAPTIVE_PLAN_RESOLVED Is Broken

4 Comments

1 Ping/Trackback

  1. Foued Foued
    29 November 2017    

    Thank you for the post

    Reply
  2. SPD State Does Not Change If Adaptive Statistics Are Disabled - SSWUG.ORG SPD State Does Not Change If Adaptive Statistics Are Disabled - SSWUG.ORG
    18 December 2017    

    […] Read More (Community content) […]

  3. Ted Persky Ted Persky
    7 June 2018    

    FYI, the title of non-published Bug 21171382 is “Add Control for Automatic Creation of Stats Extensions.” I think that this updated title would also be less confusing for your customers to read here, since the issue you describe is unrelated to DOP.

    Reply
    • Christian Antognini Christian Antognini
      8 June 2018    

      Thank you for the information, Ted! I amended the text.

      Reply
  1. SPD State Does Not Change If Adaptive Statistics Are Disabled - SSWUG.ORG on 18 December 2017 at 07:00

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.