Re: Oracle connections and memory usage

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

  • Next message: Lamar Saxon: "Re: Oracle connections and memory usage" 
    Date:         Thu, 10 Nov 2005 13:36:10 -0500
To: aix-l@Princeton.EDU

    Mark,
    Thanks for the info I will tey this and see what happens. Also something
    with the P5 servers it is an 8 way system but lparstat -i shows me with 8
    virtual CPU's also. May be something to do with the dual core design of the
    CPU's?
    Thanks
    Vince

     On 11/10/05, Hunter, Mark <Mark.Hunter@anheuser-busch.com> wrote:
    >
    > I notice you have 16 cpus, not 8. Shouldn't make a difference though.
    > Without any users, you have 75% of the boxes RAM in use. That leaves less
    > than 8GB for your 10.5GB worth of connections. Yes, you are going to swap.
    > Tune minperm% to 3, maxperm% to 5, maxclient% to 5. With 32GB, and
    > especially with Oracle, you don't need a large disk cache. 5% is still
    > 1.5GB, so you should be fine.
    > Have the oracle team look at decreasing the SGA from 16GB to 12GB. See
    > what it does to the cache hit %.
    > This should give you enough room to run 1500 connections.
    > Mark Hunter
    >
    > ------------------------------
    > *From:* IBM AIX Discussion List [mailto:aix-l@Princeton.EDU] *On Behalf Of
    > *Vincent D'Antonio
    > *Sent:* Thursday, November 10, 2005 10:22 AM
    > *To:* aix-l@Princeton.EDU
    > *Subject:* Re: Oracle connections and memory usage
    >
    > 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: Lamar Saxon: "Re: Oracle connections and memory usage"