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

  • 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
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
git clone https://github.com/sqldb360/sqldb360.git
cd ./sqldb360/sql/
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
-- 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.

[oracle@hol]$ sqlplus / as sysdba @ pathfinder.sql '"/ as sysdba"'
ALTER SESSION SET "_optimizer_partial_join_eval" = FALSE;

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Franck Pachot

Franck Pachot

502 Followers

Developer Advocate at Yugabyte, Open Source distributed SQL database 🚀 Also Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member