This discussion is archived
8 Replies Latest reply: Jan 24, 2013 5:24 AM by hbaruas RSS

Prstat

hbaruas Newbie
Currently Being Moderated
Hello,

System: 2 Node RAC
OS: sun4v sparc
Oracle Version: 11.2.0.1
Ram: 64G
SGA: 26G
PGA: 24G

Problem: Please explain if what is seen in prstat is normal, or how can I diagnose it further.
*Node 1:*
   *PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP*
  1830 grid       26G 8889M sleep   53    0   0:01:51 0.8% oracle/11
  1591 grid     1227M 1066M sleep   59    0 570:22:59 0.7% java/75
 23804 grid       26G 8891M sleep   59    0   0:00:12 0.7% oracle/34
 26006 grid       26G 8892M sleep   53    0   0:00:06 0.6% oracle/11
 14458 grid       26G 8893M sleep   59    0   3:05:19 0.6% oracle/31
   459 grid       26G 8893M sleep   59    0   2:32:06 0.6% oracle/33
 23899 grid       26G 8889M cpu1     0    0   0:00:09 0.6% oracle/11
 17314 grid       26G 8893M sleep   59    0   4:15:22 0.5% oracle/31
 25543 grid       26G 8888M sleep   59    0   0:00:08 0.5% oracle/11
 19997 oracle     10M 9768K cpu15   29    0   0:35:43 0.4% prstat/1
 15717 grid       26G 8896M sleep   59    0   0:01:19 0.4% oracle/11
 25877 grid       26G 8888M sleep    0    0   0:00:05 0.4% oracle/11
 26244 grid       26G 8888M sleep   59    0   0:00:05 0.4% oracle/11
 18856 grid       26G 8892M sleep   59    0   0:00:24 0.4% oracle/11
 13436 grid       26G 8893M sleep   59    0   0:04:48 0.4% oracle/11
*TASKID    NPROC  SWAP   RSS MEMORY      TIME  CPU PROJECT*
    64      800   *35G   34G    54% 1345:52:5  23% system*
102292       15   *26G   27G    42%   0:57:22 0.4% user.oracle*
102646        4   *26G   26G    41%   0:00:09 0.2% user.oracle*
     1        4   24M   28M   0.0%   8:14:48 0.0% system
    62        1 4368K   10M   0.0%   7:37:13 0.0% system
*Total: 878 processes, 10496 lwps, load averages: 8.90, 9.70, 10.02*


*Node 2:*
   *PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP*
   977 grid      951M  868M cpu7     0    0 378:51:53 *3.1% java/66*
 16281 grid       26G 8893M sleep   50    0   0:00:09 1.0% oracle/11
  7536 grid       26G 8938M cpu27    0    0   0:01:40 0.8% oracle/11
  6238 grid       26G 8897M sleep   59    0   0:01:50 0.7% oracle/33
 17783 grid       26G 8893M sleep   59    0   0:00:17 0.7% oracle/11
 27220 grid       26G 8888M sleep   59    0   0:00:35 0.6% oracle/11
 15055 oracle     10M 9136K cpu28   59    0   0:36:40 0.5% prstat/1
  6445 grid       26G 8896M sleep   59    0   0:02:00 0.5% oracle/11
 19168 grid       26G 8888M sleep   59    0   0:00:06 0.4% oracle/31
  8137 grid       26G 8897M sleep   59    0   0:01:11 0.4% oracle/25
  2107 grid       26G 8896M sleep   59    0   0:01:35 0.4% oracle/11
 21698 grid       26G 8888M sleep   59    0   0:02:35 0.4% oracle/11
 20184 grid       26G 8888M sleep   59    0   0:00:05 0.4% oracle/11
 20855 grid       26G 8891M sleep   59    0   0:00:06 0.3% oracle/11
   475 grid       26G 8893M sleep   59    0   0:00:09 0.3% oracle/11
*TASKID    NPROC  SWAP   RSS MEMORY      TIME  CPU PROJECT*
    63      803   *35G   34G    54% 884:39:17  30% system*
 26607       25   *26G   27G    42%   0:48:15 0.5% user.oracle*
     1        5   25M   29M   0.0%   3:36:19 0.0% system
    61        1 4368K   10M   0.0%   3:00:55 0.0% system
    19        1 9992K   11M   0.0%   2:05:43 0.0% system
