Confused about automatic memory management.
Date: Fri, 11 Jul 2014 08:24:07 +0200
Message-ID: <53BF8307.2080504_at_mgm-tp.com>
Hello,
I'm trying to investigate a slow development server where Oracle does not seem to use the memory as efficient as I'd wish it would do.
The server has 6 cores (with hyperthreading) and 64GB of RAM running Windows Server 2008R2 Oracle version is: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
I have the following parameters
NAME | DISPLAY_VALUEfrom v$memory_dynamic_components
-------------------------+--------------
hash_area_size | 8388608 hi_shared_memory_address | 0 lock_sga | FALSE memory_max_target | 58G memory_target | 56G pga_aggregate_target | 4G pre_page_sga | FALSE sga_max_size | 58G sga_target | 0 sort_area_retained_size | 0 sort_area_size | 8388608 workarea_size_policy | AUTO select component, min_size/1024/1024 as min_size_mb, max_size/1024/1024 as max_size_mb
where component in ('PGA Target', 'SGA Target');
returns the following:
COMPONENT | MIN_SIZE_MB | MAX_SIZE_MB
-----------+-------------+------------
SGA Target | 17152 | 23296 PGA Target | 28416 | 40320
The first thing that I'm a bit unsure is the huge deviation from pga_aggregate_target. Even though I have set 4GB for that, Oracle still allocates more than 17GB for it.
When looking at v$pga_target_advice:
select pga_target_for_estimate,
pga_target_factor,
estd_time,
estd_pga_cache_hit_percentage
from v$pga_target_advice
PGA_TARGET_FOR_ESTIMATE | PGA_TARGET_FACTOR | ESTD_TIME | ESTD_PGA_CACHE_HIT_PERCENTAGE
------------------------+-------------------+-----------+------------------------------
4647288832 | 0.13 | 5566 | 100
9294577664 | 0.25 | 5566 | 100
18589155328 | 0.5 | 5566 | 100
27883732992 | 0.75 | 5566 | 100
37178310656 | 1 | 5566 | 100
44613971968 | 1.2 | 5566 | 100
52049634304 | 1.4 | 5566 | 100
59485296640 | 1.6 | 5566 | 100
66920958976 | 1.8 | 5566 | 100
74356621312 | 2 | 5566 | 100
111534931968 | 3 | 5566 | 100
148713242624 | 4 | 5566 | 100
223069863936 | 6 | 5566 | 100
297426485248 | 8 | 5566 | 100
it seems that 4GB would be enough (and that's what I based my setting for pg_aggregate_target on)
Now what I am confused about is the different information get from the various tools.
When I open the Memory Advisor in Enterprise Manager, it shows this picture:
https://i.imgur.com/4bJy21i.png
Note that the PGA tab reads:
Aggregate PGA Target (GB) 4
Current PGA Allocated (KB) 651315
Maximum PGA Allocated (KB) 4185826
But the "Allocation history" graph shows about 20GB for the PGA
So why is this difference there?
The memory section in the AWR report draws another different (at least for me) picture:
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
------------ ------------
Host Mem (MB): 65,501.4 65,501.4
SGA use (MB): 22,784.0 23,296.0
PGA use (MB): 644.4 680.0
% Host Mem used for SGA+PGA: 35.77 36.60
According to that only 36GB are used (which is pretty much what the ProcessExplorer is showing as well).
However "select name,bytes from v$sgainfo" displays the following:
NAME | BYTES
--------------------------------+------------
Fixed SGA Size | 2296424
Redo Buffers | 122322944
Buffer Cache Size | 16911433728
Shared Pool Size | 6710886400
Large Pool Size | 402653184
Java Pool Size | 134217728
Streams Pool Size | 134217728
Shared IO Pool Size | 536870912
Granule Size | 134217728
Maximum SGA Size | 61998993408
Startup overhead in Shared Pool | 284347944
Free SGA Memory Available | 37580963840
Which confirms that there are 35GB of memory unused by Oracle. As the system is I/O bound (the wait class "User I/O" makes about 20-30% of all waits in the system). I wonder why Oracle doesn't use those 35G to increase the buffer cache.
But when the instance is under load (mainly during nightly batch jobs) ADDM shows a warning:
The Oracle instance memory (SGA and PGA) was inadequately sized, causing additional I/O and CPU usage. Action: Increase memory allocated to the instance by setting the parameter "memory_target" to 64512 M.
The recommendation is not really a good one, as that would mean using all the physical memory for Oracle, leaving nothing for the operating system. But at the same time Oracle still report roughly 35G as "Free SGA Memory Available".
So if Oracle sees I/O as a problem, then why aren't those 35GB used? And how can I make it use them?
I have the suspicion that I have totally misunderstood how Oracle's memory management works....
Thanks
Thomas
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 11 2014 - 08:24:07 CEST