This discussion is archived
11 Replies Latest reply: Oct 5, 2012 9:51 PM by javeedkaleem dba RSS

memory_max_target values in 11g

user12194321 Newbie
Currently Being Moderated
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. Expert
    Currently Being Moderated
    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. Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points