Is your baseline accepted as well?
On Wed, Jul 14, 2021, 17:58 Jeff Chirco <backseatdba@xxxxxxxxx> wrote:
Ever since I upgraded from 19.8 to 19.11 we've been having issues with a
query and the Fixed SQL Plan Baseline not getting used.
We have it marked as 'fixed" but new ones still get created and used
instead. This is causing havoc with our database because this query is
running 12 times per second, usually runs in .02 seconds but the newer
execution plans are anywhere from 15-45 seconds, destroying our CPU.
I have the following parameter set
optimizer_capture_sql_plan_baselines=FALSE
optimizer_use_sql_plan_baselines=TRUE
My understanding is that if a query has a baseline then it will generate
others, but maybe not understanding what "fixed" means. I thought it would
not use any other baselines?
I also tried removing all baselines for the SQLID. However it still
generated new baselines. Which I thought since I have
optimizer_capture_sql_plan_baselines=FALSE this would not happen.
I am really confused, I guess I could set
optimizer_use_sql_plan_baselines=FALSE
I am on edge everyday (typically it happens every few days) waiting to see
if the query will get a bad plan. I usually fix it by disabling or dropping
the new plans. Sometimes dropping all, or recreating a new baseline with
the good execution plan from AWR.
Oracle support has been zero help so far and it's been over a week. Time
to escalate.