Maximum SGA – Oracle 11g 64 bit – Centos 5.2 64 bit

28 Oct

SGA more than default oracle install ?
SGA >= 4GB ?

1. Hardware
a. processor Intel(R) Pentium(R) Dual CPU E2140 @ 1.60GHz
b. memory 4 GB

[oracle@test ~]$ uname -a
Linux test 2.6.18-92.el5 #1 SMP Tue Jun 10 18:51:06 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux

[oracle@test ~]$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Pentium(R) Dual CPU E2140 @ 1.60GHz
stepping : 13
cpu MHz : 1200.000
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl est tm2 cx16 xtpr lahf_lm
bogomips : 3202.49
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Pentium(R) Dual CPU E2140 @ 1.60GHz
stepping : 13
cpu MHz : 1200.000
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 2
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl est tm2 cx16 xtpr lahf_lm
bogomips : 3199.98
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:

[oracle@test ~]$ cat /proc/meminfo
MemTotal: 3341292 kB
MemFree: 42596 kB
Buffers: 18620 kB
Cached: 2872044 kB
SwapCached: 0 kB
Active: 691784 kB
Inactive: 2377968 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 3341292 kB
LowFree: 42596 kB
SwapTotal: 4610644 kB
SwapFree: 4610644 kB
Dirty: 100 kB
Writeback: 0 kB
AnonPages: 179048 kB
Mapped: 253188 kB
Slab: 37744 kB
PageTables: 31056 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 6281288 kB
Committed_AS: 3400880 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 266332 kB
VmallocChunk: 34359471099 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 2048 kB

2. Default spfile
a. /dev/shm : 1.6 GB
b. memory

memory_max_target : 1600M
memory_target : 1600M
sga_max_size : 1600M

[oracle@test ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda2 9.5G 5.5G 3.6G 61% /
/dev/hda6 40G 177M 38G 1% /u02
/dev/hda5 19G 6.8G 12G 38% /u01
/dev/hda1 99M 12M 83M 12% /boot
tmpfs 1.6G 960M 672M 59% /dev/shm

[oracle@test ~]$ sqlplus / as sysdba
SQL> startup;
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size 2144984 bytes
Variable Size 922748200 bytes
Database Buffers 738197504 bytes
Redo Buffers 7131136 bytes
Database mounted.
Database opened.
SQL> show parameter target

NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 1600M
memory_target big integer 1600M
pga_aggregate_target big integer 0
sga_target big integer 0
SQL> show parameter sga;

NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1600M
sga_target big integer 0
SQL>

3. spfile with 3 GB
a. /dev/shm : 4.0 GB
b. memory

memory_max_target : 3G
memory_target : 3G
sga_max_size : 3G

[root@test ~]# umount /dev/shm
[root@test ~]# mount -t tmpfs shmfs -o size=4G /dev/shm
[root@test ~]# exit
[oracle@test ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda2 9.5G 5.5G 3.6G 61% /
/dev/hda6 40G 177M 38G 1% /u02
/dev/hda5 19G 6.7G 12G 38% /u01
/dev/hda1 99M 12M 83M 12% /boot
shmfs 4.0G 2.5G 1.6G 61% /dev/shm

SQL> startup;
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2148680 bytes
Variable Size 1728054968 bytes
Database Buffers 1459617792 bytes
Redo Buffers 17014784 bytes
Database mounted.
Database opened.

SQL> show parameter target;
NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 3G
memory_target big integer 3G
pga_aggregate_target big integer 0
sga_target big integer 0

SQL> show parameter sga;
NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3G
sga_target big integer 0

4. spfile with 4 GB
a. /dev/shm : 4.0 GB
b. memory

memory_max_target : 4G
memory_target : 4G
sga_max_size : 4G

SQL> alter system set memory_max_target=4G scope=spfile;
SQL> alter system set memory_target=4G scope=spfile;
SQL> alter system set sga_max_size=4G scope=spfile;
SQL> shutdown abort;

SQL> startup;
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2151248 bytes
Variable Size 2164264112 bytes
Database Buffers 2097152000 bytes
Redo Buffers 12214272 bytes
Database mounted.
Database opened.
SQL> show parameter target;
NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 4G
memory_target big integer 4G
pga_aggregate_target big integer 0
sga_target big integer 0
SQL> show parameter sga;

NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4G
sga_target big integer 0
SQL>

One Response to “Maximum SGA – Oracle 11g 64 bit – Centos 5.2 64 bit”

  1. Hendrik October 29, 2008 at 3:25 pm #

    Nice tutorial. I’ll try it.
    Thanx to visit my blog.🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: