The title of this post sounds a bit weird: Less is more – faster Oracle database upgrades with less memory. Just a few weeks ago, I posted about How long will my upgrade take … revisited. And Daniel adds a very useful piece to the upgrade speed topic. Read about his tests below.

Photo by Franck V. on Unsplash
Less is more – faster Oracle database upgrades with less memory
by Daniel Overby Hansen, Senior Principal Product Manager,
Oracle Database Upgrades and Cloud Migrations

Daniel Overby Hansen
Mike and I had a talk with a customer the other day that told us they reduced the size of the SGA and PGA during upgrades. It makes their database restarts faster, and, thus, reduces the overall downtime needed for an upgrade. During an upgrade they’d recognized that the database will be restarted multiple times. Each minute of downtime was very important to them, and they wanted to save every second they could.
I decided to investigate what benefits there could be from such a tweak. Before we jump right into it please observe the following:
- A database restart consists of a shutdown and a startup. During upgrades the database is started in upgrade mode which disables a lot of things inside the database.
- A database upgrade only touches data from Oracle maintained users, e.g. SYS and SYSTEM. An upgrade never touches user data. It can be verified by doing a database upgrade with all user tablespaces in read-only mode.
- The data from Oracle maintained users comprise only a fraction of the total amount of data. Thus, there is really no need for e.g. a huge buffer cache.
- A database upgrade will issue several database restarts. In my tests I counted seven restarts if you include the time zone file upgrade.
.
Details of Investigation
- I used “wristwatch” measurements, i.e. this is not a scientific test.
- I used bare metal servers in Oracle Cloud Infrastructure (52 cores and 768 GB of memory).
- I did a series of restarts in succession and took the average.
- I had two platforms that I could use – Oracle Linux 7 and Windows Server 2016.
- I have tested with Automatic Shared Memory Management (ASSM – setting only
SGA_TARGET
andPGA_AGGREGATE_TARGET
) and with Automatic Memory Management (AMM – setting onlyMEMORY_TARGET
). When using AMM the setting is the sum of the comparable SGA/PGA setting, e.g. 32G/8G SGA/PGA compares to 40G MEMORY_TARGET.
On a side note, I was a little astonished when I logged on to the server the first time. It is not every day that I can play with such resources. So much CPU and memory. So many interesting things to do – and I just did restarts! No more talking, the numbers are in…

SGA/PGA size impact on database startup timings
So, what can we learn from this?
On Oracle Linux it doesn’t really make much of a difference.
On Windows on the other hand, there is for sure something to gain.
- Be sure to use Large Pages. It is not the default, but it is nevertheless a good idea.
- If you have a large memory allocation then you can actually save minutes.
- Using AMM (
MEMORY_TARGET
) is not a good idea either. Plus, there are many other good reasons for not using AMM.
Interestingly, you see how much faster Oracle Linux can do the database restarts. This is just another good argument for using Oracle Linux, I guess.
As always – be sure to test this “at home” in your environment but you try it out in production.
I will return in a later blog post and show how you can configure AutoUpgrade to make the reduction for you automatically.
More Information and Links
- How long will my upgrade take … revisited (Mar 17, 2020)
- Welcome onboard, Daniel Overby Hansen, as Cloud Migration PM (Jan 6, 2020)
- Save upgrade downtime, upgrade APEX upfront (Jun 6, 2014)
–Mike
Daniel/Mike, “there are many other good reasons for not using AMM”, why?
I was told by one of the Oracle support guys long time ago to setup my db with AMM, but you saying Using AMM (MEMORY_TARGET) is not a good idea?
can you explain?
If there are many other good reasons for not using AMM, why Oracle does not eliminate this option?
Regards,
jb
Hi Jorge,
at first, you block the allocation of a larger SGA.
Next, internally, the calls to allocate RAM are differently, and extra tasks are necessary.
And third, believe us, you shouldn’t use memory_target 🙂 It just makes things not work as well as with sga_target and pga_aggregate_target instead.
Cheers,
Mike
Shutting down Oracle with large pages on AIX was such a pain in the past, it used to take 30-40 minutes to return all the pages back to the pool with 4TB of large pages, so have used this workaround of running with small sga for maintenance and upgrades and it worked like charm.
IBM fixed the issue few years back, so this workaround is not required anymore.
Thanks Rahul!
Cheers,
Mike
Mike,
thanks for getting back to me. Could it be that Oracle Support told me to use AMM because I am running Oracle on Windows?
Regards,
jorge
Oh well, I have no control on what Oracle Support tells you.
Not only I disrecommend it for multiple reasons.
Cheers,
Mike
I will start moving away from AMM..
thanks
Regards,
jorge
Hi Mike,
I have been using AMM since … well … since it was created and never had problem with it (on windows). I was using the other memory management under 11.2.0.4 (ASMM) (with setting SGA targer to 0 and let oracle do the thing for me) and never had problems as well. If oracle has to take more memory than memory_target is set to, then it does. I have some databases running with memory_max_target set to 6Gb and the windows process if far over 8 Gb … It’s quite constantly (well, most of the time for “hungry” databases) over the target. I know, this is only a target. So why not using AMM ? I find it really cool to use, far better than setting parameters all the time. Did it change in 19.9 version ? Because I was checking PGA and it seems that it consumes far more PGA than it does in 12.1 …
Hi Joel,
memmory_target IMHO is not a good parameter. It has (unnecessary) limits and does mem allocation differently.
You won’t find many people out there recommending it but instead a lot who’ll tell you: “Please use sga_target and pga_aggregate_target instead!”.
My recommendation from 2016 still applies:
https://mikedietrichde.com/2016/03/04/parameter-recommendations-for-oracle-database-12c-part-i/
Cheers,
Mike