Re: Memory needed for upgrade to 18c

From: Jonathan Lewis <>
Date: Tue, 21 May 2019 09:19:36 +0000
Message-ID: <LO2P265MB0415CF4FC45B5BB7AD0DE18DA5070_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM>


Thanks for the response.

It had crossed my mind that a small change in memory requirements might be just enough to make the O/S switch from large pages to small pages, resulting in far more memory being used for memory maps. This can happen without anyone noticing in Linux systems, but your AIX system is using a very small number of very large pages to map memory, so wouldn't be suffering from that problem.

Unfortunately each Oracle upgrade seems to use more memory per process, and have more background processes running - so the change may simply be a load of new features that you might be able to switch off. Apart form that I'd follow up Jeff's comment or use Stefan's suggestion to do a detailed analysis of a couple of key processes.

Jonathan Lewis

From: <> on behalf of Thanh NGUYEN <> Sent: 20 May 2019 08:57
Subject: Re: Memory needed for upgrade to 18c

Hello Jonathan,

Yes. We got the messages you mentionned at start up.

Best regards,


Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

 Available system pagesizes:
  4K, 64K, 16M, 16G

 Supported system pagesize(s):


        4K Configured 10 10 NONE 2019-03-16T15:46:10.958487+01:00

       64K Configured 69 69 NONE 2019-03-16T15:46:10.961192+01:00

       16M Configured 512 512 NONE 2019-03-16T15:46:10.964391+01:00

       16G Configured 0 0 NONE 2019-03-16T15:46:10.967845+01:00

On 5/19/19 12:44 PM, Jonathan Lewis wrote:

I'm not hugely familiar with the special behaviour of AIX, so this may be irrelevant, but do you have something similar to either of the following in the alert logs:

Sat May 18 15:13:49 2019
 Available system pagesizes:
  4K, 2048K
 Supported system pagesize(s):

        4K       Configured               3               3        NONE
     2048K             1000             825             825        NONE

Sun May 19 11:06:13 2019
 Available system pagesizes:
  4K, 2048K
 Supported system pagesize(s):

        4K       Configured               3          421891        NONE
     2048K                0             825               0        NONE

Jonathan Lewis

From: Thanh NGUYEN <><> Sent: 18 May 2019 20:27
To: Jonathan Lewis;<> Subject: Re: Memory needed for upgrade to 18c

Hello Jonathan,

As usual, your questions are pertinent :-)


In fact, we have a lot of oracle instances and 2 P8/Aix LPARs have more than 20 single instances. They are the only 2 that we get troubles. The server I mentionned is one of the 2.

2 years and a half ago, after migrating all instances from to, the server was out of memory, swapped then crashed.

Since that moment, the incident management of our customer looks at memory graphes below (generated from nmon data) every day then we have to explain to them why such a amount of memory is needed.

Before the first upgrade (from 11g to 12c), there was only 70GB of memory allocated to the partition and every thing ran fine.

We need now 230GB of memory with more or less the same number of instances.

When I said 2GB of memory increased, it was at the used/free memory of the server with observations. It does not correspond to any percentage, just around 2GB more used after one upgrade independantly to the SGA size of the instance.

As I said in another e-mail, I monitored PGA usage and I saw that it was increased after the upgrade but the most was around 400MB.

Thus memory is used more but where?

What I expect is solution to reduce the memory usage or even more modestly explanations why we need more memory. We have a lot of memory so it is not a problem to allocate more to servers.

2) The 22 instances run in the one LPAR but they have their own binaries and their own network alias/IP address for the listener and so on. Every time, we do 22 upgrades.

3) We not have issue with CPUs. The partition has 2.5 entitled CPUs and can get more if needed in the shared pool (graph below).

4) Yes. I did compare every thing mostly about memory. At startup, the SGA remains the same before and after any upgarde, at any level including alert.log. AIXTHREAD_SCOPE is set to "S" and filesystemio_options is set to "SETALL".

5) Please below the information again one instance upgraded about sgastat.

Thank you in advance for your help.

Best regards,


SQL> select pool, sum(bytes) from v$sgastat group by pool order by 1;

-------------- ----------

java pool       117440512
large pool      117440512
shared pool    2701131776

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 8G
sga_min_size                         big integer 0
sga_target                           big integer 8G
unified_audit_sga_queue_size         integer     1048576

Real Memory Graph for system hsv0153s

from monday 00:00 (01 may 2017) to saturday 00:00 (18 may 2019)


Memory Use Graph for system hsv0153s

from monday 00:00 (01 may 2017) to saturday 00:00 (18 may 2019)


AIX CPU Graph for system hsv0153s

from monday 00:00 (01 may 2017) to saturday 00:00 (18 may 2019)


On 5/18/19 11:28 AM, Jonathan Lewis wrote:

What are you doing to check the memory usage of each instance ? Is this just the shared memory or are you doing something to include the actual memory usage of all the processes attached to the SGA as well ? Is the increase apparent from Oracle's perspective or is there something about Oracle's use of memory which has changed the way it's O/S memory pages that has led to more memory being allocated for the O/S memory map ? (Possible clues in the alert log if you've got old and new.)

You say 2GB more per instance - how much memory was each instance using before the upgrade (to give us some idea of the percentage increase). Do you have any record of memory allocation on startup for the databases you've upgraded - it's almost always the case that Oracle Corp. finds some new "overhead" to stuff into the code as you upgrade. 18c has

You say you have 22 single instances on that machine - are they all running separate virtual machines (please translate to AIX-equivalent terminology) or do you have many virtual machines with one (or more) instances per machine. (And can you tell us something about the total CPU count and memory of the hardware).

You say that the parameters are the same - but are you comparing all the parameters that you DIDN'T set that may have changed their default values. Again a comparison of old and new alert logs may help. As a quick check for large scale changes you could compare the before and after results of

select pool, sum(bytes) from v$sgastat group by pool order by 1;

This MIGHT highlight a significant change.

Jonathan Lewis

From:<><><> <><><><> on behalf of Thanh NGUYEN <><><><> Sent: 18 May 2019 08:17:52
To:<><><> Subject: Re: Memory needed for upgrade to 18c

Hello Stefan,

Thank you very much for your answer.

  1. We did not change any parameter related to memory. Furthermore, we got the same issue (memory usage increased), 2 years ago, when we upgraded from to
  2. We have 22 single instances at that server. It will take time if we determine the the consumption of each process.

Do you have any simpler idea?

Best regards,


$ ps -ef|wc

    3423 30419 238850
8:58:13 z004969:/users/ucib/z004969
$ ps -ef|grep smon|grep -v grep|wc

      22 198 1498

On 5/17/19 9:42 AM, Stefan Koehler wrote:

Hello Thanh,
ah AIX and Oracle is like Tom and Jerry ;-)

Maybe the good old "USLA heap" problem is back again. However if you have not changed any parameter (e.g. SGA_TARGET, MEMORY_TARGET, PGA_AGGREGATE_TARGET) and you are not using more (over-allocated) PGA than before than it is very likely caused by heap memory consumption of each Oracle process - you can check the memory and break it down with "svmon" very easily ( ).

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website:
Twitter: _at_OracleSK

Thanh NGUYEN <><><><><><><><> hat am 16. Mai 2019 um 21:31 geschrieben:

Aix 7.2 ML03
We upgraded 5 instances from to 18c, without changing any parameter.
Then we observed that memory is used more than before the upgrade, nearly 2GB more for each.
Do you have any experience/explanation about that please? Thank you in advance.
Best regards,



Received on Tue May 21 2019 - 11:19:36 CEST

Original text of this message