How to configure the memory target in Oracle Database
Posted by Mir Sayeed Hassan on October 4th, 2021
How to configure the memory target in Oracle Database
Consider you are running the database with sga & pga configuration., if you need to change it to memroy target, fallow the below process.
Check the status of database
SQL> select instance_name, version, open_mode from V$database, v$instance; INSTANCE_NAME VERSION OPEN_MODE ---------------------------------------------- oemsw 19.0.0.0.0 READ WRITE
Before you proceed with this setup., verify the current configuration
[root@cloudctl ~]# free -m
total used free shared buff/cache available
Mem: 48075 12786 301 14534 34988 20197
Swap: 17399 0 17399
SGA Configuration
SQL> show parameter sga_ NAME TYPE VALUE ------------------------------------ ------------------ sga_max_size big integer 14432M sga_min_size big integer 0 sga_target big integer 14432M unified_audit_sga_queue_size integer 1048576
PGA Configuration
SQL> show parameter pga NAME TYPE VALUE -------------------------------------------------- pga_aggregate_limit big integer 9614M pga_aggregate_target big integer 4807M
SQL> show parameter memory_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 0
Note:
Brief about the above confiuguration., In this server the sga & pga is configured & the memory of the server is 48G.,
Out of 48G split as below
– 15G is allocated for sga_target
– 15G is allocated for sga_max_target
– 5G is allocated for pga_aggregate_target
– Rest is OS Level
Setup the configuration of the MEMORY_TARGET, i.e: MEMORY_TARGET= SGA_TARGET + PGA_AGGREGATE_TARGET
Fallow the below setup
SQL> alter system set sga_target=0; System altered.
SQL> alter system set sga_max_size=0 scope=spfile; System altered.
SQL> alter system set pga_aggregate_target=0 scope=spfile; System altered.
SQL> alter system set memory_target=35g scope=spfile; System altered.
SQL> alter system set memory_max_target=40g scope=spfile; System altered.
Note: DB Need to bounce as there parameter updated are static
Shutdown the database
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Start up the database
SQL> startup ORACLE instance started. Total System Global Area 2.2549E+10 bytes Fixed Size 26595208 bytes Variable Size 3892314112 bytes Database Buffers 1.8522E+10 bytes Redo Buffers 107622400 bytes Database mounted. Database opened.
Verify
SQL> show parameter sga_target NAME TYPE VALUE --------------------------------------- sga_target big integer 0
SQL> show parameter pga_agg NAME TYPE VALUE --------------------------------------------- pga_aggregate_limit big integer 40G pga_aggregate_target big integer 0
SQL> show parameter memory_target NAME TYPE VALUE ----------------------------------------- memory_target big integer 35G
SQL> show parameter memory_max NAME TYPE VALUE --------------------------------------------------- inmemory_max_populate_servers integer 0 memory_max_target big integer 40G
SQL> select * from V$sgainfo; NAME BYTES RES CON_ID -------------------------------- ---------- --- ---------- Fixed SGA Size 26595208 No 0 Redo Buffers 107622400 No 0 Buffer Cache Size 1.8522E+10 Yes 0 In-Memory Area Size 0 No 0 Shared Pool Size 2952790016 Yes 0 Large Pool Size 805306368 Yes 0 Java Pool Size 0 Yes 0 Streams Pool Size 134217728 Yes 0 Shared IO Pool Size 134217728 Yes 0 Data Transfer Cache Size 0 Yes 0 Granule Size 134217728 No 0 Maximum SGA Size 2.2549E+10 No 0 Startup overhead in Shared Pool 1086770472 No 0 Free SGA Memory Available 0 0
Note: Make sure /dev/shm should be more than 3G otherwise you might get the error while startup the database
[root@cloudctl ~]# df -h /dev/shm Filesystem Size Used Avail Use% Mounted on tmpfs 24G 21G 2.5G 90% /dev/shm
In general if you want to check the current usage of the memory used by oracle, Issue the below query:
SQL> select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mb from ( select 'sga' nm, sum(value) val from v$sga union all select 'pga', sum(a.value) from v$sesstat a, v$statname b where b.name = 'session pga memory' and a.statistic# = b.statistic# ) group by rollup(nm); NM MB ----- ---------- pga 647 sga 21504 total 22151