11 Replies Latest reply: Oct 5, 2012 11:51 PM by javeedkaleem dba RSS

    memory_max_target values in 11g

    user12194321
      Hi,
       $ cat /proc/meminfo
      MemTotal:     15401920 kB
      MemFree:        234184 kB
      Buffers:        325624 kB
      Cached:        2847380 kB
      SwapCached:     176576 kB
      Active:        3755800 kB
      Inactive:      1052096 kB
      HighTotal:           0 kB
      HighFree:            0 kB
      LowTotal:     15401920 kB
      LowFree:        234184 kB
      SwapTotal:    31744432 kB
      SwapFree:     30316284 kB
      Dirty:             348 kB
      Writeback:           0 kB
      AnonPages:     1458284 kB
      Mapped:        1725176 kB
      Slab:           152608 kB
      PageTables:     139916 kB
      NFS_Unstable:        0 kB
      Bounce:              0 kB
      CommitLimit:  39445392 kB
      Committed_AS:  5907284 kB
      VmallocTotal: 34359738367 kB
      VmallocUsed:    278612 kB
      VmallocChunk: 34359459431 kB
      HugePages_Total:     0
      HugePages_Free:      0
      HugePages_Rsvd:      0
      Hugepagesize:     2048 kB
      SQL> show parameter memory

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      hi_shared_memory_address integer 0
      memory_max_target big integer 5G
      memory_target big integer 5G


      the server has only one database residing and server memory has 14+ gigs
      and we allocated memory_target=5g is this menas oracle can use max 5gigs memory in this server and can not more than 5 gigs ?

      Thanks



      Thanks
        • 1. Re: memory_max_target values in 11g
          Marco V.
          Have a look at these guides:
          http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams140.htm#REFRN10284

          http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#ADMIN00207
          • 2. Re: memory_max_target values in 11g
            Marco V.
            MEMORY_TARGET
            Property      Description
            Parameter type      Big integer
            Syntax      MEMORY_TARGET = integer [K | M | G]
            Default value      0 (SGA autotuning is disabled for DEFERRED mode autotuning requests, but allowed for IMMEDIATE mode autotuning requests)
            Modifiable      ALTER SYSTEM
            Range of values      152 MB to MEMORY_MAX_TARGET
            Basic      No
            
            MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET
             value, reducing or enlarging the SGA and PGA as needed.
            
            In a text-based initialization parameter file, if you omit MEMORY_MAX_TARGET and include a value for
             MEMORY_TARGET, then the database automatically sets MEMORY_MAX_TARGET to the value of 
            MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET,
             the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change 
            MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
            
            Total memory usage can grow beyond the value of MEMORY_TARGET. For example, memory is allocated
             to PL/SQL tables and varrays regardless of the value of MEMORY_TARGET as long as memory is available 
            at the operating system level.
            
            In the Default value field, IMMEDIATE mode autotuning requests are necessary to avoid ORA-04031 errors. 
            The DEFERRED and IMMEDIATE modes are reflected in the OPER_MODE column of the V$MEMORY_RESIZE_OPS view.
            
            Note:
            The default value of SGA_MAX_SIZE depends on the values of MEMORY_TARGET and MEMORY_MAX_TARGET.
            • 3. Re: memory_max_target values in 11g
              user12194321
              Thanks ofr this information

              since we have allocated 5gigs for oracle in this server , the below is the output of top command
              top - 10:40:32 up 1 day, 22:32,  4 users,  load average: 0.17, 0.34, 0.20
              Tasks: 232 total,   1 running, 230 sleeping,   0 stopped,   1 zombie
              Cpu(s):  0.1%us,  0.1%sy,  0.0%ni, 99.2%id,  0.6%wa,  0.0%hi,  0.0%si,  0.0%st
              Mem:  15401920k total, 14757960k used,   643960k free,   332212k buffers
              Swap: 31744432k total,  1427232k used, 30317200k free,  2876436k cached
              why only 643960k free and who are all using 14757960k used ?

              appreciated for the inputs

              Thanks
              • 4. Re: memory_max_target values in 11g
                phaeus
                Hello,
                there is no fixed memory limit for a oracle database. A user Session can overallocated the PGA for example. Also your free memory calculation in not correct because you must also keep in mind the cache.

                See this articel for more information.

                http://www.linuxhowtos.org/System/Linux%20Memory%20Management.htm

                regards
                Peter
                • 5. Re: memory_max_target values in 11g
                  user12194321
                  Thanks for the link

                  really i want to know who are all using 14757960k memory in this server , is it oracle OR some one ?
                   $ free -m
                               total       used       free     shared    buffers     cached
                  Mem:         15040      14433        607          0        332       2816
                  -/+ buffers/cache:      11284       3756
                  Swap:        31000       1392      29608
                  Thanks

                  Edited by: user12194321 on Oct 3, 2012 4:07 AM

                  Edited by: user12194321 on Oct 3, 2012 4:08 AM
                  • 6. Re: memory_max_target values in 11g
                    JohnWatson
                    You are not using 14G, you are using 11G. It is the third line of the free outout that matters.
                    • 7. Re: memory_max_target values in 11g
                      user12194321
                      Hi,

                      Thanks for the points

                      i want to know who are all using 11Gigs of memory ?

                      Thanks
                      • 8. Re: memory_max_target values in 11g
                        javeedkaleem dba
                        use this command to check top memory consumers process:
                        #####################################
                        ps gv | head -n 1; ps gv | egrep -v "RSS" | sort +6b -7 -n -r |tail -10
                        ps gv | head -n 1; ps gv | egrep -v "RSS" | sort +6b -7 -n -r |head -10

                        also try this
                        =======
                        ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10 ---> For linux, Solaris
                        ps -eo pcpu,pid,user,args | sort -y 1 -r | head -10      ---> For Aix
                        ps aux | head -1; ps aux | sort -rn +3 | head          --> FOR AIX
                        ps -eo pcpu,pid,user,args | sort -y 1 -r | head -10      --> top cpu in AIX

                        AIX based.
                        ========
                        ps -ef|sort +6|tail                --> Check last process which is using max cpu.
                        ps -ef|sort +7|tail
                        ps auxgw|sort +2|tail
                        ps auxgw|egrep "RSS| "|head          --> Check last process which is using max cpu. GOOD ONE

                        Edited by: javeedkaleem dba on Oct 3, 2012 4:52 AM
                        • 9. Re: memory_max_target values in 11g
                          user12194321
                          Hi,

                          We are using linux

                          $ cat /etc/redhat-release
                          Red Hat Enterprise Linux Server release 5.8 (Tikanga)

                          could anyone shed some light on the below output

                          $ ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10
                          %CPU   PID USER     COMMAND
                           8.2  1445 oracle   ora_j002_traxjjp
                           3.5  2275 oracle   ora_j001_traxjjp
                           3.2  1571 oracle   oracletraxjjp (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
                           3.0  2271 oracle   ora_j000_traxjjp
                           2.3  2281 oracle   ora_j003_traxjjp
                           0.5  1486 oracle   oracletraxjjp (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
                           0.3  2162 oracle   oracletraxjjp (LOCAL=NO)
                           0.3  1410 oracle   rman target sys/nolog @/backup/rman_script/weekly_backup.rman log /mnt/melexhbkp02/Weekly/full_traxjjp_2012-10-03.log
                           0.1  7121 oracle   oracletraxjjp (LOCAL=NO)
                          [oracle@meldc1trxj01 ~]$ ps gv | head -n 1; ps gv | egrep -v "RSS" | sort +6b -7 -n -r |tail -10
                            PID TTY      STAT   TIME  MAJFL   TRS   DRS   RSS %MEM COMMAND
                           2490 pts/4    S+     0:00      0    48 109127  672  0.0 sort +6b -7 -n -r
                           2488 pts/4    R+     0:00      0    74 63425   764  0.0 ps gv
                           7643 pts/1    Ss+    0:00      1   711 65388  1628  0.0 -bash
                          29629 pts/4    S      0:00      0   711 65508  1664  0.0 /bin/bash
                           2491 pts/4    D+     0:00      0   711 65508   684  0.0 /bin/bash
                          18655 pts/2    Ss     0:00      2   711 65512  1672  0.0 -bash
                          28931 pts/4    Ss     0:00      0   711 65520  1632  0.0 -bash
                          17871 pts/3    Ss+    0:00      0   711 65520  1636  0.0 -bash
                          29380 pts/4    S      0:00      0     2 70753 12900  0.0 sqlplus
                          12105 pts/2    S+     0:00      0     2 71177 13044  0.0 sqlplus                     as sysdba
                          [oracle@meldc1trxj01 ~]$ ps gv | head -n 1; ps gv | egrep -v "RSS" | sort +6b -7 -n -r |head -10
                            PID TTY      STAT   TIME  MAJFL   TRS   DRS   RSS %MEM COMMAND
                           2509 pts/4    S+     0:00      0    48 109127  676  0.0 sort +6b -7 -n -r
                           2510 pts/4    S+     0:00      0    23 58908   552  0.0 head -10
                           2507 pts/4    R+     0:00      0    74 63425   772  0.0 ps gv
                           7643 pts/1    Ss+    0:00      1   711 65388  1628  0.0 -bash
                          29629 pts/4    S      0:00      0   711 65508  1664  0.0 /bin/bash
                          18655 pts/2    Ss     0:00      2   711 65512  1672  0.0 -bash
                          28931 pts/4    Ss     0:00      0   711 65520  1632  0.0 -bash
                          17871 pts/3    Ss+    0:00      0   711 65520  1636  0.0 -bash
                          29380 pts/4    S      0:00      0     2 70753 12900  0.0 sqlplus
                          12105 pts/2    S+     0:00      0     2 71177 13044  0.0 sqlplus                     as sysdba
                          • 10. Re: memory_max_target values in 11g
                            user12194321
                            Hi,

                            As system admin saying oracle consuming memory and we have allocated only 5gigs to oracle (memory_target) so need to know who are consuming the memory

                            Appreciated the inputs

                            Thanks
                            • 11. Re: memory_max_target values in 11g
                              javeedkaleem dba
                              The top most processes is only our interest, if you found the top processes are of oracle then query the v$session view with spid using pid from the previous command.

                              ex:


                              $ ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10
                              %CPU PID USER COMMAND
                              *8.2 *1445* oracle ora_j002_traxjjp* .................................................> This is the 1st toppest process with PID=1445
                              *3.5 *2275* oracle ora_j001_traxjjp* .................................................> This is the 2nd toppest process with PID=2275
                              3.2 1571 oracle oracletraxjjp (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
                              3.0 2271 oracle ora_j000_traxjjp
                              2.3 2281 oracle ora_j003_traxjjp
                              0.5 1486 oracle oracletraxjjp (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
                              0.3 2162 oracle oracletraxjjp (LOCAL=NO)
                              0.3 1410 oracle rman target sys/nolog @/backup/rman_script/weekly_backup.rman log /mnt/melexhbkp02/Weekly/full_traxjjp_2012-10-03.log --> this is RMAN backup job running_
                              0.1 7121 oracle oracletraxjjp (LOCAL=NO)


                              PID 1445 and 2275 are the oracle jobs processes.
                              using this PID Query v$session & v$process

                              -- Author : Syed Kaleemuddin.
                              -- Script Name : pid2sid.sql
                              -- Description : get sid using server pid.
                              -- Prompt "Detail's using OS pId "
                              select a.sid, a.SERIAL# , to_char(a.logon_time,'mm/dd hh24:mi:ss') logontime,
                              a.status,a.type,a.username,a.osuser,a.action,a.machine
                              from v$session a, v$process b
                              where spid = &spid
                              and b.addr=a.paddr;


                              example:
                              -------------
                              $ ps -ef |grep 504048
                              oracle 504048 1 0 06:48:56 - 0:01 oracleyscc02 (LOCAL=NO)


                              SQL> set lines 200
                              SQL> @pid2sid
                              Enter value for spid: 504048
                              old 4: where spid = &spid
                              new 4: where spid = 504048

                              SID SERIAL# LOGONTIME STATUS TYPE USERNAME OSUSER ACTION
                              ---------- ---------- -------------- -------- ---------- ------------------------------ ------------------------------ --------------
                              MACHINE
                              ----------------------------------------------------------------
                              124 839 10/06 06:48:56 INACTIVE USER A4595 SYSTEM
                              YAMAMACEMENT\YSCCPLANT1


                              SQL>

                              here is the details
                              SID = 124
                              Serail# = 839
                              Logon time =
                              Status
                              username
                              osuser
                              Action
                              from which machine connection is comming.

                              query this details and do as per your business demands.

                              hope its helpful to you

                              Regards
                              Kaleem.