Monitor Oracle Resource Consumption in UNIX

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_block_buffers * block size) + (shared_pool_size +

large_pool_size + java_pool_size + log_buffers) + 1MB

See more detailed information in 

How to determine

SGA Size (7.x, 8.0, 8i, 9i).

For optimal database performance, SGA should fit into real memory, and

avoid swapping. The size of the SGA is also bounded by OS kernel parameters,

such as SHMMAX in Unix. To learn more about shared memory, see 

Shared

memory requirement on Unix.

Since the SGA can be accessed by all processes that have been allocated,

this is used to allow multiprocessing and access between processes within

the SGA.

A few of the common semaphores and shared memory segments that Oracle

uses are:

 - SEMMNI => Max number of semaphores sets/identifiers

  - SEMMNS => Max number of semaphores in system

  - SHMMNI => Number of shared segments identifiers to be pre-allocated

  - SHMMAX => Max shared segment size

For more information, see 

Unix Semaphores and Shared

Memory Explained.

Also see the corresponding Oracle Installation Guide for your release of Oracle,

that indicate minimum values for semaphore and shared memory. 

2) Private Memory

PGA is a memory region containing data and control information

for a single server or background process. The size of PGA depends on the

database configuration, and what the process does. In a dedicated server

configuration, where one server process interacts with one user process,

PGA contains stack space and UGA. UGA is made up by user session data,

cursor state, and sort area.  In a multithreaded server configuration,

where one shared server is shared by multiple user processes, UGA is part

of the shared pool.

The PGA exists as:

- Nonshared memory area to which a process can write

 - One PGA is allocated for each server or background process

 - Once the user has connected, a user can never run out of PGA space.

Otherwise the connection will not happen and may result in an ora-4030 error.

Oracle parameters that affect the sizes of PGA are:

 - open_links

  - db_files

  - sort_area_retained_size

  - sort_area_size

To calculate the current PGA size:

 SESSION PGA MEMORY => Statistic containing the current PGA size for a session

 SESSION PGA MEMORY MAX => Statistic containing the peak PGA size for a session

PGA = dedicated server processes - UGA = Client machine process

To view a listing of the UGA and PGA memory for every session, see 

TFTS:

LISTING MEMORY USED BY ALL SESSIONS

Note: On the RS/6000, each shadow process stores its PGA in a shared memory segment.

The size of the stack space in each PGA created on behalf of Oracle

background processes (such as DBWR and LGWR), is affected by some additional

parameters.

CPU

---

CPU consumption varies with the activities of the processes. During

the peak workload, the DBAs and the SAs may see CPU utilization go up to

90%. In general the DBAs should balance the workload to avoid overloading

the system.

I/O

---

The amount of I/O occurrence also depends on the activities of the processes. 

While memory I/O may be necessary, disk I/O should be minimized. To avoid

disk I/O contention, files should be distributed across devices. Some of

the background processes can be I/O intensive, such as DBWn, LGWn, ARCn,

and CKPT. In some situations, DBAs may find multiple DBWn/ARCn, or DBWR/ARC0/LWGR

I/O slaves beneficial.

MONITORING AT THE SERVER LEVEL

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

V$ dynamic performance views capture the cumulative statistics of resource

consumptions, at both system or session levels. STATSPACK  and Utlbstat/utlestat

reports summarize the statistics from these v$ views for a defined period

of time for the database and tkprof utility reports statistics at query

level. Init.ora parameter TIMED_STATISTICS should set to be true.

The descriptions of v$ views are in Oracle 7/8/8i/9i Server Reference manual.

To learn more about analyzing STATSPACK  or utlbstat/utlestat reports,

see  StatsPack FAQ  and

 Systemwide

Tuning using UTLESTAT Reports in Oracle7/8

To learn more about interpreting tkprof output, see Note 

:Tkprof

Interpretation.

Examples

--------

The following examples shows the output from all different tools available

in Oracle to get this information:

- STATSPACK and bstat/estat reports

Statistic                    Total         Per Transact           Per Logon         Per Second 

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

CPU used by this session     2278466       222.59                 1974.41           203.6 

CPU used when call started   2239857       218.82                 1940.95           200.15 

CR blocks created            2938          .29                    2.55              .26 

session pga memory           7138227       34521                  991               191 

session pga memory max       40327524      745091                 98305             756 

session uga memory           326143        96312                  57843             654 

session uga memory max       2483564       185735                 12386             700 

- tkprof .prf output file

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS 

call             count        cpu      elapsed       disk       query      current        rows 

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

Parse                3       0.04         0.05          0           0            0           0 

Execute              4       0.00         0.05          0           0            0           0 

Fetch                2       0.00         0.06          2          17           68           1 

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

total                9       0.04         0.16          2          17           68           1 

- Scripts output

REM Monitor memory usage 

