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

Franck Pachot
May 16 · 6 min read
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 /
[oracle@hol]$ sqlplus / as sysdba @ pathfinder.sql '"/ as sysdba"'
ALTER SESSION SET "_optimizer_partial_join_eval" = FALSE;

In summary:

Franck Pachot

Written by

https://twitter.com/FranckPachot DBA at CERN, Oracle OCM 12c, Oracle ACE Director, Oak Table member. My 499 posts at dbi-services: http://blog.dbi.pachot.net

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade