RE: Question about SGA memory allocation and sizing

From: Walker, Jed S <Jed_Walker_at_cable.comcast.com>
Date: Wed, 5 Sep 2012 16:50:59 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C6F47A649_at_COPDCEXMB08.cable.comcast.com>

The "fixed" portion is just oracle tracking itself. The variable size is the various pools. Oracle has put the other 85GB there to manage your shared, large, java, etc. pools. Tom Kyte describes it here http://asktom.oracle.com/pls/asktom/f?p=100:11:26779324172700::::P11_QUESTION_ID:365088445659

I wonder if you really need the amount of memory you have allocated. You might want to look at some of the "advice" views - v$sga_target_advice, v$memory_target_advice, etc and check the spfile/init file __ parameters for what oracle is using. The more memory allocated the more work for the OS and Oracle to manage it, which segues to

If you do use that large of an SGA you might want to consider using Huge Pages. When I implemented on a large memory system (memory target 64GB) I found a performance improvement. I have steps for that here http://tinky2jed.wordpress.com/technical-stuff/oracle-stuff/configuring-hugepages-for-oracle-database/

I hope that helps

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Hitchman Sent: Wednesday, September 05, 2012 10:16 AM To: oracle-l
Subject: Question about SGA memory allocation and sizing

Hi,
This is a 11.2 database with Automatic Memory Management with a memory_target and memory_max_target of 101GB. Looking at v$sga I see this:

select name, value/1024/1024/1024
from v$sga
/

NAME                  VALUE/1024/1024/1024
-------------------- --------------------
Fixed Size                              0
Variable Size                          85
Database Buffers                       15
Redo Buffers                            0

Then looking at v$sga_resize_ops:

select component

, AVG(FINAL_SIZE)/1024/1024/1024 "AVG FINAL", MEDIAN(FINAL_SIZE)/1024/1024/1024 "MEDIAN FINAL", MAX(FINAL_SIZE)/1024/1024/1024 "MAX FINAL" from v$sga_resize_ops

group by component

/

COMPONENT                           AVG FINAL        MEDIAN FINAL           MAX

FINAL

• ------------------- -------------------

  ---

ASM Buffer Cache                            0                   0

0

DEFAULT 16K buffer cache                    0                   0

0

DEFAULT 2K buffer cache                     0                   0

0

DEFAULT 32K buffer cache                    0                   0

0

DEFAULT 4K buffer cache                     0                   0

0

DEFAULT 8K buffer cache                     0                   0

0

DEFAULT buffer cache                       21                  21

26

KEEP buffer cache                           0                   0

0

RECYCLE buffer cache                        0                   0

0

java pool                                   0                   0

0

large pool                                  0                   0

0

*shared pool                                14                  14

20*

streams pool                                1                   1

1

Given that the shared pool has a max value of 20GB and the other pools are effectively 0, that the rest of the space for the variable part of the SGA is free?

If so, why did Oracle decide to allocate 85GB?

Thanks

Pete

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 05 2012 - 11:50:59 CDT