RE: [EXTERNAL] Question on sql plan management

We are upgrading an Oracle EBS database from 11.2.0.4 to 19c. During final testing, we noticed some Payroll processes running longer than anticipated. We created a baseline in 11g and migrated it to 19c. The 19c database did NOT use the baseline. From what I read, this can happen if the optimizer can not reproduce the plan in the baseline. Just passing this on for what its worth.

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of yudhi s Sent: Wednesday, August 31, 2022 3:08 PM To: Oracle L <oracle-l_at_freelists.org> Subject: [EXTERNAL] Question on sql plan management

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-l