Confused about automatic memory management.
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 11 Jul 2014 08:24:07 +0200
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_VALUE
-------------------------+--------------
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
from v$memory_dynamic_components
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
