Re: [EXTERNAL] Question on sql plan management
Date: Sat, 3 Sep 2022 23:39:46 +0530
Message-ID: <CAEzWdqdfqdn5PVth2=kM0NkOFYLNhXJbM854wCknHhoJOO2dbg_at_mail.gmail.com>
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?
>>
>>>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 03 2022 - 20:09:46 CEST