*Total: 875 processes, 11996 lwps, load averages: 11.92, 11.41, 11.49*
I am concerned about things as stated below:
1> On node 1 there are three projects using 35G, 26G, 26G : whose total is way beyond the total available RAM? How is it actually calculated?
2> On node 2 the top process is java by grid user, always about 3%. There is no such constant top process on node 1 . The java process is
ps -ef|grep java gives
/u01/app/11.2.0/grid/jdk/jre/bin/sparcv9/java -Doracle.supercluster.cluster.ser
Should not user.oracle be on top for projects than the system?

Any pointers, help , information much appreciated.

Thank You

Edited by: 931719 on 16-Jan-2013 07:54

Edited by: 931719 on 16-Jan-2013 09:24
  • 1. Re: Prstat
    SalmanQureshi Expert
    Currently Being Moderated
    How may CPUs are there in each node? One thing alarming i can see is load average on both nodes which is 8.9 and 11.2 respectively.
    On a 8 CPU machine, load average should not be more than 8 (means 8 processes waiting in the queue to get CPU). So if you CPUs are let say 16, load average is OK as long as it is under 16.
    1> On node 1 there are three projects using 35G, 26G, 26G : whose total is way beyond the total available RAM? How is it actually calculated?
    Do you mean 3 databases are running? At the top, you say SGA 26G, what do you mean by this if there are 3 databases running? What is memory_target, memory_max_target on all 3 databases if you are using automatic shared memory management, otherwise mention SGA_TARGET, SGA_MAX_SIZE and PGA_TARGET for all databases.
    2> On node 2 the top process is java by grid user, always about 3%. There is no such constant top process on node 1 . The java process is
    ps -ef|grep java gives Should not user.oracle be on top for projects than the system?
    Nothing to worry about and fortget about what is on top and why. 3% usage is very very low and nothing problematic.


    Salman
  • 2. Re: Prstat
    hbaruas Newbie
    Currently Being Moderated
    Hi Salman

    There are 32 processors.
    There is only one database running. Hence I was concerned.
  • 3. Re: Prstat
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    RAM side everything seems good. RAM is allocated same as memory you set for memory_target and memory_max_target. Then every session occupies a bit of RAM and this is total usage of RAM by Oracle.
    you did not highlight about your number of CPUs,load average does seem problematic if you have less than 10 CPUS per node.
    Salman
  • 4. Re: Prstat
    hbaruas Newbie
    Currently Being Moderated
    Hi Salman,

    The server has 1 physical processor and the physical processor has 32 virtual processors.
    5.10 Generic_142900-03 sun4v sparc SUNW,Sun-Fire-T200
    
    
    memory_max_target       big integer  0
    memory_target              big integer  0
    parallel_servers_target     integer       256
    pga_aggregate_target    big integer   24627M
    sga_target                    big integer   8G
    sga_max_size                 big integer  26G
    Thanks...

    Edited by: saura on 18-Jan-2013 02:48
  • 5. Re: Prstat
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    The server has 1 physical processor and the physical processor has 32 virtual processors.
    I have no idea how virtual process in SPARC works, please consult your sysadmin on this whether 32 virtual processors with 11 load average is ok or not.
    pga_aggregate_target big integer 24627M
    Why this value is so much high, who set this and why? Was pga hit ratio very low which made to set this high value? Check your AW report and/or v$pgastat about hit ratio and if you can lower the value
    sga_target big integer 8G
    Actually your instance is utilizing only 8G, not 26 G which is value of sga_max_size. how is buffer cache and library cache hit ratios?

    Salman
  • 6. Re: Prstat
    hbaruas Newbie
    Currently Being Moderated
    Thanks for response Salman.

    But more than Oracle configuration and load, I want to understand PRSTAT and its output.

    if 8G is used, y it is showing so high values , multiple times.
    Also I checked today and now it is showing only one value system as 34G.

    Why such indifferent behaviour?

    Any idea...or pointers would be great.
  • 7. Re: Prstat
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    I am only able to explain what you see memory usage under user Oracle. You need to consult some Solaris forums or your system admin for RAM usage by other processes running under "SYSTEM"
    if 8G is used, y it is showing so high values , multiple times.
    8 GB is what you have set for your SGA_TARGET, but, your SGA_MAX_SIZE is 26G and this is the value Oracle already have allocated and RAM usage under Oracle user is above 27G on node 2.
    26G for SGA and rest of memory is allocated to your sessions which connect to your database.
    hope it clears now.

    Salman
  • 8. Re: Prstat
    hbaruas Newbie
    Currently Being Moderated
    Thanks Salman for all the help. Will speak to my sys admin. If any break through will update it here.

Legend

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