Is The Latest Bundle Patch for 12.1.0.2 Fixing SQL Plan Directive’s Behavior?

Take a guess, you have 50% chances of being right!

I was very excited last week, as I got to finally deploy the January 2018 Bundle Patch to the test environment of a 12.1.0.2 Oracle database.

Why was I excited?

Because this database had problems with execution plan instability, partly caused by SQL Plan Directives, and I was hoping this bundle patch would help with that.

If you read so far, and wonder what SQL Plan Directives are, check out the previous post What Are SQL Plan Directives?

If you wonder why the January 2018 Bundle Patch should fix some of the performance problems in 12.1.0.2, check out my latest post: What Should You Do With SQL Plan Directive Changes in 12.1?

Going back to today’s topic, I was ready to deploy the patch. Very ready.

And so, that is what I did.

Once the patch was deployed, I enabled the fixes, as recommended by Oracle in the following document:
Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics and 12c SQL Performance) (Doc ID 2312911.1)

Did the patch help? Are you curious to find out? Keep on reading, as I’ll walk you through my findings.

After the patch was deployed, my expectations and reality were not in sync.

I thought that the new init.ora parameter that was introduced with this patch, optimizer_adaptive_statistics, will be set to FALSE.
I was wrong.

Check it out below:

Parameter Status After The Jan 2018 BP:

SQL> select value from v$system_fix_control
     where bugno = 26664361;

          VALUE
     ----------
              0

SQL> show parameter optimizer:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
optimizer_adaptive_features          boolean     TRUE
optimizer_adaptive_plans             boolean     TRUE
optimizer_adaptive_statistics        boolean     TRUE
...

Enabled the fixes as per Oracle’s documentation:

SQL>alter system set "_fix_control"='26664361:7','16732417:1','20243268:1' scope=spfile;
SQL>alter system set optimizer_adaptive_statistics=false scope=spfile;
SQL>shutdown immediate;
SQL>startup;
SQL>@?/rdbms/admin/execstat.sql

Parameter status After Fixes were enabled:

SQL> select value from v$system_fix_control
     where bugno = 26664361;

          VALUE
     ----------
              7

SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features          boolean     TRUE
optimizer_adaptive_plans             boolean     TRUE
optimizer_adaptive_statistics        boolean     FALSE
...

What’s next? Are SQL Plan Directives disabled now?

The only way to find out is to put the database under tests. I asked the application users to run tests, and see if performance has improved. Of course you cannot rely on user’s perception alone. You need some data that can backup your findings.

Next, I verified, if there were any SQL Plan Directives used, since I deployed the patch. The patch was deployed March 15th.

SQL> select a.directive_id, trunc(a.last_used), b.owner,b.object_name
     from DBA_SQL_PLAN_DIRECTIVES a , dba_sql_plan_dir_objects b
     where a.directive_id=b.directive_id
     and a.last_used > sysdate - 5
     order by 2,1,3,4;  

                     DIRECTIVE_ID TRUNC(A.L OWNER                    OBJECT_NAME
--------------------------------- --------- ------------------------ -------------
              1246112249024507648 15-MAR-18 HR                       TABLE_A
              1246112249024507648 15-MAR-18 HR                       TABLE_B
              ...
              2489171598763801962 17-MAR-18 HR                       TABLE_A
              2489171598763801962 17-MAR-18 HR                       TABLE_B
              2609575923464800517 17-MAR-18 HR                       TABLE_A
              ...
 

Can you see that SQL Plan Directives are being used? Last used date in the output above, being March 17th.

Are there new SQL Plan Directives created? Let’s find out!

SQL> select max(created) from DBA_SQL_PLAN_DIRECTIVES;

MAX(CREATED)
-------------------------------
20-MAR-18 09.11.40.000000 PM

SQL> select count(*) from DBA_SQL_PLAN_DIRECTIVES where created > sysdate -5;

  COUNT(*)
----------
        50

Wow, did I miss something?

There were 50 new SQL Plan Directives created since the patch was deployed, and a whole bunch of existing ones that were used since the patch was deployed.

During the test, I was able to confirm that certain SQL Statements were no longer using SQL Plan Directives, and some of them were still using SQL Plan Directives.

I read and re-read Oracle’s documentation to see if I missed something. I did not. I searched on Oracle’s support site, and did not find anything to confirm my findings.

That is when I decided to open an SR.

The SR identified, that the Oct 2017 and Jan 2018 BP had a bug: Patch 27626925: OPTIMIZER ADAPTIVE STATS DEFAULT FALSE NOT HONORED WHEN ENABLED IN OCT OR JAN BP, and that I needed to install a patch, to fix the Bundle Patch.

So that is what I did.

Do you think the patch fixed the problem?

Unfortunately not. After the fix was in place, the users ran the tests. I ran the same sql statements as above to confirm if SQL Plan Baselines were in use, and if new ones were created.
I got positive for both, SQL Plan Directives were still used and new ones were created after the second patch was deployed.

Finally Oracle support recommended I set 2 hidden parameters to disable the SQL Plan Directives:

_sql_plan_directive_mgmt_control = 0 -----> This will disable the creation of new SQL Plan Directives 
_optimizer_dsdir_usage_control = 0   -----> This will disable the use of existing SPDs 

The conclusion of the SR was that it seems the patch did not fix my problem.

Now with these hidden parameters, do you think the SQL Plan Directives are no longer used and created?

Actually, the 2 hidden parameters do turn off the creation of new SQL Plan Directives, and also it will disable the use of existing SQL Plan Directives.
These parameters were available without the patch, and are documented in the following note:
How To Disable SQL Plan Directive (SPD) At Multiple Levels ( Doc ID 2209560.1 )

There is one important things to remember from this experience:

Never assume a patch will fix the problem. Always verify and get proof that what a patch is supposed to do, it actually does.

Have you tried deploying this patch? Did it solve your problems? Leave a comment below, I read every comment!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana