RE: fixed sql baseline not being used
Date: Wed, 14 Jul 2021 17:27:32 +0000
Message-ID: <DM6PR09MB467755C709D75FFA3A6186D9DF139_at_DM6PR09MB4677.namprd09.prod.outlook.com>
What does the below show:
select sql_handle, plan_name, enabled, accepted, fixed, last_executed, origin
from sys.dba_sql_plan_baselines
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Jeff Chirco
Sent: Wednesday, July 14, 2021 1:11 PM
Yes it is.
On Wed, Jul 14, 2021 at 8:29 AM Laurentiu Oprea <laurentiu.oprea06_at_gmail.com<mailto:laurentiu.oprea06_at_gmail.com>> wrote:
Is your baseline accepted as well?
On Wed, Jul 14, 2021, 17:58 Jeff Chirco <backseatdba_at_gmail.com<mailto:backseatdba_at_gmail.com>> 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
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.
WHERE signature IN (
sELECT exact_matching_signature FROM v$sql WHERE sql_id='&1'
)
;
To: Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
Cc: oracle-l-freelist <oracle-l_at_freelists.org>
Subject: Re: fixed sql baseline not being used
optimizer_capture_sql_plan_baselines=FALSE
optimizer_use_sql_plan_baselines=TRUE
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 14 2021 - 19:27:32 CEST