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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








The Relationship Between the Database Administrator and the Systems Administrator

Oracle Database Tips by Donald Burleson

In many Oracle shops, computer professionals called systems administrators are responsible for the setup, configuration, and tuning of the Oracle database server. The abilities of system administrators vary widely, from excellent support and cooperation to neophyte support and complete noncooperation.

Because of the tight coupling between database performance and server performance, it is imperative that the Oracle DBA have access to the tools that we describe here. By themselves, the systems administrators will not have enough information about what is happening inside the database to properly tune the server. Conversely, the DBAs cannot get the information they need to properly configure Oracle if they cannot get access to the server monitor utilities.

Many Oracle shops give root access to the DBA so they will have full control over their database server. The system administrator continues to be responsible for the configuration and system software on the server, but the DBA accepts responsibility for setting the kernel parameters and managing the interface layer between Oracle and the operating system environment.

A small minority of shops will restrict access to the root account, but these shops will provide the Oracle DBA with access to all of the server monitor tools and system utilities. In either case, it is imperative that the Oracle DBA have access to the system monitor tools.

Online Server Monitor Tools

Before we address the specific tuning techniques for your database server, let's begin with a brief tour of several online tools that can help us tune the server. There are many companies that provide third-party server performance monitors, but there are several ?freeware? server monitors that can help you see what is happening on the server.

Using glance

The glance utility is provided on HP/UX systems to provide a graphical display of server performance. Glance displays current CPU, memory, and disk and swap consumption, and also reports on the top processes. The glance utility is invoked by entering glance from the UNIX prompt. The name for glance is quite appropriate because it gives you a complete glance at the whole server. Figure 6-1 shows an example glance screen.

Figure 6-28: A sample screen from glance

The histograms at the top of the glance screen show the amount of consumption on CPU, disk, memory, and swap. The output within each histogram shows the high-water mark, and the amount of the resource in system and user mode. For example, from Figure 6-1 we see that this server is only using 4 percent of the CPU capacity, but the RAM memory is at 90-percent capacity.

The second part of the glance utility shows the most intensive tasks on the database server. The glance screen in Figure 6-1 was for an Oracle server running Oracle Applications, and we see that the Oracle Forms 4.5 runtime task (f45runw) is the most intensive task on the server. For each task displayed, we also can see the amount of disk I/O currently being consumed by the task. The glance screen will refresh every few seconds, so we get a continual picture of the load on the Oracle server. To exit glance, you enter Ctrl-C. Now let's look at another server monitor tool that is quite similar to glance.

Using top to Monitor the Server

The top utility is used to show CPU consumption, RAM memory consumption, and the top sessions on a UNIX server. The top utility is invoked by entering top from the UNIX prompt. The output from top is displayed in three sections.

The top Load Averages

At the very beginning of the following top output we see a series of three numbers. These are the called load average metrics. The load average is an arbitrary number that describes the load on the system. The first load average value is the immediate load for the past minute. The next value represents the load average from 5 minutes in the past. The third value is the load average from 15 minutes ago. Whenever the load average rises above 1, you can assume that the processors are fully burdened and you should immediately run vmstat to check the run queue values.

L 6-1

System: corp-hp9 Thu Jul  6 09:14:23 2002
Load averages: 0.04, 0.03, 0.03
340 processes: 336 sleeping, 4 running

The top CPU Summary

The first output from top shows the load on each processor and the current top sessions in terms of CPU utilization. Top gives details on each CPU on the server, and we can immediately see from the listing here that this server has six CPUs, numbered 0?5:

L 6-2

root> top
Cpu states:
 0    0.06   5.0%   0.0%   0.6%  94.4%   0.0%   0.0%   0.0%   0.0%
 1    0.06   0.0%   0.0%   0.8%  99.2%   0.0%   0.0%   0.0%   0.0%
 2    0.06   0.8%   0.0%   0.0%  99.2%   0.0%   0.0%   0.0%   0.0%
 3    0.06   0.0%   0.0%   0.2%  99.8%   0.0%   0.0%   0.0%   0.0%
 4    0.00   0.0%   0.0%   0.0% 100.0%   0.0%   0.0%   0.0%   0.0%
 5    0.00   0.2%   0.0%   0.0%  99.8%   0.0%   0.0%   0.0%   0.0%
---   ----  -----  -----  -----  -----  -----  -----  -----  -----
avg   0.04   1.0%   0.0%   0.2%  98.8%   0.0%   0.0%   0.0%   0.0%

Top Sessions

Now let's look at the second section from the top command. The second section of top output details the current top sessions in terms of CPU utilization, and appears as follows:

L 6-3

System: core-hp1                                      Mon Dec 25 07:17:56 2001
Load averages: 0.03, 0.04, 0.05
372 processes: 368 sleeping, 4 running
Cpu states:
 0    0.11   0.0%   0.0%   0.0% 100.0%   0.0%   0.0%   0.0%   0.0%
 1    0.02   0.0%   0.0%   0.0% 100.0%   0.0%   0.0%   0.0%   0.0%
 2    0.02   0.0%   0.0%   0.0% 100.0%   0.0%   0.0%   0.0%   0.0%
 3    0.03   0.0%   0.0%   0.0% 100.0%   0.0%   0.0%   0.0%   0.0%
 4    0.00   0.0%   0.0%   0.0% 100.0%   0.0%   0.0%   0.0%   0.0%
 5    0.01   0.0%   0.0%   0.0% 100.0%   0.0%   0.0%   0.0%   0.0%
---   ----  -----  -----  -----  -----  -----  -----  -----  -----
avg   0.03   0.0%   0.0%   0.0% 100.0%   0.0%   0.0%   0.0%   0.0%

Memory: 736056K (417860K) real, 733560K (422192K) virtual, 1101512K free  Page#

 3   ?    16664 oracle   154 20 20304K  1892K sleep   15:32  2.21  2.21 oracleTE
 5   ?       36 root     152 20     0K     0K run     57:52  1.65  1.65 vxfsd
 2   ?      477 root     154 20    32K    80K sleep  160:55  0.71  0.71 syncer
 3   ?    14963 oracle   154 20  4448K  2780K sleep    4:39  0.32  0.32 oraweb
 0   ?    15980 oracle   154 20  4704K  3020K sleep    4:41  0.31  0.31 oraweb
 0 pts/tb 21355 root     158 20   536K   184K sleep    0:00  0.77  0.30 sh

In this section of the top output, we see the process ID (PID), username, the dispatching priority (PRI), the nice value (NI), the size of each task's memory (SIZE), the state, the execution time, and the percentage of CPU being used by each process.

While top has many columns of information, there are only a few columns that are of interest to you as the Oracle DBA:

  • Load averages These are the load averages for the entire server. Values greater than 1 may indicate an overload problem on the server.

  • CPU The first section of the top output shows a load summary for each CPU. The CPU column in the detailed listing shows which CPU is servicing each individual task.

  • LOAD The LOAD column shows the load on each of the CPUs.

  • NI The NI (nice) value is the dispatching priority of the task, and refers to the rate that the task receives services from the CPUs.

  • IDLE This shows the percentage of time that each CPU has been idle.

In sum, the top and glance utilities are reliable reactive utilities that allow the DBA to see what is happening on the database server. Now let's take a look at a more detailed online monitor, the System Activity Reporter, or sar for short.


This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.