Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 


Oracle tuning:
Monitoring hardware with UNIX tools

Mar 7, 2001
Donald Burleson

2001 TechRepublic, Inc.

If an Oracle database server has a hardware problem, such as an overloaded CPU, excessive memory swapping, or disk I/O bottleneck, no amount of tuning within the Oracle database itself is going to improve performance. So the first thing an Oracle professional must examine during tuning is the server's hardware.

Because Oracle databases run on dozens of different platforms, from mainframes to personal computers, it's very difficult to give guidelines for tuning every type of server environment. However, since more than 85 percent of Oracle databases run in the UNIX environment, we're going to discuss some of the UNIX tools for determining whether a UNIX database server is undergoing stress.

The vmstat utility

One of the most common tools used in monitoring hardware for an Oracle database is the UNIX vmstat utility. This utility takes an elapsed-time snapshot of what's going on in the server environment. By issuing the vmstat command, we can take a look at the run queue for the CPU, the memory page and page out of activity, and the activity within each CPU on the database server.

Some UNIX system administrators and Oracle DBAs have figured out methods for capturing vmstat output and placing it in the Oracle tables for more detailed analysis. But in order to conduct meaningful analysis, you must know what to look for. It's important to recognize that in each of the different UNIX flavors (Solaris, AIX, HP/UX), vmstat has different display formats. Although the vmstat output is different depending on the flavor of UNIX, all flavors share some important server metrics. In Table 1 below, the important metrics appear in bold.


Table 1


As you can see, each UNIX flavor of vmstat reports provides different information about the current status of the server. But despite these differences, only a small number of metrics is important for server monitoring. These include:

  • r—runque: Shows the number of tasks waiting for CPU resources. When this number exceeds the number of CPUs on the server, a CPU bottleneck exists.
  • pi—page-in: Occurs when the server is experiencing a shortage of RAM. Any nonzero value for pi indicates excessive swap activity.
  • us—User CPU: Shows the amount of CPU used by the user state.
  • sy: Shows the percentage of CPU being used to service system tasks.
  • id—idle: Shows the percentage of CPU that is idle.
  • wa—wait (AIX only): Shows the percentage of CPU that is waiting on external operations such as disk I/O.

Note that the CPU metrics are expressed as percentages. Hence, all of the CPU values (us+sy+id+wa) will always sum to 100.
 

The popular Ion tool is the easiest way to analyze Oracle OS parameter performance and Ion allows you to spot hidden OS related  performance trends.  Ion is our favorite Oracle tuning tool.

Let's examine each of the important measures of server hardware performance.

Run queue (r) - The run queue values are usually the very first values displayed on the left of all vmstat outputs. In the UNIX environment, the run queue is used to display the number of active tasks that are currently waiting for CPU resources. Hence, it is important that the run queue values never exceed the number of CPUs on the processor. For example, for an Oracle database server with eight CPUs (8-way SMP), any run queue value in excess of 8 would indicate that the CPU is being overloaded and tasks are waiting for service.

RAM page-in (pi) - The use of RAM on the database server is also very important. In any virtual memory environment, it is not uncommon to see RAM pages moved out to a swap disk. This is a special disk area in UNIX that's reserved for holding memory pages so that the processor is capable of addressing RAM in excess of its full capability. While page-out of operations are a normal part of any server's operations, page-in operations indicate that the real amount of available RAM has been exceeded and that additional RAM pages are required on the server. Whenever page-ins occur, the memory contents must be read in from the swap disk. This page-in activity is very time-consuming, and memory page-in operations can cause significant slowdowns of any Oracle database.

Disk bottlenecks

Some versions of vmstat, such as IBM's AIX environment, allow the DBA to get a general idea that an I/O process is slowing down the system. In AIX, there's a wait (wa) column that indicates the percentage of CPU that is in the wait-state, waiting for other resources on the server.

An IBM disk I/O monitor


For other UNIX environments, such as Solaris, HP/UX, and Linux, the UNIX iostat utility is generally used to take a look at the I/O activity on each device that's been hooked up to the Oracle server. For more information on using the iostat utility with Oracle, see my article "Tuning Disk I/O in Oracle8" in Oracle Magazine.

In today's disk architectures, we commonly find that disk striping is used to alleviate these types of disk I/O bottlenecks. The RAID 0+1 method effectively does block-level striping across the disk devices, thereby ensuring that the disk load rises and decreases at the same level. However, it's important to recognize that individual disk devices for Oracle databases are becoming larger all the time. Today it's not uncommon to see an individual disk with 73 gigabytes of storage. In such an environment, it's possible to store an entire Oracle database on a single disk. Without disk array caching tools such as EMC disk caching, the Oracle DBA might find the small, frequently referenced area of disk is constantly being recalled, causing the read-write heads on the disk device to have a significant impact on the overall performance of the database.

This type of I/O bottleneck can also commonly be seen in cases where the DBA is running hot backups while the database is active. The Oracle hot backups task is attempting to read the database from disk, cylinder by cylinder, while the online database engine is attempting to randomly access information on the disk. The contention is seen in the demand for the read-write heads on the disk, as they relocate themselves under competing cylinders, and in the significant slowdown of the system during hot backups.

Database server trend analysis

Many UNIX system administrators have utilities that can capture and plot the server stress over a period of time. These server statistics are very useful for the Oracle DBA because they can show periods where the server is overloaded. In the example in Figure A, we see a plot of CPU and CPU waits, averaged by the hour of the day. It is clear that this database is experiencing periods of high activity each day at 5:00 A.M. and at 11:00 P.M.

 Plotting database server statistics by hour of the day


We can also plot abnormal conditions such as page-in operations. Alert reports, such as the one shown in Figure B, can be created and e-mailed to the DBA to show when the Oracle server is short on memory.



This type of page-in information can also be used to predict the overall load on the database server. One of the jobs of the Oracle DBA is to predict when additional hardware resources are needed for the database. By plotting the consumption of memory and CPU and performing a linear regression, the Oracle DBA can accurately predict when additional RAM or CPU will be required.

A predictive report for RAM memory on an Oracle server


Summary

This article is intended to give you a high-level, overall understanding of monitoring and tuning the hardware on your Oracle database server. The main points are to carefully look for shortages in CPU, RAM, and disk I/O bottlenecks. Next week's article will look at tuning within the confines of the Oracle database, beginning with an overview of Oracle instance tuning.


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational