How to use OS commands to diagnose Database Performance issues?- Oracle

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

Raw Partitions and Windows NT Raw Devices and Oracle - 20 Common Questions and Answers

Oracle 中的 How to use OS commands to diagnose Database Performance issues?

评论 (0) All