Re: Confused about automatic memory management.
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: thomas.kellerer@xxxxxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 11 Jul 2014 08:14:05 -0700
Hello Thomas
If your question is, "why the memory advisors prefer to allocate more
memory for PGA instead of SGA?", then that is probably some bug in the code
causing overallocation to PGA.
I have a few questions before I can recommend :
1. Can you share the top wait events section from AWR report?
2. Does your application use PX execution extensively? Does the execution
plans have heavy full table scans on bigger tables or more OLTP? Remember
that FTS might trigger direct path reads and require more PGA.
If I were you (knowing that the supplied data is inadequate), I would set
lower bound values for db_cache_size, shared_pool_size, large_pool_size,
java_pool_size, etc. Memory advisors will honor that lower bound and keep
at least that much memory for that area. This will prevent overallocation
to PGA from SGA.
Also, if there is a bigger PGA allocated, RDBMS code seems to have
tendency to trigger more direct path reads, which in turn, might need more
memory for PGA, a cyclic, invalid logic. I have seen, a few databases
suffering from this chronic issue.
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance,
RAC and EBS
Blog: http://orainternals.wordpress.com/
Oracle ACE Director and OakTable member <http://www.oaktable.com/>
Co-author of the books: Expert Oracle Practices
<http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL,
<http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC
Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices
<http://tinyurl.com/book-expert-plsql-practices>
<http://tinyurl.com/book-expert-plsql-practices>
On Thu, Jul 10, 2014 at 11:24 PM, Thomas Kellerer <
thomas.kellerer@xxxxxxxxxx> wrote:
> 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:
