vmstat reports information about processes, memory, paging, block IO, traps, and cpu activity.

procs          memory                  swap      io      system       cpu
r b     swpd   free   buff  cache      si so    bi bo   in  cs us   sy id wa st
0 0    130668 103604 198144 5029000     0 0     1 68     8   6  0   0 100 0  0
0 0    130668 103604 198144 5029000     0 0     0 86    226 352 0   0 100 0  0
0 0    130668 103604 198148 5029000     0 0     0 58    223 357 0   0 100 0  0
0 0    130668 103604 198152 5029004     0 0     0 68    223 358 0   0 100 0  0
0 0    130668 103604 198152 5029004     0 0     0 56    223 357 0   0 100 0  0
0 0    130668 103604 198152 5029004     0 0     0 57    228 362 0   0 100 0  0

procs     memory             page             faults       cpu
r b w   avm    free    si so pi po fr de sr  in    sy    cs us sy id
1 0 0  112085 2189167  0  0  3  0  0  0  1  1033  32186 108  1  0  98
1 0 0  112085 2189074  0  0  4  0  0  0  0  1022   508   60  0  0 100

On Oracle Solaris systems, use the mpstat command to view statistics for each processor in a multiprocessor system. Each row of the table represents the activity of one processor. The first row summarizes all activity since the last system restart. Each subsequent row summarizes activity for the preceding interval. All values are events per second unless otherwise noted. The arguments are for time intervals between statistics and number of iterations.

The following example shows sample output from the mpstat command:

CPU minf mjf xcal  intr ithr  csw icsw migr smtx  srw syscl  usr sys  st idl
  0   3   0    2    319  103  109    0  19   11    0   73     0   1   0  99
  1   4   0    1    71   1     95    0  14   12    0   96     0   0   0  99

On Linux systems, use the top, free, and cat /proc/meminfo commands to view information about swap space, memory, and buffer usage.

The following sections describe tools available on IBM AIX on POWER Systems (64-bit):

• Base Operation System Tools

• Performance Toolbox

• System Management Interface Tool

The following performance analysis tools are available on HP-UX systems:

• GlancePlus/UX

  This HP-UX utility is an online diagnostic tool that measures the activities of the system. GlancePlus displays information about how system resources are used. It displays dynamic information about the system Input-Output, CPU, and memory usage on a series of screens. You can use the utility to monitor how individual processes are using resources.

• HP Programmer's Analysis Kit

  HP Programmer's Analysis Kit consists of the following tools:

  • Puma

    This tool collects performance statistics during a program run. It provides several graphical displays for viewing and analyzing the collected statistics.

  • Thread Trace Visualizer

    This tool displays trace files produced by the instrumented thread library, libpthread_tr.sl, in a graphical format. It enables you to view how threads are interacting and to find where threads are blocked waiting for resources.

    HP Programmer's Analysis Kit is bundled with the HP Fortran 77, HP Fortran 90, HP C, HP C++, HP ANSI C++, and HP Pascal compilers.

    The following table lists the performance tuning tools that you can use for additional performance tuning on HP-UX:

    Tool	Description
    caliper (Itanium only)	Collects run-time application data for system analysis tasks such as cache misses, translation look-aside buffer or instruction cycles, along with fast dynamic instrumentation. It is a dynamic performance measurement tool for C, C++, Fortran, and assembly applications
    gprof	Creates an execution profile for programs
    monitor	Monitors the program counter and calls to certain functions
    netfmt	Monitors the network
    netstat	Reports statistics on network performance
    nfsstat	Displays statistics about Network File System and Remote Procedure Call activity
    nettl	Captures network events or packets by logging and tracing
    prof	Creates an execution profile of C programs and displays performance statistics for the program, showing where the program is spending most of its execution time
    profil	Copies program counter information into a buffer
    top	Displays the top processes on the system and periodically updates the information

Try to minimize swapping because it causes significant operating system overhead. To check for swapping, use the sar or vmstat commands. For information about the appropriate options to use with these commands, refer to the man pages.

If the system is swapping and you must conserve memory, then:

• Avoid running unnecessary system daemon processes or application processes.

• Decrease the number of database buffers to free some memory.

• Decrease the number of operating system file buffers.

To determine the amount of swap space, run one of the following commands, depending on the platform:

Monitor the use of swap space, and increase it as required. The following table describes the initially recommended relationship between installed RAM and the configured swap space requirement:

To add swap space to the system, run one of the following commands, depending on the platform:

Paging may not present as serious a problem as swapping, because an entire program does not have to be stored in memory to run. A small number of page-outs may not noticeably affect the performance of the system.

To detect excessive paging, run measurements during periods of fast response or idle time to compare against measurements from periods of slow response.

Use the vmstat or sar command to monitor paging.

In Oracle Solaris, vmstat —p indicates the number of address translation page faults. Address translation faults occur when a process refers to a valid page not in memory.

