Re: Memory needed for upgrade to 18c

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 19 May 2019 10:44:30 +0000
Message-ID: <LO2P265MB04151FCA1BC7EA888D3901C1A5050_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM>


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):
  PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(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):
  PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)

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


Regards
Jonathan Lewis



From: Thanh NGUYEN <ttrnguyen_at_gmail.com> Sent: 18 May 2019 20:27
To: Jonathan Lewis; oracle-l_at_freelists.org Subject: Re: Memory needed for upgrade to 18c

Hello Jonathan,

As usual, your questions are pertinent :-)

1)

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 11.2.0.4 to 12.1.0.2, 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,

Thanh NGUYEN


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

POOL SUM(BYTES)
-------------- ----------

java pool       117440512
large pool      117440512
shared pool    2701131776
               5653920192

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)

[http://azphrl01.bsni.ch.ibm.com/nmon_grapher//tmp/tmpgraph.15742.png]

Memory Use Graph for system hsv0153s

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

[http://azphrl01.bsni.ch.ibm.com/nmon_grapher//tmp/tmpgraph.61357.png]

AIX CPU Graph for system hsv0153s

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

[http://azphrl01.bsni.ch.ibm.com/nmon_grapher//tmp/tmpgraph.29635.png]

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.

Regards
Jonathan Lewis



From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org> on behalf of Thanh NGUYEN <ttrnguyen_at_gmail.com><mailto:ttrnguyen_at_gmail.com> Sent: 18 May 2019 08:17:52
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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 11.2.0.4 to 12.1.0.2.
  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,

Thanh NGUYEN


$ 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 ( http://www-01.ibm.com/support/docview.wss?uid=isg3T1012834 ).

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

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

Hello,
Aix 7.2 ML03
We upgraded 5 instances from 12.1.0.2 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,
Thanh NGUYEN

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






--
http://www.freelists.org/webpage/oracle-l
Received on Sun May 19 2019 - 12:44:30 CEST

Original text of this message