Less is more – faster Oracle database upgrades with less memory

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 and PGA_AGGREGATE_TARGET) and with Automatic Memory Management (AMM – setting only MEMORY_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