The purpose of this document is to provide a few OS commands for UNIX

 operating systems to gather information about Physical Memory (RAM), 

 swap memory,CPU usage and idle percentage, whether lots of processes

 are in the process run queue and whether too much paging and swapping 

 going on in the server or not so that we can use the information along

 with Statspack report to help diagnose the Database Performance issues.

 Sometimes, the information gathered using these commands will be helpful

 in finding the most OS resource consuming database or non-database 

 processes and will help in identifying the processes to further 

 investigations about why the processes are consuming so much resources and

 whether the corresponding query or application needs to be tuned. 

SCOPE & APPLICATION

-------------------

 This document can be used by anyone with moderate expertise to run UNIX

 OS commands in various operating systems like Solaris, HP-UX, AIX, TRU64,

 Linux, etc. The commands will help us collect OS related information

 to verify whether overuse of any of the OS resources can be affecting 

 the database performance or not. This document does not deal with OS 

 Kernel tuning.

How to use OS commands to diagnose Database Performance issues?

----------------------------------------------------------------

 You want to gather data for Physical memory, Swap space, CPU % usage

 and idle, IO usage in the server in general and also to find a few most

 OS resouce consuming processes to get better understanding about 

 how the UNIX server is performing and whether any of these resources

 are causing a bottleck in the database performance or not.

 Since the popular UNIX systems do not support the same commands or even

 same options for a command, we are proving different commands for different

 OS in the following section. Please refer to the corresponding OS man pages

 for detailed information about each UNIX command mentioned in this document.

 Solaris:

 =========

 $ /usr/sbin/prtconf |grep -i "Memory size"

 $ swap -s

 $ df -k

 $ /usr/local/bin/top

 $ vmstat 5 100

 $ sar -u 2 100

 $ iostat -D 2 100

 $ mpstat 5 100

 Out of these commands, top command  may not be installed in your server by default.

 In that case,you can get it for free from www.sunfreeware.com and install it preferably

 under /usr/local/bin directory and then use it. Please take a few snpshots of the 

 top command output and store it in a file.It refreshes the output screen every 5 sec.

 Prtconf command will show how much Physical Memory (RAM) the Solaris server has.

 Swap command will provide us with the usage of swap space including the RAM.

 Df command will indicate how much space is free in each mount point and also

 provides information about swap space(s).

 Top command wil provide the above information plus information about top CPU

 consuming processes, CPU usage in the system, etc.

 Vmstat will provide information about process run queue, memory usage,

 paging and swapping and CPU % usage in the server. Different options in vmstat

 can provide more specific information, if required.

 Iostat provides IO usage by Disk, CPU % usage, etc. depending on the options

 used. Sar command with "-u" option also provides CPU usage and idle time

 information.

 Mpstat will provide CPU usage stats for a Solaris server with  1 or more CPUs.

 This command is very useful for multi-processor system to provide details about

 the CPU usage by every CPU in the server.

 For example: 

 $ man vmstat

 Here is some sample output from these commands:

 $ prtconf |grep -i "Memory size"

   Memory size: 4096 Megabytes

 $ swap -s

 total: 7443040k bytes allocated + 997240k reserved = 8440280k used, 2777096k available

 $  df -k

 Filesystem            kbytes    used   avail capacity  Mounted on

 /dev/dsk/c0t0d0s0    4034392 2171569 1822480    55%    /

 /proc                      0       0       0     0%    /proc

 fd                         0       0       0     0%    /dev/fd

 mnttab                     0       0       0     0%    /etc/mnttab

 /dev/dsk/c0t0d0s3     493688  231339  212981    53%    /var

 swap                 2798624      24 2798600     1%    /var/run

 swap                 6164848 3366248 2798600    55%    /tmp

 /dev/vx/dsk/dcdg01/vol01

                     25165824 23188748 1970032    93%    /u01

 /dev/vx/dsk/dcdg01/vol02

                     33554432 30988976 2565456    93%    /u02

 ...

 $ top

 last pid: 29570;  load averages:  1.00,  0.99,  0.95 10:19:19

 514 processes: 503 sleeping, 4 zombie, 6 stopped, 1 on cpu

 CPU states: 16.5% idle, 17.9% user,  9.8% kernel, 55.8% iowait,  0.0% swap

 Memory: 4096M real, 46M free, 4632M swap in use, 3563M swap free

   PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND

 29543 usupport   1  35    0 2240K 1480K cpu2    0:00  0.64% top-3.5b8-sun4u

 13638 usupport  11  48    0  346M  291M sleep  14:00  0.28% oracle

 13432 usupport   1  58    0  387M 9352K sleep   3:56  0.17% oracle

 29285 usupport  10  59    0  144M 5088K sleep   0:04  0.15% java

 13422 usupport  11  58    0  391M 3968K sleep   1:10  0.07% oracle

 6532 usupport   1  58    0  105M 4600K sleep   0:33  0.06% oracle

 ...

 $ vmstat 5 100

 procs     memory            page            disk          faults     cpu

 r b w   swap  free  re  mf pi po fr de sr f0 s1 s1 s1   in   sy   cs us sy id

 0 1 72 5746176 222400 0  0  0  0  0  0  0  0 11  9  9 4294967196 0 0 -19 -6 -103

 0 0 58 2750504 55120 346 1391 491 1171 3137 0 36770 0 37 39 5 1485 4150 2061 18 8 74

 0 0 58 2765520 61208 170 272 827 523 1283 0 3904 0 36 40 2 1445 2132 1880 1 3 96

 0 0 58 2751440 58232 450 1576 424 1027 3073 0 12989 0 22 26 3 1458 4372 2035 17 7 76

 0 3 58 2752312 51272 770 1842 1248 1566 4556 0 19121 0 67 66 12 2390 4408 2533 13 11 75

 ...

 $ iostat -c 2 100

     cpu

 us sy wt id

 15  5 13 67

 19 11 52 18

 19  8 44 29

 12 10 48 30

 19  7 40 34

 ...

 $ iostat -D 2 100

     sd15          sd16          sd17          sd18

 rps wps util  rps wps util  rps wps util  rps wps util

   7   4  9.0    6   3  8.6    5   3  8.1    0   0  0.0

   4  22 16.5    8  41 37.9    0   0  0.7    0   0  0.0

  19  34 37.0   20  24 37.0   12   2 10.8    0   0  0.0

  20  20 29.4   24  37 51.3    3   2  5.3    0   0  0.0

  28  20 40.8   24  20 42.3    1   0  1.7    0   0  0.0

 ...

 $  mpstat 2 100

 CPU minf mjf xcal  intr ithr  csw icsw migr smtx  srw syscl  usr sys  wt idl

   0  115   3  255   310  182  403   38   72   82    0   632   16   6  12  66

   1  135   4  687   132  100  569   40  102   68    0   677   14   5  13  68

   2  130   4   34   320  283  552   43   94   63    0    34   15   5  13  67

   3  129   4   64   137  101  582   44  103   66    0    51   15   5  13  67

 ...

 HP-UX 11.0:

 ============

 $ grep Physical /var/adm/syslog/syslog.log

 $ df -k

 $ sar -w 2 100  

 $ sar -u 2 100

 $ /bin/top

 $ vmstat -n 5 100

 $ iostat 2 100

 $ top

 For example:

 $ grep Physical /var/adm/syslog/syslog.log

 Nov 13 17:43:28 rmtdchp5 vmunix:     Physical: 16777216 Kbytes, lockable: 13405388 Kbytes, available: 15381944 Kbytes

 $ sar -w 1 100

 HP-UX rmtdchp5 B.11.00 A 9000/800    12/20/02

 14:47:20 swpin/s bswin/s swpot/s bswot/s pswch/s

 14:47:21    0.00     0.0    0.00     0.0    1724

 14:47:22    0.00     0.0    0.00     0.0    1458

 14:47:23    0.00     0.0    0.00     0.0    1999

 14:47:24    0.00     0.0    0.00     0.0    1846

 ...

 $ sar -u 2 100  # This command generates CPU % usage information.

 HP-UX rmtdchp5 B.11.00 A 9000/800    12/20/02

 14:48:02    %usr    %sys    %wio   %idle

 14:48:04      20       2       1      77

 14:48:06       1       1       0      98

 ...

 $ iostat 2 100

  device    bps     sps    msps  

  c1t2d0     36     7.4     1.0  

  c2t2d0     32     5.6     1.0  

  c1t0d0      0     0.0     1.0  

  c2t0d0      0     0.0     1.0   

  ...

 AIX:

 =======

 $ /usr/sbin/lsattr -E -l sys0 -a realmem

 $ /usr/sbin/lsps -s

 $ vmstat 5 100

 $ iostat 2 100

 $ /usr/local/bin/top  # May not be installed by default in the server

 For example: 

 $ /usr/sbin/lsattr -E -l sys0 -a realmem

 realmem 33554432 Amount of usable physical memory in Kbytes False

 NOTE: This is the total Physical + Swap memory in the system.

       Use top or monitor command to get better breakup of the memory.

 $ /usr/sbin/lsps -s

 Total Paging Space   Percent Used

       30528MB               1%

 Linux [RedHat 7.1 and RedHat AS 2.1]:

 =======================================

 $ dmesg | grep Memory

 $ vmstat 5 100

 $ /usr/bin/top

 For example:

 $ dmesg | grep Memory

 Memory: 1027812k/1048568k available (1500k kernel code, 20372k reserved, 103k d)$ /sbin/swapon -s

 Tru64: 

 ========

 $ vmstat -P| grep -i "Total Physical Memory ="

 $ /sbin/swapon -s

 $ vmstat 5 100

 For example:

 $ vmstat -P| grep -i "Total Physical Memory ="

 Total Physical Memory =  8192.00 M

 $ /sbin/swapon -s

 Swap partition /dev/disk/dsk1g (default swap):

    Allocated space:      2072049 pages (15.81GB)

    In-use space:               1 pages (  0%)

    Free space:           2072048 pages ( 99%)

 Total swap allocation:

    Allocated space:      2072049 pages (15.81GB)

    Reserved space:        864624 pages ( 41%)

    In-use space:               1 pages (  0%)

    Available space:      1207425 pages ( 58%)

 Please take at least 10 snapshots of the "top" command to get an idea 

 aboud most OS resource comsuming processes in the server and the different

 snapshot might contain a few different other processes and that will indicate

 that the use of resouces are varying pretty quickly amound many processes.