Maximum SGA – Oracle 11g 64 bit – Centos 5.2 64 bit (2)

1 Dec

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>

3 Responses to “Maximum SGA – Oracle 11g 64 bit – Centos 5.2 64 bit (2)”

  1. 迷你倉 December 24, 2008 at 3:37 am #

    Merry X’Mas & Happy 2009.
    Tony.
    荔枝角卓越迷你倉
    香港仔時昌迷你倉

  2. Ivan February 6, 2009 at 6:42 am #

    Pak setijo,

    Bagaimana dengan DB 9.2.0.3?
    Jika menggunakan BIG SGA, apakah parameter berikut masih bisa digunakan
    1. db_cache_size
    2. db_cache_advice

    kemudian apakah ada setup memmory pada sisi OS nya (RH.3.5)

    Terimkasih,
    Ivan

    • setijoagus February 7, 2009 at 2:00 am #

      untuk DB 9.2.0.3? sejujurnya aku nggak pernah pakai, ttp menurut teori pengalamatan
      machine 32-bit : 0 s/d 4’294’967’295 (4 GiB – 1).
      machine 64-bit : 0 s/d 18’446’744’073’709’551’615 (16 EiB – 1).
      artinya 32 bit mmg terbatas pengalamatan memorinya
      untuk pastinya coba periksa di google
      sb yang aku coba memang benar 2x machine 64 bit dan oraclenya juga 64 bit

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: