Re: Things to consider during upgrade/migration

Thank you Andy. In existing databases , our thought process was to not create any SQL profile/baseline/patches unless until it's really required. As they will get detached in case of application code change and give us surprises. So we mostly try to fix statistics wherever possible, so that optimizer would be able to do the best decision for us.

But I understand , in real life this doesn't happen always and we endup creating baseline/profiles in case certain plan changes where it's unavoidable and not much options to chage code. But as a long-term we suggest hints and then drop the profile/baselines.

 But in this upgrade case as you suggest creating baseline for all the SQL queries in the database . So wondering if that is recommend way and will not have negative impact in long term? Or may be I am not fully able to understand your point. Can you explain bit more about this?

And one of the challenge we normally face wrt test environment is the absence of volume of data and thus stats as it's in production. So many a time that test doesn't really mimic production scenario. And I believe the only exact test would be to have a real application test environment with capture and replaying the exact production load. Please correct me if wrong.

On Sun, 14 Nov 2021, 12:44 am Andy Sayer, <andysayer_at_gmail.com> wrote:

> If you want to retain performance then that is primarily going to mean
> doing the same execution plans (whether or not they could be improved by
> Exa magic). I would recommend gather sql plan baselines for every statement
> that gets executed by your application.
>
> Once you’ve upgraded, you can start with sql plan evolution to improve any
> plans that can be improved. Note that this is now automatically done so you
> will want to check the parameters for this make sense.
>
> Obviously, number one must have requirement is a test environment that can
> be properly signed off by all relevant people in your org before you start
> thinking about production upgrades.
>
> Thanks,
> Andy
>
> On Sat, 13 Nov 2021 at 18:46, Lok P <loknath.73_at_gmail.com> wrote:
>
>> Hello Listers, With respect to having a safe upgrade(say from 11.2 to
>> 19C) or migration(From HP to Exadata) experience with minimal performance
>> issues. Is there any guideline we should follow like setting up exadata
>> system stats in case the target database is going to be exadata, Or
>> verifying dictionary stats/table stats etc in a certain way. Want to know
>> experts' views, if there are any such guidelines?
>>
>> Regards
>>
>> Lok
>>
>

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