Re: Memory needed for upgrade to 18c
Date: Sat, 18 May 2019 21:27:33 +0200
Message-ID: <668b0f08-4481-e4c9-bc91-ab5570a4e6de_at_gmail.com>
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 8Gunified_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.
>
>
> 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: 18 May 2019 08:17:52
> To: 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> 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-lReceived on Sat May 18 2019 - 21:27:33 CEST