Re: [EXTERNAL] Question on sql plan management
Date: Mon, 5 Sep 2022 09:24:55 +0200
Message-ID: <CAJu8R6hD+zDAutLREvo9Mz20soRsC7qnEH6GrS=A3oez+e5VtQ_at_mail.gmail.com>
Hello,
So, in 19c, you will be in one of the two following situations:
*How should we be able to make use of 19C features or plans on top of the existing baselines post migration? *
If you are in situation n°1 it means that your queries are processed by Oracle in the same way in both releases 12c and 19c
However, if you are in situation n°2 b), then it is possible that the new 19c plan is better (or worse). And this is where you need to strategize. Opt for stability and keep using the SPM 12c plans (you will have to prevent Oracle from evolving the 19c plan by accepting it as a new valid plan in the baseline), or let Oracle automatically evolves the 19c plan
But I think that you have opted for a bad strategy which consists in capturing execution plans of all queries executed twice. This will fill the SYSAUX tablespace and introduce considerable parsing effects, especially for queries that change plans too often (due to ACS for example). If the new 19c plan is not present in the SPM baseline and this SPM baseline contains more than 2 ACCEPTED AND ENABLED plans, then all those execution plans will enter in competition to determine the best one. And you can imagine that this is not free in terms of CPU and Cursor Pin S wait on X and library cache lock wait events.
Best regards
Mohamed Houri
Le sam. 3 sept. 2022 à 20:10, yudhi s <learnerdatabase99_at_gmail.com> a écrit :
> Thank You Jonathan.
> Actually we were not having an environment which is close to production in
> terms of data volume and data pattern and infrastructure. And currently we
> have turned on the "capture baseline" flag to TRUE in production itself
> which is on 12.1 version and we already have hundreds of thousands of sql
> baselines getting captured in production on a daily basis.And i noticed the
> "use baseline" flag is already set as default TRUE. And we are planning to
> set the capture baseline flash now to FALSE as we have all types of
> workloads covered till now. In this situation, how should we be able to
> make use of 19C features or plans on top of the existing baselines post
> migration?
>
> On Sat, Sep 3, 2022 at 8:49 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> One fairly standard approach is to test on a full-size backup copy of
>> production with no SQL baselines to find the statements that show much
>> worse performance in 19c, then go back to the 11g versions and capture
>> plans for only those statements before the live upgrade.
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Sat, 3 Sept 2022 at 14:21, yudhi s <learnerdatabase99_at_gmail.com>
>> wrote:
>>
>>> Thank you All.
>>> I understand for some of the specific scenarios like fixing wrong
>>> results related to bugs from some transformation etc, it may happen that a
>>> certain plan is not reproducible exactly on 19c which was there in 11.2
>>> /12.1 versions. In these cases in the19c version, it will come up with a
>>> new plan which will eventually not match with any of the accepted old
>>> baselines and thus the optimizer will forcibly choose a different path
>>> altogether and create that as a new accepted baseline.
>>>
>>> However, doesn't it sound odd that in cases where, of all those hundreds
>>> of thousands of sqls using one of the captured baselines from 12.1 by auto
>>> capture process which are now in an accepted state from the 12.1 version,
>>> has to be manually evolved and accepted to use the 19C feature or new
>>> plans? Considering enabling auto evolve jobs as riskier to go for. So I
>>> wanted to understand what is the standard or lesser riskier suggested way
>>> of doing this when moving to the 19c version?
>>>
>>>>
-- Houri Mohamed Oracle DBA-Developer-Performance & Tuning Visit My - Blog <http://www.hourim.wordpress.com/> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>* My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri <https://twitter.com/MohamedHouri> -- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 05 2022 - 09:24:55 CEST