Monitor Oracle Resource Consumption in UNIX

【 tulaoshi.com - Oracle教程 】

PURPOSE

=======

This article is intended to provide DBAs an overview of the resources

consumed by Oracle, and the tools commonly used to monitor the resource

consumption.

SCOPE & APPLICATION

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

Database performance is bounded by the system resources. Sometimes,

poor database performance can be caused by faulty configuration of the

instance and database. Sometimes, it can be caused by abnormal resource

consumption by an Oracle transaction, user, or process. It is essential

for DBAs to proactively monitor the resource consumption, and take any

corrective actions before potential serious impacts.

Memory, CPU, and I/O are the three most common resources consumed by

Oracle. We will discuss these resources, and list some of the commonly

used tools that monitor them. These resources can be monitored at both

Oracle Server and Operating System levels. DBAs should acquire any detailed

information about the OS tools from the System Administrators or OS vendors.

OVERVIEW

========

Oracle Instance

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

An Oracle Instance is a set of System Global Area and background processes.

It is started during �startup nomount�. The characteristics of an instance

are:

 - Its name is defined by environmental variable ORACLE_SID.

  - It is started based on the configurations defined in init.ora file.

  - It has its own set of SGA and background processes.

  - It can only belong to one database at one time.

  - Multiple instances can access the same database in OPS configuration.

There can be multiple oracle instances from the same $ORACLE_HOME. They

are only limited by the OS resources, such as disk, memory, kernel parameters,

etc. Each time an instance is started, the OS is being asked to give some

key resources according to the parameters specified in init.ora

for that instance. Each Oracle instance has two main areas of memory structures,

System Global Area (SGA) and Program Global Area (PGA) for background processes.

SYSTEM RESOURCES

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

Memory

------

There are several memory structures used by Oracle, SGA, PGA, UGA, and

sort area.

1) Shared Memory & Semaphores

Oracle uses shared memory for efficiency. The OS does not have

to load up the same address page(s) into the memory each time a process

needs to reference it. Instead the process can just reference the one memory

location, read/write to it, and then leave. Therefore, the data is not

moved from one processs memory address space to another. In order to control

memory integrity so that only one process is accessing that memory address,

semaphores are used.

Semaphores have only two values, set or unset. When a process goes to

reference a memory location, it first checks to see if the semaphore allocated

for that memory location is set. If so, it waits until that semaphore location

is free. Once available, it will first set the semaphore so to not allow

other processes to interrupt or corrupt the current processes read/write.

The size of the SGA is made up by shared pool, database buffer cache,

redo log buffer, large pool, and java pool. A commonly used formula to

calculate the size of the SGA is:

((db_b