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@xxxxxxxxx>
Sent: 18 May 2019 20:27
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx><mailto:oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Thanh NGUYEN <ttrnguyen@xxxxxxxxx><mailto:ttrnguyen@xxxxxxxxx>
Sent: 18 May 2019 08:17:52
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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: @OracleSK
Thanh NGUYEN
<ttrnguyen@xxxxxxxxx><mailto:ttrnguyen@xxxxxxxxx><mailto:ttrnguyen@xxxxxxxxx><mailto:ttrnguyen@xxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
