Re: Oracle connections and memory usage

From: Vincent D'Antonio (dantoniov_at_GMAIL.COM)
Date: 11/10/05

  • Next message: Robert Miller: "Restricting FTP data connection port range?" 
    Date:         Thu, 10 Nov 2005 11:22:27 -0500
To: aix-l@Princeton.EDU

    Mark,
    We are using JFS2. and right now there is no load and no connections.
    Here is the vmstat 5 5 output:

     System configuration: lcpu=16 mem=31744MB

    kthr memory page faults cpu
    ----- ----------- ------------------------ ------------ -----------
    r b avm fre re pi po fr sr cy in sy cs us sy id wa
    0 0 4646776 2721286 0 0 0 0 0 0 48 9853 388 1 0 93 6
    1 0 4645713 2722347 0 0 0 0 0 0 158 99367 2190 7 4 83 6
    0 0 4645713 2722369 0 0 0 0 0 0 47 499 351 0 0 93 6
    0 0 4645716 2722365 0 0 0 0 0 0 45 2809 529 1 0 93 6
    0 0 4645716 2722363 0 0 0 0 0 0 49 347 351 0 0 93 6
    and topas:
     Topas Monitor for host: sdp1 EVENTS/QUEUES FILE/TTY
    Thu Nov 10 11:21:49 2005 Interval: 2 Cswitch 333 Readch 5431
    Syscall 743 Writech 19161
    Kernel 0.2 |# | Reads 12 Rawin 0
    User 0.1 |# | Writes 6 Ttyout 269
    Wait 6.0 |## | Forks 0 Igets 0
    Idle 93.7 |########################### | Execs 0 Namei 22
    Runqueue 0.0 Dirblk 0
    Network KBPS I-Pack O-Pack KB-In KB-Out Waitqueue 0.0
    en7 2.5 24.5 2.5 1.4 1.1
    en6 0.3 1.5 1.0 0.2 0.1 PAGING MEMORY
    lo0 0.1 1.0 1.0 0.0 0.0 Faults 257 Real,MB 31743
    en8 0.0 0.0 0.0 0.0 0.0 Steals 0 % Comp 57.5
    PgspIn 0 % Noncomp 9.8
    Disk Busy% KBPS TPS KB-Read KB-Writ PgspOut 0 % Client 10.0
    hdisk4 0.0 0.0 0.0 0.0 0.0 PageIn 2
    hdisk7 0.0 0.0 0.0 0.0 0.0 PageOut 4 PAGING SPACE
    hdisk28 0.0 0.0 0.0 0.0 0.0 Sios 6 Size,MB 32768
    hdisk30 0.0 0.0 0.0 0.0 0.0 % Used 1.0
    hdisk29 0.0 0.0 0.0 0.0 0.0 NFS (calls/sec) % Free 98.9
    hdisk6 0.0 0.0 0.0 0.0 0.0 ServerV2 0
    hdisk11 0.0 0.0 0.0 0.0 0.0 ClientV2 0 Press:
    hdisk13 0.0 8.0 0.5 0.0 8.0 ServerV3 0 "h" for help
    ClientV3 0 "q" to quit
    Name PID CPU% PgSp Owner
    topas 1110202 0.1 1.1 root
    hats_nim 135766 0.0 1.7 root
    oracle 237782 0.0 7.7 oracle8
    oracle 123506 0.0 7.7 oracle8
    oracle 180690 0.0 7.6 oracle8
    hatsd 119642 0.0 8.2 root
    gil 25154 0.0 0.1 root
    oracle 193420 0.0 10.0 oracle8
    hats_nim 140212 0.0 1.7 root
     On 11/10/05, Hunter, Mark <Mark.Hunter@anheuser-busch.com> wrote:
    >
    > Hmmm....
    > Looks like you have minperm%=5, maxperm%=10, and maxclient%=10. Probably
    > should be ok.
    > Are you using jfs or jfs2? jfs2 hard limits by default but jfs does not.
    > (strict_maxclient).
    > Oracle is using 22.5GB approx. How many connections is this? I doubt you
    > can get it, but what is the svmon for oracle just after the database comes
    > up before any connections (minimum usage)? Can you get an svmon during the
    > 800 connections?
    > Try
    > svmon -U | awk '$1~"User"{print;getline;print}'
    > also
    > vmstat 5 5
    > Also, if you can grab a screenprint of a topas session, that might help.
    > ------------------------------
    > *From:* IBM AIX Discussion List [mailto:aix-l@Princeton.EDU] *On Behalf Of
    > *Vincent D'Antonio
    > *Sent:* Thursday, November 10, 2005 9:08 AM
    > *To:* aix-l@Princeton.EDU
    > *Subject:* Re: Oracle connections and memory usage
    >
    > Here is the requested info:
    > minperm = 388872
    > maxperm = 777747
    > maxclient% = 10
    > SGA is set to 16GB, there are no other appl. running on this system just
    > oracle.
    > out put of svmon -U is hugh but here is the beginning of the output:
    >
    >
    > ===============================================================================
    > User Inuse Pin Pgsp Virtual LPageCap
    > oracle8 5695786 89342 68764 5751820 -
    >
    >
    > ...............................................................................
    > SYSTEM segments Inuse Pin Pgsp Virtual
    > 65554 65541 1 65555
    >
    > Vsid Esid Type Description LPage Inuse Pin Pgsp Virtual
    > 0 0 work kernel segment (lgpg_vsid=0) Y 65536 65536 0 65536
    > 12b8a4 - work - 11 3 1 12
    > 2739cf - work - 7 2 0 7
    >
    >
    > ...............................................................................
    > EXCLUSIVE segments Inuse Pin Pgsp Virtual
    > 5621614 22340 68628 5677608
    >
    > Numbers don't lie and I agree with you that I should be able to get my
    > 1500 connections no problem but the system seems to disagree with me on
    > this, maybe I am missing a process that is using more memory than being
    > reported but I couldn't find anything.
    >
    > Thanks again for you suggestions,
    >
    > Vince
    >
    >
    > On 11/10/05, Hunter, Mark <Mark.Hunter@anheuser-busch.com> wrote:
    > >
    > > More information please.
    > > What are your vmo minperm%, maxperm%, and maxclient%? Hopefully you have
    > > tuned these already.
    > > How big is the oracle SGA? Most oracle DBA like to set these really
    > > large.
    > > What other application run on the box?
    > > What does svmon -U using your RAM?
    > > Based upon your numbers, 7MB * 1500 connection = 10.5GB of RAM, so you
    > > should have plenty.
    > >
    >
    > *Mark Hunter*
    > > Anheuser-Busch Cos.
    > > MIS Consultant, ES&SO Server Planning and Integration
    > > (Office: (314) 632-6663
    > > ÊFax: (314) 632-6901
    > > ?Pager: (314) 841-4026
    > > * Email: Mark.Hunter@Anheuser-Busch.com
    > >
    > > The information transmitted (including attachments) is covered by the
    > > Electronic Communications Privacy Act, 18 U.S.C. 2510-2521, is intended
    > > only for the person(s) or entity/entities to which it is addressed and may
    > > contain confidential and/or privileged material. Any review, retransmission,
    > > dissemination or other use of, or taking of any action in reliance upon,
    > > this information by persons or entities other than the intended recipient(s)
    > > is prohibited. If you received this in error, please contact the sender and
    > > delete the material from any computer.
    > >
    > >
    > > ------------------------------
    > > *From:* IBM AIX Discussion List [mailto:aix-l@Princeton.EDU] *On Behalf
    > > Of *Vincent D'Antonio
    > > *Sent:* Thursday, November 10, 2005 8:13 AM
    > > *To:* aix-l@Princeton.EDU
    > > *Subject:* Oracle connections and memory usage
    > >
    > > AIXer's,
    > > I have a P570 8way with 32GB of memory running oracle 10G. Per my DBA
    > > each connection to the database is sucking up 7MB of memory which after a
    > > while we start to swap, this is about 800 connections and need we to support
    > > a lot more than this (1500).
    > > Is there anything that I can tune to improve memory usage or is there
    > > anything within oracle that can be tuned? I have AIX 5.3 ML2 no lpars
    > > just a full system.
    > > Any info to help this would be great.
    > > Thanks in advance for your comments and suggestions,
    > > Vince
    > >
    >
    >

  • Next message: Robert Miller: "Restricting FTP data connection port range?"

    Relevant Pages

    • Re: Oracle connections and memory usage
      ... Have the oracle team look at decreasing the SGA from 16GB to 12GB. ... This should give you enough room to run 1500 connections. ... hats_nim 135766 0.0 1.7 root ... maybe I am missing a process that is using more memory than being reported but I ...
      (AIX-L)
    • Re: Effect of listener on existing connections?
      ... Oracle support is not giving us satisfactory results. ... and attempts to create new connections are ... We also see these messages appearing with regularity in our listener ... how does a blocked listener explain the fact that apps with existing ...
      (comp.databases.oracle.server)
    • Re: Effect of listener on existing connections?
      ... Solaris 10 inside a ZFS zone (our previous system was Oracle ... and attempts to create new connections are ... issue of the listener locking up if you're not using ONS. ... how does a blocked listener explain the fact that apps with existing ...
      (comp.databases.oracle.server)
    • Connection delays causing problems.
      ... ORACLE connections sometimes take tens of seconds to come ... This is true whether the connection is via sqlplus, ACCELL ... Over 150 clients running ACCELL applications. ...
      (comp.databases.oracle.server)
    • Re: Susu 8.1
      ... Only root can manage network connections, ... > with root rights. ... Login as user, open ... > Only root can manage network connections, ...
      (alt.os.linux.suse)