jump to navigation

Maximum SGA – Oracle 11g 64 bit – Centos 5.2 64 bit (2) Desember 1, 2008

Posted by setijoagus in Linux, oracle, oracle Performance.
Tags: , , ,
3 comments

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

my hardware :
11g01
11g021. info my linux box
model name : Intel(R) Xeon(R) CPU E5420 @ 2.50GHz
cpu MHz : 1992.000
processor : 4

2. fresh install : default SGA = 6464M
[oracle@test ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 19G 5.3G 13G 30% /
/dev/sda5 99G 5.8G 88G 7% /u01
/dev/sda1 99M 12M 83M 13% /boot
tmpfs 7.9G 3.8G 4.1G 48% /dev/shm

[oracle@test ~]$ sqlplus / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area 6747725824 bytes
Fixed Size 2145024 bytes
Variable Size 3623879936 bytes
Database Buffers 3087007744 bytes
Redo Buffers 34693120 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 6464M
memory_target big integer 6464M
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 6464M
sga_target big integer 0
SQL>

3. test SGA = 12G
SQL> alter system set memory_max_target=12G scope=spfile;
SQL> alter system set memory_target=12G scope=spfile;
SQL> alter system set sga_max_size=12G scope=spfile;
SQL> shutdown abort;
SQL> host

[oracle@test ~]$ su – root
[root@test ~]# umount shmfs
[root@test ~]# mount -t tmpfs shmfs -o size=12G /dev/shm
[root@test ~]# exit
[oracle@test ~]$ exit

SQL> startup;
ORACLE instance started.
Total System Global Area 1.2827E+10 bytes
Fixed Size 2148680 bytes
Variable Size 6039799480 bytes
Database Buffers 6777995264 bytes
Redo Buffers 7426048 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 12G
memory_target big integer 12G
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 12G
sga_target big integer 0

4. test SGA = 14G
SQL> alter system set memory_max_target=14G scope=spfile;
SQL> alter system set memory_target=14G scope=spfile;
SQL> alter system set sga_max_size=14G scope=spfile;
SQL> shutdown abort;
SQL> host

[oracle@test ~]$ su – root
[root@test ~]# umount shmfs
[root@test ~]# mount -t tmpfs shmfs -o size=14G /dev/shm
[root@test ~]# exit
[oracle@test ~]$ exit

SQL> startup;
ORACLE instance started.
Total System Global Area 1.4965E+10 bytes
Fixed Size 2149960 bytes
Variable Size 6845104568 bytes
Database Buffers 8053063680 bytes
Redo Buffers 64946176 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 14G
memory_target big integer 14G
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 14G
sga_target big integer 0
SQL>

Maximum SGA – Oracle 11g 64 bit – Centos 5.2 64 bit Oktober 28, 2008

Posted by setijoagus in oracle, oracle Performance.
Tags: ,
1 comment so far

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>

oracle 11g memory management Oktober 16, 2008

Posted by setijoagus in oracle, oracle Performance.
Tags: , , ,
add a comment

1. sebelum melakukan percobaan save lebih dulu spfile
[oracle@test ~]$ grep SwapTotal /proc/meminfo
SwapTotal: 4096564 kB
[oracle@test ~]$ grep MemTotal /proc/meminfo
MemTotal: 3367968 kB
[oracle@test ~]$ sqlplus / as sysdba
SQL> create pfile=’/home/oracle/14102008.ora’ from spfile;
SQL> startup nomount;
2. memory_max_target= SGA_TARGET + PGA_AGGREGATE_TARGET
a. SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET = 0, maka :
SGA = 60% MEMORY_TARGET
PGA = 40% MEMORY_TARGET

SQL> alter system set memory_target=4G scope=spfile;
SQL> alter system set PGA_AGGREGATE_TARGET=0 scope=spfile;
SQL> alter system set SGA_TARGET=0 scope=spfile;
SQL> alter system set SGA_MAX_SIZE=0 scope=spfile;
SQL> shutdown abort;
SQL> startup;
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 2460M
sga_target big integer 0

SQL> column COMPONENT format a26;
SQL> select COMPONENT, CURRENT_SIZE, MIN_SIZE, MAX_SIZE from V$MEMORY_DYNAMIC_COMPONENTS
where COMPONENT like ‘SGA Target’ or COMPONENT like ‘PGA Target’;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
————————– ———— ———- ———-
SGA Target 2575302656 2575302656 2575302656
PGA Target 343932928 343932928 343932928

b. SGA_TARGET <> 0 dan PGA_AGGREGATE_TARGET = 0
PGA_AGGREGATE_TARGET = MEMORY_TARGET-SGA_TARGET
SQL> alter system set memory_max_target=3G scope=spfile;
SQL> alter system set memory_target=0 scope=spfile;
SQL> alter system set PGA_AGGREGATE_TARGET=0 scope=spfile;
SQL> alter system set SGA_TARGET=2458M scope=spfile;
SQL> shutdown abort;
SQL> startup;
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 0
pga_aggregate_target big integer 0
sga_target big integer 2464M
SQL> show parameter sga;
NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2464M
sga_target big integer 2464M

C. SGA_TARGET = 0 dan PGA_AGGREGATE_TARGET <> 0
SGA_TARGET = MEMORY_TARGET-PGA_AGGREGATE_TARGET

SQL> alter system set memory_max_target=3G scope=spfile;
SQL> alter system set memory_target=0 scope=spfile;
SQL> alter system set PGA_AGGREGATE_TARGET=1G scope=spfile;
SQL> alter system set SGA_TARGET=0 scope=spfile;
SQL> shutdown abort;
SQL> startup;

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 0
pga_aggregate_target big integer 1G
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 1968M
sga_target big integer 0

D. SGA_TARGET <> 0 dan PGA_AGGREGATE_TARGET <> 0
SQL> alter system set memory_max_target=4G scope=spfile;
SQL> alter system set memory_target=0 scope=spfile;
SQL> alter system set PGA_AGGREGATE_TARGET=1G scope=spfile;
SQL> alter system set SGA_TARGET=2457600000 scope=spfile;

SQL> shutdown abort;
SQL> startup;
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 0
pga_aggregate_target big integer 1G
sga_target big integer 2352M
SQL> show parameter sga;
NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2352M
sga_target big integer 2352M

ORACLE 11g : MEMORY_TARGET not supported on this system (ORA-00845) September 26, 2008

Posted by setijoagus in oracle, oracle Performance.
Tags: , ,
add a comment

ORACLE 11g : MEMORY_TARGET not supported on this system (ORA-00845)

pada saat mengatur memory di oracle 11g linux khususnya parameter
memory_target misalkan diinginkan memory_target = 2,5 G, saya menemukan
error :
SQL> ORA-00845: MEMORY_TARGET not supported on this system
ternyata hal itu berhubungan dengan /dev/shm, artinya /dev/shm tidak
dikonfigurasi dengan nilai yang cukup untuk mengalokasikan parameter
memory_target.

[root@node1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
……… —- —- —– —- ———-
tmpfs 1.8G 1.0G 747M 74% /dev/shm
……… —- —- —– —- ———-

maka langkah yang harus dilakukan adalah :

1. periksa apakah /dev/shm masih ada isisnya ?, jika ya sebaiknya instance database sudah di shutdown
[root@node1 ~]# ls /dev/shm
……… —————————
ora_node1_491529_0 ora_node1_491529_1 ora_node1_491529_10
……… —————————

2. umount tmpfs dan buat yang baru
[root@node1 ~]# umount tmpfs
[root@node1 ~]# mount -t tmpfs shmfs -o size=2663m /dev/shm
[root@node1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
……… —- —- —– —- ———-
shmfs 2.7G 2.0G 622M 77% /dev/shm
……… —- —- —– —- ———-

3. supaya setiap kali reboot dijalankan, biasanya dipasang di /etc/rc.d/rc.local
[root@node1 ~]# vi /etc/rc.d/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don’t
# want to do the full Sys V style init stuff.
touch /var/lock/subsys/local
umount tmpfs
mount -t tmpfs shmfs -o size=2663m /dev/shm
4. supaya setiap kali reboot dijalankan, juga biasanya dipasang di/etc/fstab
[root@node1 ~]# vi /etc/fstab
tmpfs /dev/shm tmpfs size=2663M 0 0

5. misalkan diinginkan memory_target = 2,5 G
SQL> show parameter memory
NAME TYPE VALUE
————————————————-
hi_shared_memory_address integer 0
memory_max_target big integer 2512M
memory_target big integer 2512M
shared_memory_address integer 0