Re: Oracle connections and memory usage

• Previous message: Robert Miller: "Re: Restricting FTP data connection port range?"
• Maybe in reply to: Vincent D'Antonio: "Oracle connections and memory usage"
• Next in thread: Vincent D'Antonio: "Re: Oracle connections and memory usage"
• Reply: Vincent D'Antonio: "Re: Oracle connections and memory usage"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date:         Thu, 10 Nov 2005 11:36:20 -0600
To: aix-l@Princeton.EDU

 
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

• Previous message: Robert Miller: "Re: Restricting FTP data connection port range?"
• Maybe in reply to: Vincent D'Antonio: "Oracle connections and memory usage"
• Next in thread: Vincent D'Antonio: "Re: Oracle connections and memory usage"
• Reply: Vincent D'Antonio: "Re: Oracle connections and memory usage"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]