Question on sql plan management
From: yudhi s <learnerdatabase99_at_gmail.com>
Date: Thu, 1 Sep 2022 00:37:38 +0530
Message-ID: <CAEzWdqeOQ_BZ+dAvjhfCCq9cfzAfTcvJVH1EO9CJn+xJEVBjug_at_mail.gmail.com>
Hello, we are migrating from 12.1 version Oracle database to 19C. And just to avoid any surprises we have turned on optimizer_capture_sql_plan_baselines to TRUE in current 12.1 production. Each day we are seeing thousands of baselines getting created in dba_sql_plan_baselines with both the flag ACCEPTED and ENABLED as YES. And as I understand , each of the sql that were executed are now associated with one baselines or a specific execution path. We are planning to turn the optimizer_capture_sql_plan_baselines back to FALSE after all the possible workload times(like daily/weekly/monthly jobs sql) are captured so that no sql is left without usage of an 'accepted' baseline.
Date: Thu, 1 Sep 2022 00:37:38 +0530
Message-ID: <CAEzWdqeOQ_BZ+dAvjhfCCq9cfzAfTcvJVH1EO9CJn+xJEVBjug_at_mail.gmail.com>
Hello, we are migrating from 12.1 version Oracle database to 19C. And just to avoid any surprises we have turned on optimizer_capture_sql_plan_baselines to TRUE in current 12.1 production. Each day we are seeing thousands of baselines getting created in dba_sql_plan_baselines with both the flag ACCEPTED and ENABLED as YES. And as I understand , each of the sql that were executed are now associated with one baselines or a specific execution path. We are planning to turn the optimizer_capture_sql_plan_baselines back to FALSE after all the possible workload times(like daily/weekly/monthly jobs sql) are captured so that no sql is left without usage of an 'accepted' baseline.
Now as I understand this above baselines will work as a shield for the plan regression issue. As because , even with the 19C optimizer feature, the queries are going to follow the captured 12.1 baseline path which is in the accepted state. But my question was , as 19C has a lot of enhancement done in the optimizer and those may actually benefit many or some of the existing queries, so what is the suggested way to get those new or better plans added safely to the existing sql queries or accepted baselines? Or should we rely on the oracle given the auto evolve task(SYS_AUTO_SPM_EVOLVE_TASK) to do this for us? And will the captured baselines cause issues for heavy hard parse queries?
Regards
Yudhi
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 31 2022 - 21:07:38 CEST