Question on adaptive plans
Date: Mon, 27 Dec 2021 17:45:57 +0530
Message-ID: <CAKna9Vbm6bbrQDB+=QBYHmvepg18+1cGdeZ8K0=cJi9UBdvD9Q_at_mail.gmail.com>
Hello Listers, During one of our database migrations from version 11.2 to 19C(19.9 patch), we encountered many similar queries suffering from plan changes and running longer. And those were complex queries with 10+ tables in the join conditions etc. But as we kept encountering the same for many we tried creating profile/patch for some with OFE 11.2 hints but then endup setting the OFE back to 11.2.0.4 in system level so as to avoid application/customer impact.
After verifying the difference in explain plan we found the new plans were having 'statistics collectors' in many of the lines and mostly adding some buffered operations and changing joins causing slowness for these queries. And these are part of 'adaptive plan' which has been introduced in this new version and I have attached a sql monitor for one such execution , it was taking seconds in 11.2 vs 19c and it was running for 10minutes+. Tried by tweaking the optimizer_adpative_reporting_only to TRUE but was getting similar results and by setting optimizer_adaptive_plans to false only its finishing in quick time.
Now , as we want to move ahead and make the OFE back to 19.1.0 at system
level. So my question was , is it okay to set the optimizer_adaptive_plans
set as 'FALSE' at system level in this scenario? Or will it cause any other
harm or say we end up missing some related good 19C features?
Regards
Lok
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Dec 27 2021 - 13:15:57 CET
- text/plain attachment: adaptive_plan_19_1_VS_11_2.txt