Re: Memory needed for upgrade to 18c
Date: Tue, 21 May 2019 09:19:36 +0000
Message-ID: <LO2P265MB0415CF4FC45B5BB7AD0DE18DA5070_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM>
Thanh NGUYEN,
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.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Thanh NGUYEN <ttrnguyen_at_gmail.com> Sent: 20 May 2019 08:57
To: oracle-l_at_freelists.org
Subject: Re: Memory needed for upgrade to 18c
Hello Jonathan,
Yes. We got the messages you mentionned at start up.
Best regards,
Thanh NGUYEN
2019-03-16T15:46:10.934112+01:00
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
2019-03-16T15:46:10.939816+01:00
Available system pagesizes:
4K, 64K, 16M, 16G
2019-03-16T15:46:10.947807+01:00 Supported system pagesize(s): 2019-03-16T15:46:10.951870+01:00
PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 2019-03-16T15:46:10.955982+01:00
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):
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><mailto:ttrnguyen_at_gmail.com> Sent: 18 May 2019 20:27
To: Jonathan Lewis; oracle-l_at_freelists.org<mailto: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><mailto: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><mailto: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><mailto: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><mailto: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.
- 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.
- 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><mailto: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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 21 2019 - 11:19:36 CEST