Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 34B1110031AF22 for ; Fri, 17 Dec 2021 10:47:27 +0100 (CET) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [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-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id DEF8B40986; Fri, 17 Dec 2021 09:47:25 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id C78C73F9C9; Fri, 17 Dec 2021 09:47:25 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1639734445; bh=BbyQPRAjzmO1jpFMchoc0Ps7XfdDp/sgFg8IOUAsZgw=; h=From:Sender:Sender:From; b=by2v/0Kwh+rm+EKo+5+Qqp9Z1o1LJQ6N2Y0R6bH82pxJHRO5nJ5booPMlH8EkKXfw 0Ly7DZUibxUgh27yXrUh0dUVayKWvaLjAADEiGRKab6VDgf7D5qWMDqUriMpXixh8C IDMS/PTZpxlIvddQPkBly+q7xZ6LoE2utZNXeNpk= 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 i-oi0D_ve5eK; Fri, 17 Dec 2021 09:47:25 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id C13083FB5A; Fri, 17 Dec 2021 09:47:22 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1639734444; bh=BbyQPRAjzmO1jpFMchoc0Ps7XfdDp/sgFg8IOUAsZgw=; h=From:Sender:Sender:From; b=NYbOk7UxCxCT2u228hfvNwKkImKYemetADIRqqb+jjRfzbKY1SErMgRD0rsaUxaRo ERDsCC3WD555pMO74SKbvy8vYkOoH74Zar84E7pNDwlBbiHwDSuGQBT+r/p3OXMzO6 1Iu8HqS5bt6ac7eYlKtfX+qo+EWVIc4NWCLNEm6M= Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 17 Dec 2021 09:47:21 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 0D6153F9C9 for ; Fri, 17 Dec 2021 09:47:21 +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=NplwipV/; 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 44erAmb3yV6P for ; Fri, 17 Dec 2021 09:47:21 +0000 (UTC) Received: from mail-ed1-f45.google.com (mail-ed1-f45.google.com [209.85.208.45]) (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 EBCCE3F7AB for ; Fri, 17 Dec 2021 09:47:20 +0000 (UTC) Received: by mail-ed1-f45.google.com with SMTP id y12so5491416eda.12 for ; Fri, 17 Dec 2021 01:47:20 -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=b1aWprzndEwDlZKOmkyAJNOk/HI/fjWTEySbpnVdigs=; b=0Z2xRaa2ZEReawhNKxsBUH3ZdpMcmmLKwdWzybfZ02vVfA6W5Q2LAUFCsY6udITh43 yz/rffa2gIuQm+4pIzelPFRkP0xEPFW+MochW/A7xjalFJEnJuTfmUNW/lkRNipURlI3 5QmSrh//Fdq2u+yLoLH4peVXBDxSmZ+6KsjFgmxQwxxgvgJq+p28ANBzRYIBbqQ4Fdsp SlXtzDr86z28ivYkhBz6ZKbNHAGSn9r+0YndyCryCRrTWRqGBUudO7a3ubOsQyBIuzTQ 2Zjovs7bTCl9SacDofGkF9xG1GZSvqzbqBAl4VnrUpRlDGdqZ55CteoZw2/JStBLVY69 lugA== X-Gm-Message-State: AOAM533ETl+1/MeAcRlRLRRg+oHtYmzd5PkF8yXqtnI5EKEB1Qz8rViy JGty5eSD3bjHwGaqC537OolBnmFxiEtanxtPA24= X-Google-Smtp-Source: ABdhPJwFdcENOJUwBNJqQeJ1FTYUG/BsatX/Bwql2DPqoGJgjjZqT7xpEyKE7rdo13kz23nVzEXj8bFZ8a+gTC0CGBA= X-Received: by 2002:a05:6402:50cf:: with SMTP id h15mr2106677edb.90.1639734439850; Fri, 17 Dec 2021 01:47:19 -0800 (PST) MIME-Version: 1.0 References: <55p-PDX85_zCh_GWlYkGZSUF9bSRVXfGiuixroaemoqVFYuFESjt3o5VTgG9z41X7fpdouFskiI4I_w0bPX7RMa1jhgRNzc0LsEwFIR56tM=@klockmail.com> In-Reply-To: From: Mohamed Houri Date: Fri, 17 Dec 2021 10:47:09 +0100 Message-ID: Subject: Re: Priority of profile baseline patch To: Pap Cc: Andy Klock , Laurentiu Oprea , Oracle L Content-Type: multipart/alternative; boundary="000000000000d039db05d3546c41" X-archive-position: 81598 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 --000000000000d039db05d3546c41 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello *In other words the sql profile or sql patch helps to create new plans based on the embedded hints however presence of baseline with accepted +enabled flag as 'YES' is always going to rule at the end. Correct if my understanding is wrong here?* The underlined part is incorrect If you look at the picture I designed in this article https://hourim.wordpress.com/2015/10/01/oracle-optimizer-and-spm-plan-inter= action/ You will realize that the presence of an SPM has *no influence on the ordinary work of the CBO*. Initially, Oracle will produce its execution plan ignoring the presence or absence of an SPM. When generating this execution plan, all things being equal, Oracle will use the set of hints contained in the SQL Profile or the SQL patch. *It is only when the execution plan has been generated that the SPM finally comes into play.* And what exactly will this SPM do? It will simply ensure that only the execution plan contained in the SPM baseline will be used regardless of how this plan has been generated, via SQL Profile, via SQL patch, or without any of those two features. Of course, this insurance against a bad surprise(a non-desired execution plan) is only possible if the plan in the SPM is reproducible at the time of the execution of the request. Hope this is clear Best regards Mohamed Houri Le ven. 17 d=C3=A9c. 2021 =C3=A0 05:18, Pap = a =C3=A9crit : > Thank you so much Andy. > > If I get it correct, it's the sql profile(which is generally a combinatio= n > of opt_estimate hints) which oracle will use to evaluate the plans but it= 's > ultimately the sqlplan baselines (which must be in accepted+enabled) stat= e > which is going to be used finally. > > And in scenarios where we use technique to manually create sql profile by > passing exact outline hints (but not with opt_etsimate hints) i.e. using > procedure dbms_sqltune.import_sql_profile where we have ability to pass > the exact full query outline hints which restricts the execution path to > one. In those cases it will mostly fix the exact one execution path for t= he > query no matter how many times it parses. And in the presence of such a s= ql > profile , if the sql plan baseline has exactly the same path > enabled+accepted that is going to be used or else it's going to be > completely discarded and the priority will be given to the baseline which > is in accepted+enabled state. (And the discarded plan i.e the parsed plan > using sql profile may be added a new baseline to the list with accepted > flag as NO). Which means sql plan baseline having enabled+accepted flag > as "YES" will take the precedence always irrespective of sql profile/sq > patch etc. Is this understanding correct? > Or > In other words the sql profile or sql patch helps to create new plans > based on the embedded hints however presence of baseline with > accepted +enabled flag as 'YES' is always going to rule at the end. Corre= ct > if my understanding is wrong here? > > On Fri, Dec 17, 2021 at 1:53 AM Andy Klock wrote: > >> Hi Laurentiu, >> >> =E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90 Original= Message =E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90 >> On Thursday, December 16th, 2021 at 3:08 PM, Pap < >> oracle.developer35@gmail.com> wrote: >> >> So in cases where the SQL profile or SQL patch has some hints (say FTS o= n >> tab1) which is exactly opposite than that of the one in SQL plan >> baseline(say index access for tab1). In that scenario will oracle follow >> profile/patch or the baseline path? >> >> >> If a cursor has "accepted" baselines, then that is typically what the CB= O >> is going to prefer. When there are multiple accepted plans, then the CB= O >> will pick the plan with the lowest cost. For cursors that also have a SQ= L >> Profile, then those hints and opt_estimates are applied which may affect >> the cost of the baseline plans that the CBO will pick. >> >> Maria wrote a nice article about it [1] in 2012 which I think is still >> relevant, but often when you have cursors with lots of baselines, SQL >> Profiles, and patches it's time to fix the stats or change the code :) >> >> Andy K >> >> [1] >> https://blogs.oracle.com/optimizer/post/what-is-the-difference-between-s= ql-profiles-and-sql-plan-baselines >> > --=20 Houri Mohamed Oracle DBA-Developer-Performance & Tuning Visit My - Blog Let's Connect - *Linkedin Profile * My Twitter - MohamedHouri --000000000000d039db05d3546c41 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hello=

=C2=A0In other words=C2=A0the sql profile or sql patch helps to create new plans based on the embedded=C2=A0hints however=C2= =A0presence of baseline with accepted=C2=A0+enabled flag as 'YES' is always goi= ng to rule at the end. Correct if my understanding is wrong here?

=C2=A0The underlined= part is incorrect

=C2=A0If you look at= the picture I designed in this article

=C2=A0https://hourim.wordpress.com/2015/10/01/oracle-optimizer-and-spm-= plan-interaction/

You will realize that the presence of an SPM has=C2=A0no influence on the ordinary work of the CBO. Initially, Oracle will produce its execution = plan ignoring the presence or absence of an SPM.

When generating this execution plan, all = things being equal, Oracle will use the set of hints contained in the SQL Profile or the SQL patch.=C2=A0= It is only when the execution plan has been generated that=C2=A0the SPM finally comes into play.

And what exactly will this SPM do?

It will simply ensure that only the execution plan contained in the SPM baseli= ne will be used regardless of how this plan has been generated, via SQL Profil= e, via SQL patch, or without any of those two features.

Of course, this insurance against a bad surprise(a non-desired execution plan)= is only possible if the plan in the SPM is reproducible at the time of the execution of the request.

Hope this is clear

Best regards

Mohamed Houri




Le=C2=A0ven. 17 d=C3=A9c= . 2021 =C3=A0=C2=A005:18, Pap <oracle.developer35@gmail.com> a =C3=A9crit=C2=A0:
Thank you = so much Andy.=C2=A0

If I get it correct, it's the sq= l profile(which is generally a combination of opt_estimate hints) which ora= cle will use to evaluate the plans but it's ultimately the sqlplan base= lines (which must be in accepted+enabled) state which is going to be used f= inally.=C2=A0

And in scenarios where we use techni= que to manually create sql profile by passing exact outline hints (but not = with opt_etsimate hints) i.e. using procedure=C2=A0dbms_sqltune.import_sql_profile where we have = ability to pass the exact full query outline hints which restricts the exec= ution path to one. In those cases it will mostly fix the exact one executio= n path for the query no matter how many times it parses. And in the presenc= e of such a sql profile , if the sql plan baseline has exactly the same pat= h enabled+accepted that is going to be used or else it's going to be co= mpletely=C2=A0discarded=C2=A0and the priority will be given to the baseline= which is in accepted+enabled state. (And the=C2=A0discarded plan i.e the p= arsed plan using sql profile may be added a new baseline to the list with a= ccepted flag as NO).=C2=A0 =C2=A0Which means sql plan baseline having enabl= ed+accepted flag as "YES" will take the precedence=C2=A0always ir= respective of sql profile/sq patch etc. Is this understanding correct?=C2= =A0
Or=C2=A0
In other words=C2=A0the sql profile or sql patch helps to = create new plans based on the embedded=C2=A0hints however presence of basel= ine with accepted=C2=A0+enabled flag as 'YES' is always going to ru= le at the end. Correct if my understanding is wrong here?

On Fr= i, Dec 17, 2021 at 1:53 AM Andy Klock <andy@klockmail.com> wrote:
Hi Laurentiu,
=

=E2=80=90=E2=80=90=E2=80=90= =E2=80=90=E2=80=90=E2=80=90=E2=80=90 Original Message =E2=80=90=E2=80=90=E2= =80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90
On Thursday, Decem= ber 16th, 2021 at 3:08 PM, Pap <oracle.developer35@gmail.com> wrote:
So in cases where the SQL profile or SQL patch has some hints (say FTS on= tab1) which is exactly opposite than that of the one in SQL plan baseline= (say index access for tab1). In that scenario will oracle follow profile/pa= tch or the baseline path?

=
If a cursor has "accepted" baselines, then that is typically= what the CBO is going to prefer.=C2=A0 When there are multiple accepted pl= ans, then the CBO will pick the plan with the lowest cost. For cursors that= also have a SQL Profile, then those hints and opt_estimates are applied wh= ich may affect the cost of the baseline plans that the CBO will pick.

Maria wrote a nice article about it [1] in 2012 whi= ch I think is still relevant, but often when you have cursors with lots of = baselines, SQL Profiles, and patches it's time to fix the stats or chan= ge the code :)=C2=A0

Andy K=C2=A0



--

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

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