Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-ab.freelists.org (smtp-ab.freelists.org [34.228.148.125]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 6FC131003111DD for ; Wed, 2 Feb 2022 09:12:39 +0100 (CET) Received: from turing.freelists.org (turing [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-ab.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 51B36405E3; Wed, 2 Feb 2022 08:12:38 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 4400D3FA0A; Wed, 2 Feb 2022 08:12:38 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1643789558; bh=Y9+CPdOy/trx0mgRTOcRqHbcMSrs74aEcfLfGdPsJ0c=; h=From:Sender:Sender:From; b=vEEjW4CjCKnJAWmwLmDCNCvWvwz9n37XiutshFbbhzR92sxgXElZ6f2bCgozHnYV1 GjmlGoI6TAie88hA3QSEhA1YrxoBiQvuTiG1m+K9hZjTvFryZT9MZDfAhD8G62LuLL GIGxco44FtgvHMt0MjKIrw27U93TF+y7eShr/PjY= X-Virus-Scanned: by FreeLists at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id WQidqPlv7czl; Wed, 2 Feb 2022 08:12:38 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 92E6D3FA0C; Wed, 2 Feb 2022 08:12:33 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1643789555; bh=Y9+CPdOy/trx0mgRTOcRqHbcMSrs74aEcfLfGdPsJ0c=; h=From:Sender:Sender:From; b=KQJpf+Z8nOLZdrqPW4Wm6ZyLYu73p0uhoH60cADBfn17rcDHIhg3bty7bS2yt0utU ofV/M1TTf3mDfpaU7e15cLvPecVFB8VBiQXtQK4s7adOZ0L6Y2n4rI066Xi/3tz35D W0HPAQz/0Mo9rhmV2mEpFHkJ+YRX3pxh2xa3Hd/Q= Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 02 Feb 2022 08:12:32 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id EED6B3FA08 for ; Wed, 2 Feb 2022 08:12:31 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20210112 header.b=OsUcX4H+; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id FsgxmFUrZ4iY for ; Wed, 2 Feb 2022 08:12:31 +0000 (UTC) Received: from mail-ed1-f50.google.com (mail-ed1-f50.google.com [209.85.208.50]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id DD16D3F79E for ; Wed, 2 Feb 2022 08:12:31 +0000 (UTC) Received: by mail-ed1-f50.google.com with SMTP id p7so40076486edc.12 for ; Wed, 02 Feb 2022 00:12:31 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=GuIqtX9L9u6QfAGzfK0Wh/joyb7YNhrx99U/XDh/eyo=; b=7iomkYH3cskj8eMLbCYhEJDHylYw2NuGEbXV+LdmL1Cst0V6koDdgUSmz4coZztQoB gfVpi9j1Cw20L08xxGxf+z0Ps/ZQyrHrz7EeKymBgJ58wKeYp4eL3Fqzix0QdWpy+iOQ 87//WKO8DVC+70bhu3IOr/o1BMQus2VT5KVji6njR9Jav4LM4lb3JTDO4mH2b523oG5U Q3ab4Yo/cHg+ICcloDDDH0bcuYXdsJb+yhxD3ttwRwMymADGrKCxyqRWN73lqt4GIGqY nsGq3bwcEtTrBI2uNfgGuEOgT5Uy7lLE5tovPojYcrYyrUO/HL4j9uBCAuxdfHbD78ud Oy7g== X-Gm-Message-State: AOAM530nINRCDE5kvj29sUBXFtjW0Coi43PbKkXF4g0qpKp5BiS1Z3hd OWS8cXSoPYiqg1P6NrBbuUmJ9FeBe9gAnc9dIfi2MRo3FZ0= X-Google-Smtp-Source: ABdhPJzEPEUtYcxT50wO9eSS/sa2ThxcYkooYSp/ajD3c4Lnuz+MBA1Yx8D7gd8zxxefi2glVpX2XWhP4QWzpw7EHWU= X-Received: by 2002:a05:6402:1c95:: with SMTP id cy21mr29069538edb.170.1643789550786; Wed, 02 Feb 2022 00:12:30 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Mohamed Houri Date: Wed, 2 Feb 2022 09:12:19 +0100 Message-ID: Subject: Re: SQL Profile and baseline To: gogala.mladen@gmail.com Cc: ORACLE-L Content-Type: multipart/alternative; boundary="000000000000429c0905d704949d" X-archive-position: 81843 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mohamed.houri@gmail.com Precedence: normal Reply-To: mohamed.houri@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --000000000000429c0905d704949d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Amir I am totally in phase with Dom. And that's why* I advise you to stay with this SQL Profile if you have found that it does its job*. That=E2=80=99s said, there are situations where a SPM is self-evident. I wa= nt to talk about the case where you have a parsing problem (yes, I'm talking about parsing =F0=9F=98=80 ) but a parsing only due to ACS. In this case an= d only in this particular case of ACS, and from 12.2 onwards, if you set an SPM your query becomes not subject to ACS and stops causing parsing issues. And even in this particular situation, if you are in 19c and above you should also make sure to prevent Oracle from *evolving, automatically, new plans in the SPM*. Indeed, if a new plan is added by Oracle to your SPM baseline, Oracle will consider again that your query is likely to benefit from ACS and, the parsing problem will reappear with greater severity than before. To prevent Oracle from evolving your SPM plans you should make sure that the value of the following parameter is set to FALSE SELECT parameter_name, parameter_value FROM dba_advisor_parameters WHERE task_name =3D 'SYS_AUTO_SPM_EVOLVE_TASK' AND parameter_name =3D 'ACCEPT_PLANS' ; PARAMETER_NAME PARAMETER_VALUE ------------------ ---------------- ACCEPT_PLANS TRUE SELECT parameter_name, parameter_value FROM dba_advisor_parameters WHERE task_name =3D 'SYS_AUTO_SPM_EVOLVE_TASK' AND parameter_name =3D 'ACCEPT_PLANS' ; BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name =3D> 'SYS_AUTO_SPM_EVOLVE_TASK', parameter =3D> 'ACCEPT_PLANS', value =3D> 'FALSE'); END; / PARAMETER_NAME PARAMETER_VALUE ---------------- ---------------- ACCEPT_PLANS FALSE Best regards Mohamed Le mer. 2 f=C3=A9vr. 2022 =C3=A0 01:20, Mladen Gogala a =C3=A9crit : > On 2/1/22 13:06, Hameed, Amir wrote: > > Hi, > > As a general guideline, once a SQL profile provides an optimal execution > plan for a SQL, is it okay to create a baseline for that SQL and then dro= p > the SQL Profile? The DB version is 11.2.0.4. Any feedback will be > appreciated. > > > > Thanks, > > Amir > > Hi Amir, > > Both baselines and profiles are essentially the same thing: a bunch of > hints. In my experience, profiles provide more stable and overall better > mechanism for achieving plan stability. Baselines are poor man's profiles= . > However, if you want to nail the plan to the wall, you can also create an > outline, which is an actual execution plan which doesn't change or evolve > but stays the same forever and ever, until "drop database" takes it apart= . > Outlines are deprecated but still supported. However, I wouldn't advise > using outlines precisely because their inability to evolve. > > -- > Mladen Gogala > Database Consultant > Tel: (347) 321-1217https://dbwhisperer.wordpress.com > > -- http://www.freelists.org/webpage/oracle-l --=20 Houri Mohamed Oracle DBA-Developer-Performance & Tuning Visit My - Blog Let's Connect - *Linkedin Profile * My Twitter - MohamedHouri --000000000000429c0905d704949d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Amir

I am totally in phase with Dom. And that's why I advise you to stay with = this SQL Profile if you have found that it does its job.

T= hat=E2=80=99s said, there are situations where a SPM is self-evident. I want to talk abou= t the case where you have a parsing problem (yes, I'm talking about parsi= ng=C2=A0=F0=9F=98=80 ) but a parsing only due to ACS. In this case and only in this particular case of ACS, and from 12.2 onwards, if you set an SPM your query becomes not= subject to ACS and stops causing parsing issues.

A= nd even in this particular situation, if you are in 19c and above you should also make sure to prevent Oracle from evolving, automatically, new plans in the SP= M. Indeed, if a new plan is added by Oracle to your SPM baseline, Oracle will consider again that your query is likely to benefit from ACS and, the parsing proble= m will reappear with greater severity than before.

T= o prevent Oracle from evolving your SPM plans you should make sure that the value of = the following parameter is set to FALSE

SELECT

= =C2=A0 =C2=A0 parameter_name,
=C2=A0 =C2=A0 paramet= er_value
FROM
=C2=A0 =C2=A0 dba_advisor_parameters
WHERE
=C2=A0= =C2=A0 =C2=A0 =C2=A0 task_name =3D 'SYS_AUTO_SPM_EVOLVE_TASK'
= =C2=A0 =C2=A0 AND parameter_name =3D 'ACCEPT_PLANS'
;
=C2=A0 =
PARAMETER_NAME =C2=A0 =C2=A0 PARAMETER_VALUE
------------------ ----= ------------
ACCEPT_PLANS =C2=A0 =C2=A0 =C2=A0 TRUE

SELECT
=C2= =A0 =C2=A0 parameter_name,
=C2=A0 =C2=A0 parameter_value
FROM
=C2= =A0 =C2=A0 dba_advisor_parameters
WHERE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 t= ask_name =3D 'SYS_AUTO_SPM_EVOLVE_TASK'
=C2=A0 =C2=A0 AND parame= ter_name =3D 'ACCEPT_PLANS'
;

BEGIN
=C2=A0 =C2=A0DBMS_= SPM.SET_EVOLVE_TASK_PARAMETER(
=C2=A0 =C2=A0 =C2=A0 task_name =3D> &#= 39;SYS_AUTO_SPM_EVOLVE_TASK',
=C2=A0 =C2=A0 =C2=A0 parameter =3D>= 'ACCEPT_PLANS',
=C2=A0 =C2=A0 =C2=A0 value =3D> 'FALSE&#= 39;);
END;
/

PARAMETER_NAME =C2=A0 PARAMETER_VALUE
--------= -------- ----------------
ACCEPT_PLANS =C2=A0 =C2=A0 FALSE

=

Best regards

Mohamed=C2=A0

Le=C2=A0m= er. 2 f=C3=A9vr. 2022 =C3=A0=C2=A001:20, Mladen Gogala <gogala.mladen@gmail.com>= ; a =C3=A9crit=C2=A0:
=20 =20 =20
On 2/1/22 13:06, Hameed, Amir wrote:
=20 =20 =20

Hi,<= u>

As a genera= l guideline, once a SQL profile provides an optimal execution plan for a SQL, is it okay to create a baseline for that SQL and then drop the SQL Profile? The DB version is 11.2.0.4. Any feedback will be appreciated.

=C2= =A0

Thanks,<= /u>

Amir=

Hi Amir,

Both baselines and profiles are essentially the same thing: a bunch of hints. In my experience, profiles provide more stable and overall better mechanism for achieving plan stability. Baselines are poor man's profiles. However, if you want to nail the plan to the wall, you can also create an outline, which is an actual execution plan which doesn't change or evolve but stays the same forever and ever, until "drop database" takes it apart. Out= lines are deprecated but still supported. However, I wouldn't advise using outlines precisely because their inability to evolve. 

--=20
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbw=
hisperer.wordpress.com
-- htt= p://www.freelists.org/webpage/oracle-l


--

Houri Mohamed=C2= =A0

Oracle DBA-Developer-Performance & Tuning=C2=A0

Visit My =C2=A0 =C2=A0 =C2=A0 =C2=A0 -=C2=A0Blog

Let's Connect -=C2=A0Linkedin Profile

My=C2=A0Twitter= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -=C2=A0MohamedHouri

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