Re: fixed sql baseline not being used

[image: image.png]

On Wed, Jul 14, 2021 at 10:27 AM Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org> wrote:

> What does the below show:
>
>
>
>
>
> select sql_handle, plan_name, enabled, accepted, fixed, last_executed,
> origin
>
> from sys.dba_sql_plan_baselines
>
> WHERE signature IN (
>
> sELECT exact_matching_signature FROM v$sql WHERE
> sql_id='&1'
>
> )
>
> ;
>
>
>
> *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
> *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
>
>
>
> Yes it is.
>
>
>
> On Wed, Jul 14, 2021 at 8:29 AM Laurentiu Oprea <
> 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> 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.
>
>

--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image.png