Yes Mark, so my point was, we already have ~9 sqls which are using baseline
in current 11.2. version and of course
have optimizer_use_sql_plan_baselines set as TRUE(which is default). But
Now that we will be setting optimizer_capture_sql_plan_baselines to TRUE a
few days before 19C upgrade on production to get hold of all the
possible sql and their plans(say we captured ~1000 sqls baseline), but we
don't want all of those to be enabled at that same moment. rather we want
only to enable the baseline for those ~9 old sqls + additional few sql (say
e.g. 3 sqls ) which misbehaved/changed plan post upgrade to 19c. And we
want to drop/get rid of those other ~997 sql baselines from our 19C version
database (which we captured initially just for our safety). So considering
its inplace upgrade and we don't have an exact test environment like RAT,
is there a way to achieve this in the existing production database? I am
still unable to get it clearly.
On Wed, Nov 17, 2021 at 10:29 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
I thought the point was he has enabled the use of all the baselines he
currently has en masse by a default parameter, so he wants to be able to
get back to just those being stored.
Did I misread something?
*From:* Andy Sayer [mailto:andysayer@xxxxxxxxx]
*Sent:* Monday, November 15, 2021 5:29 PM
*To:* mwf@xxxxxxxx
*Cc:* Clay Jackson (cjackson); Oracle L; loknath.73@xxxxxxxxx
*Subject:* Re: Things to consider during upgrade/migration
“ As i had posted earlier, I am still wondering how to isolately enable
the sql plan baselines to only specific sqls manually rather than forcing
it to be enabled for all the captured baselines? ”
Capture ALL baselines (either with the parameter or just loop through your
application sql_ids and the dbms_spm function). Keep using baselines with
your existing parameter. You can turn off the capture just before you
upgrade if you really wanted (I leave it permanently true).
No need to do anything special with your existing baselines, just keep
them as they are (nothing will happen to them unless you manually disable
them).
Thanks,
Andy
On Mon, 15 Nov 2021 at 21:45, Mark W. Farnham <mwf@xxxxxxxx> wrote:
Or just export the current contents and use your favorite method to
restore it as the contents later…
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Lok P
*Sent:* Monday, November 15, 2021 2:53 PM
*To:* Mark W. Farnham
*Cc:* Clay Jackson (cjackson); Oracle L
*Subject:* Re: Things to consider during upgrade/migration
As i had posted earlier, I am still wondering how to isolately enable the
sql plan baselines to only specific sqls manually rather than forcing it to
be enabled for all the captured baselines?
Note- We have currently optimizer_use_sql_plan_baseline set as default
true which is helping few of the existing sqls already. So we can not turn
it false. And keeping it True is again going to make all the
captured baselines to be enabled, so how should we deal with this?
On Mon, Nov 15, 2021 at 7:22 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
AND, (not but at all),
Don’t be hesitant to discard a few false starts. Make sure your timeline
includes time for a few fresh starts.
IF you have a way to simulate the activity including transactions,
standard reports, and most common ad hoc reports for a five month period
of December, January, February, March, and April for a January 1st year
end, that will cover a year end and a quarter end, which tend to be the
most important. IF special things happen at year and quarter ends and you
don’t do this, you are in extreme violation of #3 below.
IF you have time period comparison reports for, say, last year compared
to this year and the time to shove all data conflicts with the desire for
multiple trial conversions and dress rehearsals make sure you get enough
months to make the comparison reports realistic.
IF you have a lot of moribund data (data that is no longer allowed to be
changed) consider physically re-ordering, partitioning, and making such
data isolated in read only spaces as part of the migration. As needed for
retrograde plan or iso-plan performance outliers, extra specific indexes
(and/or best cluster factor and attribute ordering to match queries of
existing indexes) may be extremely useful to improve performance. The extra
time to convert is often time well spent in both size and speed.
IF you can simulate as suggested, that may tempt you to go light on
suggestion #2 below, ignoring the things that seem fine and just snapping
the baselines that have problems in your “test migration” when everything
on the destination is fresh and new. Don’t skip suggestion #2 unless you
plan to have the old machine available for years. Transactions can change
the texture of the data over time such that plan changes erupt differently
in the new environment as time passes. IF the old machine is gone, all
those baselines are your time machine to keep life BORING. BORING is good
in IT operations. The excitement should be innovative use of well running
technology, not firefighting to keep the technology from burning up.
Good luck. JL also has a blog thread of things that can go bump in the
night when you “didn’t change anything” that is a useful read to help
THINKING CLEARLY about things that erupt during your several dress
rehearsals.
And remember: Two complete dress rehearsals with NO CHANGES before you
pull the trigger on the real migration. (Farnham’s Law of Sane Migrations,
often violated at great pain).
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Clay Jackson
("Clay.Jackson")
*Sent:* Saturday, November 13, 2021 4:36 PM
*To:* loknath.73@xxxxxxxxx; Oracle L
*Subject:* RE: Things to consider during upgrade/migration
What Mladen and Andy said – I would pay very serious attention to their
wisdom; born of many years 😊. ESPECIALLY the parts about
1. How different 19 is from 11 and the Exadata is from the HP
2. Collecting baselines for ALL SQL – the time you spend up from will
save more than that troubleshooting on the “back end”
3. TEST EVERYTHING
In my position, I talk to customers/prospects going through similar
upgrades/migrations and those who follow(ed) Steps 2 and 3 (and understood
the differences) had SIGNIFICANTLY better success than those who tried to
“shortcut” the process.
Clay Jackson
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Lok P
*Sent:* Saturday, November 13, 2021 10:47 AM
*To:* Oracle L <oracle-l@xxxxxxxxxxxxx>
*Subject:* Things to consider during upgrade/migration
*CAUTION:* This email originated from outside of the organization. Do not
follow guidance, click links, or open attachments unless you recognize the
sender and know the content is safe.
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
