Re: [EXTERNAL] Question on sql plan management

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?

On Thu, Sep 1, 2022 at 6:42 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> Sometimes that happens because the plan should never have been allowed in
> the first place - often because someone has worked out a boundary condition
> where a transformation would produce the wrong results if some column were
> allowed to be null. So a transformation is blocked (sometimes for a few
> versions only) until the code is refined to avoid the error.
>
> Regards
> Jonathan Lewis
>
>
> On Thu, 1 Sept 2022 at 12:33, Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
> wrote:
>
>> We are upgrading an Oracle EBS database from 11.2.0.4 to 19c. During
>> final testing, we noticed some Payroll processes running longer than
>> anticipated. We created a baseline in 11g and migrated it to 19c. The 19c
>> database did NOT use the baseline. From what I read, this can happen if the
>> optimizer can not reproduce the plan in the baseline. Just passing this on
>> for what its worth.
>>
>>
>>
>

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