To begin with, sga_max_size is set in the spfile to a value of 200M and then I try to set the sga_target and the story unfolds as below.
SQL> sho parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/app/oracle/product/10.2.0
/dbs/spfileprod_db.ora
SQL> sho parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 200M
sga_target big integer 0
SQL> sho sga
Total System Global Area 209715200 bytes
Fixed Size 1977560 bytes
Variable Size 155194152 bytes
Database Buffers 50331648 bytes
Redo Buffers 2211840 bytes
SQL> sho parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 84M
SQL> sho parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 48M
SQL> sho parameter large
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0
SQL> sho parameter java_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 24M
SQL> sho parameter streams_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
Now, trying to set the sga_target to a value of 210M which is greater than that of sga_max_size gives out an error of ORA-02097 and ORA-00823, this shows that the sga_target can never be greater than the sga_max_size.
SQL> alter system set sga_target=210M scope=both; alter system set sga_target=210M scope=both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00823: Specified value of sga_target greater than sga_max_size
However, sga_target can be set less than or equal to sga_max_size.
SQL> alter system set sga_target=200M scope=both; System altered. SQL> sho parameter sga_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_max_size big integer 200M sga_target big integer 200M
But an interesting point to be noted in here is that as soon as the sga_target is set to a non-zero value, it starts affecting the memory areas of the SGA which is evident from the change in the value of Database Buffers (db_cache_size) from 50331648 (48M) from the previous execution of SHO SGA to 92274688 (88M), while every other memory area remains unchanged.
SQL> sho sga Total System Global Area 209715200 bytes Fixed Size 1977560 bytes Variable Size 113251112 bytes Database Buffers 92274688 bytes Redo Buffers 2211840 bytes SQL> sho parameter db_Cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_size big integer 88M SQL> sho parameter shared_pool_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ shared_pool_size big integer 84M SQL> sho parameter java_pool NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_pool_size big integer 24M SQL> sho parameter streams_pool NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ streams_pool_size big integer 0 SQL> sho parameter large_pool NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ large_pool_size big integer 0
Let's try to unset the sga_target and see if Database Buffers (db_cache_size) reverts back to what it was, but it does not.
SQL> alter system set sga_target=0 scope=both; System altered. SQL> sho sga Total System Global Area 209715200 bytes Fixed Size 1977560 bytes Variable Size 113251112 bytes Database Buffers 92274688 bytes Redo Buffers 2211840 bytes SQL> sho parameter sga_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_max_size big integer 200M sga_target big integer 0 SQL> sho parameter db_Cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_size big integer 88M
It was hoped that bouncing the database would make a difference, on the contrary it did not. It is another discussion as to why the Database Buffers (db_Cache_size) does not change back to what it was even after unsetting the sga_target and bouncing the database, let's not get into the nitty-gritty of that.
SQL> sho sga Total System Global Area 209715200 bytes Fixed Size 1977560 bytes Variable Size 113251112 bytes Database Buffers 92274688 bytes Redo Buffers 2211840 bytes SQL> sho parameter db_Cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_size big integer 88M SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 209715200 bytes Fixed Size 1977560 bytes Variable Size 113251112 bytes Database Buffers 92274688 bytes Redo Buffers 2211840 bytes Database mounted. Database opened. SQL> sho parameter sga_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_max_size big integer 200M sga_target big integer 0 SQL> sho parameter db_Cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_size big integer 88M
The Oracle server does not let you to resize the sga_max_size on the fly since it is not a dynamically changeable parameter. Let's now change sga_target to the value of sga_max_size. After this I will comment the sga_max_size and add sga_target=200M in the pfile, convert it into spfile and bounce the database.
SQL> sho parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 200M
sga_target big integer 0
SQL> alter system set sga_max_size=0 scope=both;
alter system set sga_max_size=0 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set sga_target=200M scope=both;
System altered.
SQL> sho parameter sgA_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 200M
sga_target big integer 200M
### MAIN SGA PARAMS ###
###sga_max_size=200M
sga_target=200M
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1977528 bytes
Variable Size 75502408 bytes
Database Buffers 130023424 bytes
Redo Buffers 2211840 bytes
Database mounted.
Database opened.
SQL> sho parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 200M
sga_target big integer 200M
SQL> sho parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0
SQL> sho parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL> sho parameter large_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0
SQL> sho parameter java_pool_s
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 0
SQL> sho parameter streams_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL>
It now shows that as soon as the sga_target is set and set_max_size is unset, all the other memory area related parameters get reset to a value of zero(0) - making it clear that the Automatic Shared Memory Management is fully functional and that Memory Manager (MMAN) background process is actively doing its job. Also has the Database Buffers (db_cache_size) taken a higher value of 130023424 (124M) against the previous 88M.
There is yet another point to be noted here, besides sga_max_size being commented in spfile - it takes a value equal to the sga_target.
SQL> sho parameter sga_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_max_size big integer 200M sga_target big integer 200M UNIX$> grep -i "sga" initprod_db.ora ###sga_max_size=200M sga_target=200M UNIX$> grep -i "sga" $ORACLE_HOME/dbs/spfileprod_db.ora *.sga_target=200M
The conclusions that we can draw from these little experiments are listed below.
1) sga_max_size cannot be changed without bouncing the database.
2) sga_target can be changed on the fly without bouncing the database but it can never be set to a value greater than the sga_max_size.
3) If the sga_max_size is not set in either of the pfile or spfile and the sga_target is set then the sga_max_size takes the value of sga_target when you do a sho parameter sga_
Posts
