How to use OS commands to diagnose Database Performance issues?

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 req