SQL> select sid, name, value 

      2  from v$statname n, v$sesstat s 

      3  where n.statistic# = s.statistic# 

      4  and n.name like %memory% 

      5  order by sid; 

     SID NAME                              VALUE 

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

        1  session uga memory               18252 

        1  session uga memory max           18252 

        1  session pga memory max           59568 

        1  sorts (memory)                   0 

        1  session pga memory               59568 

      . . 

REM monitor CPU usage 

SQL> select sid, name, value 

       2  from v$statname n, v$sesstat s 

       3  where n.statistic# = s.statistic# 

       4  and n.name like %cpu% 

       5  order by sid; 

     SID NAME                           VALUE 

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

         1 recursive cpu usage           0 

         1 parse time cpu                0 

         1 OS Wait-cpu (latency) time    0 

       . . 

REM  monitor I/O 

SQL> select file#, phyrds, phywrts 

      2  from v$filestat; 

       FILE#         PHYRDS          PHYWRTS 

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

           1           3239              141 

           2             80              246 

           3              9              163 

           4              6                3 

           5              5                3 

           . . 

MONITORING AT THE OPERATING SYSTEM LEVEL

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

OS Semaphores

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

Each Oracle instance needs to have a set amount of semaphores. The total

amount of semaphores required is derived from the processes parameter

inside that Oracle instance init.ora file. As more instances and/or databases

are added the OS kernel parameter SEMMNS will need to be adjusted accordingly.

For more information, see Semaphore

Calculations

- Estimating semaphore sets/semaphores for an Instance.

Ulimits

--------

Refer to the install guide for the basic kernel parameters that need

to be set. When increasing the amount of instances & databases on this

Unix server, one will need to increase the kernel parameters accordingly.

To check the Unix oracle user id limits:

% ulimit -Sa

(This is the output you may expect to see)

 time(seconds)          unlimited

 file(blocks)           unlimited

 data(kbytes)           unlimited

 stack(kbytes)          unlimited

 memory(kbytes)         unlimited

 coredump(blocks)       2097151

 nofiles(descriptors)   unlimited

Monitoring Tools

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

Commonly used tools are vmstat, iostat, pstat, size, ipcs, ps, sar, and other OS

specific tools.  View the manual page to obtain the usage and column

descriptions.

Here are a few specific Unix supplied tools to help monitor different OS activities:

1) Memory: To see how much memory is currently being used, vmstat (virtual memory

statistics) focuses mainly on CPU and memory.

    OS                 Command

    ~~~~~~~~~~         ~~~~~~~~~

    Sun Solaris:       vmstat

    HP:                vmstat -n

    IBM:               /bin/vmstat

    Compaq:            /sbin/hwmgr

    Linux:             xosview

  For specific examples of diagnosing memory on various platforms:

   HPUX --> see

Diagnosing Oracle memory on HP using GLANCE

   AIX --> see

Diagnosing Oracle Memory on AIX using SVMON

  SUN -->see Diagnosing

Oracle memory on Sun Solaris using PMAP

2) I/O: You should regulary monitor disk I?O statistics by using utilities such as

"sar -d" or "iostat". Average service times of 50ms or lessare reason for

concern if it continues over a long time. One of the goals should be minimizing disk I/O by balancing the load on the disks.

To watch over disk space usage, at the Unix prompt issue the command:  df -k

Look at where the datafiles and ORACLE_HOME are mounted. Pay attention to how much

space is available. General rule of thumb is to never allow

these mount points get to 90% full (i.e. 10% available).

3) CPU utilization:

    OS                 Command

    ~~~~~~~~~~         ~~~~~~~~~

    Sun Solaris:       sar -u

    IBM:               ps av (or) iostat 3 20

    Digital:           /usr/sbin/pset_info

    Linux:             xosview

In addition of monitoring the CPU usage you should monitor the runqueue to determine

if processes are waiting for an available processor. You can

use "sar -q" to monitor the runqueue.

4) To see how much CPU time is being used by each processor on a multiprocessor machine:

    OS                 Command

    ~~~~~~~~~~         ~~~~~~~~~

    Sun Solaris:       /usr/bin/mpstat

    HP:                /usr/sbin/sar -M 5 5

    IBM:               vmstat -> Under cpu, if "us" is a very high number you have a cpu intensive process

5) To see the number of CPUs there are in the machine, and their status:

    OS                 Command

    ~~~~~~~~~~         ~~~~~~~~~

    Sun Solaris:       /usr/sbin/mpstat

    HP:                /usr/sbin/sar -M 2 2

    IBM:               /usr/sbin/bindprocessor -q

    Digital:           /usr/sbin/psrinfo -v

    Linux:             xosview

6) To see the amount of swap space is on the machine and the usage:

    OS                 Command

    ~~~~~~~~~~         ~~~~~~~~~

    Sun Solaris:       /etc/swap -l and /etc/swap -s

    HP:                /etc/swapinfo -m ( must be root Unix id)

    IBM:               lsps -a

    Digital:           /usr/sbin/swapon -s

    Linux:             free -t

7) To see the current usage of shared memory & semephores:  "ipcs -b"