7 Replies Latest reply: May 15, 2012 3:50 AM by Karan RSS

    How much average physical memory was used by Oracle database ?

    johnpau2013
      - oracle 11g R2 - standard edition
      - Automatic Memory Management (AMM) is enabled
      - physical server has 32GB
      - SGA_MAX_SIZE = 16GB

      So by default when we start the server, Oracle db would take the 16GB.

      1) How to find what was the average memory used by Oracle db from time X to time Y ?

      2) I am guessing if 16GB was allocated to oracle db in sga_max_size , even though 16GB is grabbed by Oracle instance. I am guessing it does not used 100% of it internally.
      How to find what was the average actual memory used by Oracle db ?
        • 1. Re: How much average physical memory was used by Oracle database ?
          sb92075
          johnpau2013 wrote:
          - oracle 11g R2 - standard edition
          - Automatic Memory Management (AMM) is enabled
          - physical server has 32GB
          - SGA_MAX_SIZE = 16GB

          So by default when we start the server, Oracle db would take the 16GB.

          1) How to find what was the average memory used by Oracle db from time X to time Y ?
          Might answer be OS dependent?

          >
          2) I am guessing if 16GB was allocated to oracle db in sga_max_size , even though 16GB is grabbed by Oracle instance. I am guessing it does not used 100% of it internally.
          How to find what was the average actual memory used by Oracle db ?
          query DBA_HIST_SYSSTAT
          • 2. Re: How much average physical memory was used by Oracle database ?
            415289
            So by default when we start the server, Oracle db would take the 16GB.
            it should consume SGA + PGA as well.
            1) How to find what was the average memory used by Oracle db from time X to time Y ?
            if SGA_MAX_SIZE = 16GB than it will consume 16 GB from your RAM and it may not reduce.

            2) I am guessing if 16GB was allocated to oracle db in sga_max_size , even though 16GB is grabbed by Oracle instance. I am guessing it does not used 100% of it >internally.How to find what was the average actual memory used by Oracle db ?
            inside oracle SGA if you want to see how much is allocated between each component ,try v$sga_resize_ops

            select
                    component,
                    oper_type,
                    initial_size,
                    target_size,
                    final_size,
                    status,
                    to_char(start_time,'dd-mon hh24:mi:ss') start_time,
                    to_char(end_time,'dd-mon hh24:mi:ss')   end_time
            from
                    v$sga_resize_ops
            order by
                    start_time
            ;  
            
             
            • 3. Re: How much average physical memory was used by Oracle database ?
              mBk77
              Total memory used by Oracle=SGA+PGA+BP

              Since you are using AMM

              Oracle will use the value set for MEMORY_TARGET.

              MEMORY_TARGET specifies the Oracle system-wide usable memory.

              http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams133.htm
              • 4. Re: How much average physical memory was used by Oracle database ?
                johnpau2013
                Is there any table which shows HWM (high water mark) for SGA what Oracle reached
                for the current instance ?

                Even though I had allocated the memory_max =16GB , I am sure Oracle would not
                have used 100%. I wanted to know its HWM
                • 5. Re: How much average physical memory was used by Oracle database ?
                  sb92075
                  johnpau2013 wrote:
                  Is there any table which shows HWM (high water mark) for SGA what Oracle reached
                  for the current instance ?

                  Even though I had allocated the memory_max =16GB , I am sure Oracle would not
                  have used 100%. I wanted to know its HWM
                  http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_3196.htm#REFRN23397
                  but not SGA
                  :-(
                  • 6. Re: How much average physical memory was used by Oracle database ?
                    Billy~Verreynne
                    johnpau2013 wrote:

                    Even though I had allocated the memory_max =16GB , I am sure Oracle would not
                    have used 100%. I wanted to know its HWM
                    The SGA is allocated as shared memory at instance startup - and the size of the request to the kernel for creating that shmem (kernel call shmget() on Linux) is the size specified in the Oracle initialisation/configuration file.

                    Or are you saying that you set the SGA size to 16Gb and less than 16Gb is allocated as shmem?
                    • 7. Re: How much average physical memory was used by Oracle database ?
                      Karan
                      For PGA you can easily see it through

                      SQL> select name,value from v$pgastat where name in('total PGA used for manual workareas','maximum PGA used for auto workareas');

                      NAME VALUE
                      ---------------------------------------------------------------- ----------
                      maximum PGA used for auto workareas 1241088
                      total PGA used for manual workareas 0

                      Regards
                      Karan