Question about SGA memory allocation and sizing
From: Peter Hitchman <pjhoraclel_at_gmail.com>
Date: Wed, 5 Sep 2012 17:15:45 +0100
Message-ID: <CAPMSPxMm4tJ2UMapdHWBMB3xQ6Lctgs_gwLoUYCtBuzCO+LwLg_at_mail.gmail.com>
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:
Date: Wed, 5 Sep 2012 17:15:45 +0100
Message-ID: <CAPMSPxMm4tJ2UMapdHWBMB3xQ6Lctgs_gwLoUYCtBuzCO+LwLg_at_mail.gmail.com>
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 MAXFINAL
- ------------------- -------------------
ASM Buffer Cache 0 00
DEFAULT 16K buffer cache 0 00
DEFAULT 2K buffer cache 0 00
DEFAULT 32K buffer cache 0 00
DEFAULT 4K buffer cache 0 00
DEFAULT 8K buffer cache 0 00
DEFAULT buffer cache 21 2126
KEEP buffer cache 0 00
RECYCLE buffer cache 0 00
java pool 0 00
large pool 0 00
*shared pool 14 1420*
streams pool 1 11
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-lReceived on Wed Sep 05 2012 - 11:15:45 CDT