Today I decided to increase memory but before that I wanted to change sga_max_size`s value.
Because in my production database configured sga_max_size to 12Gb.
It means independing on busy or idle of instance state, SGA could not be more than 12GB (it is worse).
The SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.
Our instance configured AMM:
select name, value from v$parameter where name like 'memory%'
memory_target 32212254720
memory_max_target 32212254720
--Checking init file
-bash-3.2$ more initPROD.ora| grep -i sga
PROD.__sga_target=12616466432
*.sga_max_size=25769803776
*.sga_target=0
-bash-3.2$
SGA_MAX_SIZE is not dynamic value, after changing those value, you should restart instance.
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
SQL> alter system set sga_max_size=0 scope=spfile;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1,2828E+10 bytes
Fixed Size 2272944 bytes
Variable Size 6710886736 bytes
Database Buffers 6106906624 bytes
Redo Buffers 7602176 bytes
Database mounted.
Database opened.
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
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.0.1/db_1/dbs/spfilePROD.ora
Uppps! Nothing is changed!
-bash-3.2$ cd /u01/app/oracle/product/11.0.1/db_1/dbs/
-bash-3.2$ more spfilePROD.ora | grep sga
PROD.__sga_target=12276727808
*.sga_max_size=0
*
-bash-3.2$
As you see spfile changed but Oracle set this parameter itself.
After that I investigated from the internet and found interesting post from Tom Kyte.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516
"You are using automatic memory management, we set things - the caches, everything, to whatever we feel like.
If you set memory_target, you need set nothing else. Just don't set it at all. Unset it."
If you want to set sga_max_size turn of memory parameters then set sga_max_size.
