Hi all,
I'm about to build a new production database to migrate an existing 8.1.7 database to 10.2.0.3. I'm in the enviable position of having a good chunk of memory to play with on the new system (compared with the existing one) so was looking at a suitable size for the SGA... when something pinged in my memory about SGA_MAX_SIZE and memory allocation in the OS where some platforms will allocate the entire amount of SGA_MAX_SIZE rather than just SGA_TARGET.
So I did a little test. Using Solaris 10 and Oracle 10.2.0.3 I've created a basic database with SGA_MAX_SIZE set to 400MB and SGA_TARGET 280MB
$ sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jan 30 18:31:21 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 400M
sga_target big integer 280M
So I was expecting to see the OS pre-allocate 280MB of memory but when I checked the segment is actually the 400MB (i.e. SGA_MAX_SIZE) (my database owner is 'ora10g'):
$ ipcs -a
IPC status from <running system> as of Wed Jan 30 18:31:36 GMT 2008
T ID KEY MODE OWNER GROUP CREATOR
CGROUP CBYTES QNUM QBYTES LSPID LRPID STIME RTIME CTIME
Message Queues:
T ID KEY MODE OWNER GROUP CREATOR
CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME
Shared Memory:
m 22 0x2394e4 rw-r--- ora10g 10gdba ora10g
10gdba 20 419438592 2386 2542 18:31:22 18:31:28 18:28:18
T ID KEY MODE OWNER GROUP CREATOR
CGROUP NSEMS OTIME CTIME
Semaphores:
s 23 0x89a070e8 ra-r--- ora10g 10gdba ora10g
10gdba 154 18:31:31 18:28:18
$
I wasn't sure whether Solaris 10 was one of the OSs with truly dynamic memory for the SGA but had hoped it was... this seems to say different. Really I'm just after some confirmation that I'm reading this correctly.
Thanks.
Joseph
----------------------------
Message was edited by:
Joseph Crofts
Edited for clarity