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
繁體中文
Monitor Oracle Resource Consumption in UNIX
评论 (0) 