Analyzing memory related issues should start with checking the amount of free memory in the vmstat output. If free memory is low, then the sr (scan rate) column should be checked for non-zero values. This indicates that the page scanner is scanning memory pages to put back on the free list to be reused.

Anonymous (bad) paging can be observed in the vmstat -p command output under the api (anonymous page-in) and apo (anonymous page-out) columns. This kind of paging takes place when the system moves anonymous pages to the swap device during a memory shortage.

If the system consistently has excessive page-out activity, then consider the following solutions:

• Install more memory.

• Move some work to another system.

• Configure the System Global Area (SGA) to use less memory.

During read operations, entire operating system blocks are read from the disk. If the database block size is smaller than the operating system file system block size, then Input-Output bandwidth is inefficient. If you set Oracle Database block size to be a multiple of the file system block size, then you can increase performance by up to 5 percent.

The DB_BLOCK_SIZE initialization parameter sets the database block size. However, to change the value of this parameter, you must re-create the database.

To see the current value of the DB_BLOCK_SIZE parameter, run the SHOW PARAMETER DB_BLOCK_SIZE command in SQL*Plus.

You can set parameters to automatically allocate memory based on the demands of workload and the requirements of various database instances running on the same system. The MEMORY_TARGET parameter specifies the Oracle systemwide usable memory for that instance and automatically tunes SGA and Process Global Area (PGA) components. The MEMORY_MAX_TARGET parameter identifies the value up to which the MEMORY_TARGET parameter can grow dynamically.

By default, the value for both these parameters is zero and there is no auto-tuning. You can activate auto-tuning by setting the MEMORY_TARGET parameter to a nonzero value. To dynamically enable the MEMORY_TARGET parameter, the MEMORY_MAX_TARGET parameter must be set at startup.

If you choose to use Automatic Storage Management for database storage, then all database Input-Output is balanced across all available disk devices in the Automatic Storage Management disk group.

By using Automatic Storage Management, you avoid manually tuning disk Input-Output.

Use the alter diskgroup disk online and alter diskgroup disk offline commands to temporarily suspend Input-Output to a set of disks. You can use these commands to perform regular maintenance tasks or upgrades such as disk firmware upgrade. If transient failures occur on some disks in a disk group, then use alter diskgroup disk online to quickly recover the disk group.

The System Resource Verifier utility (sysresv) is available with Oracle8i and later releases. It provides Oracle instance and operating system resource information for the Oracle system identifiers (ORACLE_SID) that you specify. This utility is located in $ORACLE_HOME/bin, but it can be used from other locations.

1. Calculate the minimum total semaphore requirements using the following formula:

   sum (process parameters of all database instances on the system) + overhead for oracle background processes + system and other application requirements
   

2. Set semmns (maximum number of semaphores allowed for the server) to this total.
3. Set semmsl (maximum number of semaphores in a semaphore set) to 250.
4. Set semmni (maximum number of semaphore sets) to semmns/semmsl rounded up to the nearest multiple of 1024.

   See Also:

   My Oracle Support note 226209.1, "Linux: How to Check Current Shared Memory, Semaphore Values," at the following URL:

   https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=226209.1

   The semaphore parameters semmns, semmsl, and semmni are obsolete on Oracle Solaris 10 and later. See My Oracle Support note 1006158.1:

   https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1006158.1

Shared memory uses common virtual memory resources across processes. Processes share virtual memory segments through a common set of virtual memory translation resources, for example, tables and cached entries, for improved performance.

Shared memory can be pinned to prevent paging and to reduce Input-Output overhead. To perform this, set the LOCK_SGA parameter to true. On IBM AIX on POWER Systems (64-bit) 5L, the same parameter activates the large page feature whenever the underlying hardware supports it.

Run the following command to make pinned memory available to Oracle Database:

$ /usr/sbin/vmo -r -o v_pinshm=1

Run a command similar to the following to set the maximum percentage of real memory available for pinned memory, where percent_of_real_memory is the maximum percent of real memory that you want to set:

$ /usr/sbin/vmo -r -o maxpin percent=percent_of_real_memory

When using the maxpin percent option, it is important that the amount of pinned memory exceeds the Oracle SGA size by at least 3 percent of the real memory on the system, enabling free pinnable memory for use by the kernel. For example, if you have 2 GB of physical memory and you want to pin the SGA by 400 MB (20 percent of the RAM), then run the following command:

$ /usr/sbin/vmo -r -o maxpin percent=23

Use the svmon command to monitor the use of pinned memory during the operation of the system. Oracle Database attempts to pin memory only if the LOCK_SGA parameter is set to true. If the SGA size exceeds the size of memory available for pinning, then the portion of the SGA exceeding these sizes is allocated to ordinary shared memory.

$ /usr/sbin/vmo -r -o lgpg_regions=10 -o lgpg_size=16777216

$ vmstat -P all