I ‘fixed’ execution plan regression with optimizer_features_enable, what to do next?

Here is a simple example of using Mauro Pagano ‘pathfinder’ tool where you don’t really want to run the query, but just get the execution plan with all variations of optimizer settings. That’s something I used many times in situations similar to this one:

• the database was upgraded, say from 11.2.0.4 to 19.3
• one (or a few) SQL statements have problematic performance regression
• the execution plan (in 19.3) is different than from the previous version (11.2.0.4) — you get both with SQL Tuning Sets or AWR
• you set optimizer_features_enable to 11.2.0.4 and the old plan with acceptable performance is back

That’s a quick workaround, thanks to this unique Oracle Optimizer feature which let us run the latest version of the database with a previous version of the optimizer code. But the goal is not to stay long like this. Once the service is made acceptable again with this temporary setting, the second step is to understand which bug or feature is responsible for the change. Then, at least, the workaround can be limited to only one underscore setting instead of the generic optimizer_features_enable which sets hundreds of them. The third step then will be to fix the root cause, of course, and understanding what was wrong will help.

This post is about the second step — going from the general optimizer_features_enable to a unique focused setting.

This is something I wanted to write for a long time but I was always in a rush when encountering this kind of problem. But I’m currently attending Mike Dietrich upgrade workshop at AOUG conference in Vienna and this, the change of execution plan, is addressed by the exercises. Mike exposes the tools that can be used to compare the performance before and after the upgrade: capture the statements and performance statistics and compare them, and to fix them with SQL Plan Management.

The workshop instructions are on Mike’s blog:

HOL 19c - Main Index Page

If you did the workshop you have seen that the query sql_id=13dn4hkrzfpdy has a different execution plan between 11g and 19c and the idea of the lab is to fix the previous plan with a SQL Plan Baseline. That’s perfect, but I was curious about the reason for this execution plan change. There are many new features or fixes between 11.2.0.4 and 19.3 and one is probably responsible for that.

This is where Mauro Pagano ‘pathfinder’ can be used. Setting optimizer_features_enable is a shortcut to set all individual features or fixes, and pathfinder will try each of them one by one.

The query with a plan regression was:

SQL Details:
-----------------------------
 Object ID            : 34
 Schema Name          : TPCC
 Container Name       : Unknown (con_dbid: 72245725)
 SQL ID               : 13dn4hkrzfpdy
 Execution Frequency  : 3273
 SQL Text             : 
SELECT COUNT(DISTINCT (S_I_ID)) FROM ORDER_LINE, STOCK
 WHERE OL_W_ID = :B2 AND OL_D_ID = :B4 AND (OL_O_ID < :B3
 ) AND OL_O_ID >= (:B3 - 20) AND S_W_ID = :B2 AND S_I_ID =
 OL_I_ID AND S_QUANTITY < :B1

The plan before and after, as reported by AWR Diff Report are the following:

And my goal is to understand which feature or fix control, when disabled, gets back to the plan hash value 954326358 instead of 3300316041

I installed sqldb360 (open sourced by Carlos Sierra and Mauro Pagano), which contains pathfinder:

git clone https://github.com/sqldb360/sqldb360.git
cd ./sqldb360/sql/

I changed the script.sql to put my query there with an EXPLAIN PLAN because I don’t want to execute it (which would require parameters):

alter session set current_schema=TPCC;
explain plan for
 SELECT  /* ^^pathfinder_testid */
  COUNT(DISTINCT (S_I_ID)) FROM ORDER_LINE, STOCK
  WHERE OL_W_ID = :B2 AND OL_D_ID = :B4 AND (OL_O_ID < :B3
  ) AND OL_O_ID >= (:B3 - 20) AND S_W_ID = :B2 AND S_I_ID =
  OL_I_ID AND S_QUANTITY < :B1

By default, pathfinder executes the query and gets the execution plan with dbms_xplan.display_cursor, using the tag in the comment to identify it.

Here I’m doing an EXPLAIN PLAN and then I changed the pathfinder.sql to use dbms_xplan.display. My change in the ‘xplan driver’ is the following:

I’ve left the old query, but add the following one to be executed:

-- my addition there
PRO .
PRO SELECT RPAD('explain plan', 11) inst_child, plan_table_output
PRO FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ADVANCED'))
-- done
PRO /

Updated 17-DEC-2019

You may not have to do this change. I’ve submitted a PR for it which was accepted: added union all to get the plan from PLAN_TABLE if not empty but you can also contribute further in Refine “EXPLAIN PLAN” mode in Pathfinder.

Then running pathfinder:

[oracle@hol]$ sqlplus / as sysdba @ pathfinder.sql '"/ as sysdba"'

This takes some time to test all settings for optimizer underscore parameters (632 ones here in 19.3) and fix controls (1459 here):

The result is a zip file containing an index and the detail of each test.

The index (00001_pathfinder_upgr_20190515_1113_index.html) has one line per combination and it is easy to search from the plan hash value:

My old plan is chosen when _optimizer_partial_join_eval is set to false:

And now, I have a better workaround. Instead of setting the optimizer_feature_enable, I can set only:

ALTER SESSION SET "_optimizer_partial_join_eval" = FALSE;

Of course, my search for the plan hash value also highlights which versions set the same:

The goal of this post is to show the tool. If you want to know more about Partial Join Evaluation, Google tells me that I blogged about this in the past:

Partial Join Evaluation in Oracle 12c - Blog dbi services

The query here, a count(distinct) on a join, is subject to this optimization which changes the join to a semi-join.

If I can change the query, maybe I’ll prefer to disable it with a hint. If I click on the baseline plan from the pathfinder index, I can see the plan with hints:

Then probably a NO_PARTIAL_JOIN can disable this feature.

Side remark: you can see OPTIMIZER_FEATURES_ENABLE(‘19.1.0') but I told you that I’m on 19.3, right? And that this is the pathfinder baseline without any session setting. I didn’t expect 19.3 there because Release Updates should not add features that change the execution plan. But I expected something like ‘19.0.0’. The magic of the new release model…

In summary:

• Pathfinder is easy to run, give it a try when you need to understand why an execution plan has changed.
• Do the Mike Dietrich hands-on lab: upgrade is something to exercise before doing it in production.
• Since 8i, the Oracle Optimizer developers add a flag for any change, in order to give us the possibility to enable or disable the feature or the fix. And you control it at instance, session or query level. This is a unique feature you do not find on other database systems. And it can save your business because a critical regression can always happen after an upgrade.
• AOUG conference had a great idea with the ‘workshop and live-demo’ day before the conference day. Fewer attendees and more interaction with the speakers.