This content has been marked as final. Show 5 replies
user13427480 wrote:What is "it"?
I am using AMM and i have define
tried setting sga_max_size =0
but when i start the database with spfile it get set up 60% of MT by itself
what is "MT"?
post SQL & results so we can see what you do & how Oracle responds.
How do I ask a question on the forums?
SQL and PL/SQL FAQ
and by "it" I was referring to SGA_MAX_SIZE (SMS)
I'm going to assume that you are referring to sga_max_size.
This will show up the same as your memory_target, or memory_max_target, whichever is greatest, even though you set sga_max_size to zero (or unset it).
If you want to confirm it, create pfile='<some file name>' from memory;
Then, you can look in that file and see that you do not have an sga_max_size setting.
Alternatively, you can set it to a value less than or equal to your memory_max_target to 'restrict' the size of your sga.
This is done sometimes to ensure enough memory is available for other pools (e.g., pga, shared_pool, etc.).
alter system set sga_max_size = 0 scope=spfile;
alter system set memory_max_target = 2G scope=spfile;
alter system set memory_target = 2G scope=spfile;
this is what i did and bounced the instance
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 2G
memory_target big integer 2G
pga_aggregate_target big integer 0
sga_target big integer 0
sga_max_size big integer 1228M
1. If my sga_max_size is 1228M, does it mean the max size of sga will be 1228M by AMM ?
2. How to make sga_max_size=0?
3. what if I make sga_max_size =2G will be the best utilization of memory ?
Hmmm... Do you have any other settings for anything like PGA_AGGREGATE_TARGET, or other pools / caches?1 person found this helpful
If you do, then this would be causing those pools to a minimum size and making the maximum available to the SGA_MAX_SIZE whatever is left over.
For example, shared_pool_reserved_size, etc...
Bottom line is that you can ignore the sga_max_size. It is just telling you this is all that can be allocated based on other settings already set.
As I said, you can dump your memory settings to a pfile and see what settings are hard set.
I think you will find that sga_max_size is not hard set, but rather just a dynamic value you see at the time you check it.
Also, instead of setting sga_max_size = 0, I would recommend to 'reset' it instead.
This is done by running:
SQL> alter system reset sga_max_size scope=spfile sid='*';