1 Reply Latest reply on Dec 31, 2012 5:49 PM by marksmithusa

    SGA_MAX_SIZE and AMM

    marksmithusa
      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