Adjusting the memory_max_target based on available memory.
This example is Linux x86-64.
If you can afford to set the memory_max_target higher then the memory_target this will give you room to grow the memory_target without restarting the database.
SQL> show parameters memory_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 17920M SQL> show parameters memory_max_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_max_target big integer 17920M
System has 36GB physical RAM available.
SQL> !grep MemTotal /proc/meminfo MemTotal: 36912956 kB
Server has now set aside 24GB for use with Oracle. Kernel shared memory parameter.
SQL>!df -h /dev/shm/ Filesystem Size Used Avail Use% Mounted on
tmpfs 24G 11G 14G 44% /dev/shm
Increase memory_max_target to 24GB.
SQL> ALTER SYSTEM SET memory_max_target = 24G SCOPE=SPFILE; System altered. Shutdown Oracle. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. Startup Oracle. SQL> startup ORACLE instance started. Total System Global Area 2.5655E+10 bytes Fixed Size 2213776 bytes Variable Size 2.0133E+10 bytes Database Buffers 5368709120 bytes Redo Buffers 151166976 bytes Database mounted. Database opened.
Update your pfile.
SQL> create pfile from spfile; File created.
Verify the new settings. Max now 24GB and memory target is 17.9GB. We can now increase the memory_target if the need arises without shutting the database down.
SQL> sho parameters memory_max_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_max_target big integer 24G SQL> sho parameters memory_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 17920M
Comments
Tags: MEMORY MAX TARGET, MEMORY_MAX_TARGET, MEMORY_TARGET, Oracle 11g - MEMORY_MAX_TARGET and MEMORY_TARGET, oracle app, Oracle Database, Oracle DBA, Oracle Queries