Oracle Database Discussions

Hi, all,

I've been testing using AMM in our newly-upgraded 11gR2 databases. All databases (Prod and non-Prod) are running 11.2.0.3 on AIX 5.3 (64-bit).

I noticed that despite me explicitly setting the following to 0:

alter system set db_cache_size = 0 scope=spfile;
alter system set log_buffer = 0 scope=spfile;
alter system set pga_aggregate_target = 0 scope=spfile;
alter system set sga_max_size = 0 scope=spfile;
alter system set sga_target = 0 scope=spfile;
alter system set shared_pool_size = 0 scope=spfile;
alter system set shared_pool_reserved_size = 0 scope=spfile;

And allowing Oracle 'full control' how it manages all parts of the memory (SGA and PGA)

-- The 'big' database
alter system set memory_max_target = 24G scope=spfile;
alter system set memory_target = 24G scope=spfile;

-- The 'small' database
alter system set memory_max_target = 3G scope=spfile;
alter system set memory_target = 3G scope=spfile;

I bounce both databases and things seem to come up OK. The only exception is that sga_max_size seems to be set to a specific value:

For the 'big' database, it's 16Gb (2/3rds of the total memory_target)
For the 'small' database, it's 1536Mb (1/2 of the total memory_target)

I don't really understand why this is happening. I've double-checked my SPFILE, I've queried v$spparameter and I've read MOS 443746.1

+"If MEMORY_TARGET is set to a non-zero value:+

+If neither is set, they will be auto-tuned without any minimum or default values. We will have a policy of distributing the total memory set by MEMORY_TARGET parameter in a fixed ratio to the the SGA and PGA during initialization. The policy is to give 60% to the SGA and 40% to the PGA at startup."+

BUT I presume that sga_max_size works in the same way that it did when we weren't using AMM - it's the upper limit of the SGA. Not ideal, to be honest, as I can definitely see times when the SGA needs a LOT of buffer cache in Production (and, therefore, lots of SGA)

I do see this in the alert.logs whenever I restart the system:

Using parameter settings in server-side spfile /opt/oracle/product/11.2.0.3/db/dbs/spfilebigdb.ora
System parameters with non-default values:
processes = 600
sessions = 928
timed_statistics = TRUE
resource_limit = TRUE
event = "10262 trace name context forever, level 160000"
sga_max_size = 16G

So it's suggesting that it's querying the SPFILE and determining that sga_max_size should be set. But I've double-double-checked and that isn't the case.

Now, I know I can get around this by explicitly setting sga_max_size at memory_target. But I was wondering if anyone else had seen this before too?

Mark