oracle 11g memory management

16 Oct

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

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: