SPD State Does Not Change If Adaptive Statistics Are Disabled

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