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
19 comments:
Hi,
I like the explanation , thanks
Have a great day.
Regards,
Sheldon.
glad you liked it
Very clear explanation with actual scenario.
Thanks a lot!
The explanation is very distinct...
effort taken is highly appreciated
Very distinct explanation........ The effort taken is highly appreciated. Thanks ...
thanks for the comments, its nice to know that folks like a very exhaustive post like this one....
Hi, excelent post!
I just want to know: is this info valid for Oracle 11g?
Thanks again.
Jorge, I havn't tried that yet, might behave differently since we have the new out of the box memory management parameters (memory_target and memory_max_target) in 11g
Hi
I do have similiar question. My requirement is to start database(Ebusiness database) with minimum value, and it should grow uptill sga_max_Size set. RIght now value of SGA_Target and SGA_MAX_SIZE are same.
I wanted to change SGA_TARGET with minmumb value i.e 400MB and SGA_MAX_SIZE=1024MB. DOes it mean that My database starts with 400MB as min. parameter it can grow uptill 1024? Is my understanding is correct.
PLease let me know if my undestanding is correct
THanks for the help in advance.
thanks
Praveen
Praveen,
Yes your understanding is correct,you can start with 400 MB and grow your SGA until SGA_MAX_SIZE... hope this helps.
Hi,
That's really cool and good explanation.
Thanks & Regards,
mahi
Hi,
I have allcated 10 gb to user.oracle and default. But oracle is not using those memory. Getting out of memory during startup whenver i altered the SGA_MAX_SIZE.
SGA_MAX_SIZE is taking value upto 3.5 gb only. we are using oracle 9i.
I have some doubts.
1) Do we have to increase SGA_TARGET_SIZE also so that during startup oracle might use that. or SGA_TARGET_SIZE will automatically increases according to the necessity upto SGA_MAX_SIZE.
2)DO we have to allocate 10 gb for SYSTEM.root also .. as that of user.oracle and default.
3) is there anything i have missed which have to be altered.
Thanks
Is there that much memory available in the server ?
SGA_TARGET_SIZE cannot automatically grow upto SGA_MAX_SIZE, you will have to do it manually but cannot go beyong SGA_MAX_SIZE.
Size of the memory available for Oracle to use is also dependent on the word size of the OS (32-bit / 64-bit), some Operating Systems as such cannot see the entire amount of memory allocated to them so in that case they may not be able to provide the allocated amount of memory to Oracle.
I would suggest you refer to the 9i documentation of Oracle to know the upper bounds of memory specifications.
For the ORA-27102: out of memory, i did a quick OERR and found the below
$ oerr ora 27102
27102, 00000, "out of memory"
// *Cause: Out of memory
// *Action: Consult the trace file for details
Here again, i believe, there is not enough memory on the server to allocate to the SGA, hence the error "Out of memory"
Hi this is what i have done,
project: 100: user.oracle
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-shm-memory
privileged 10.0GB - deny -
system 16.0EB max deny -
SQL> Alter system set sga_max_size=5000M SCOPE=spfile;
System altered.
SQL> Alter system set db_cache_size=2684354560 SCOPE=spfile;
System altered.
SQL> Alter system set log_buffer=52428800 SCOPE=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27102: out of memory
SQL> exit
please reply what i am doing wrong..
you can see from the above post that i am having enough memory but wy oracle is not using that memory.
Please reply.. wy i am getting out of memory
Hi,
seems like the config is like this
solaris 64 bit
and oracle 32 bit..
is it posiiible to alter the SGA_MAX_SIZE to 5 gb.
(10 gb memory is available)
or because of this only am i getting out of memory during startup
thanks
I would suggest you look at any other trace files that may have gotten generated for such an error or even better get to Oracle support.
Post a Comment