8 Tuning Oracle Database

8.2.1 vmstat

Use the vmstat command to view process, virtual memory, disk, trap, and CPU activity, depending on the switches that you supply with the command. Run one of the following commands to display a summary of CPU activity six times, at five-second intervals:

• On HP-UX and Solaris:

  $ vmstat -S 5 6
  

• On AIX and Linux:

  $ vmstat 5 6
  

The following is sample output of this command on HP-UX:

procs     memory            page            disk          faults      cpu
 r b w   swap  free  si  so pi po fr de sr f0 s0 s1 s3   in   sy   cs us sy id
 0 0 0   1892  5864   0   0  0  0  0  0  0  0  0  0  0   90   74   24  0  0 99
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  0   46   25   21  0  0 100
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  0   47   20   18  0  0 100
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  2   53   22   20  0  0 100
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  0   87   23   21  0  0 100
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  0   48   41   23  0  0 100

The w sub column, under the procs column, shows the number of potential processes that have been swapped out and written to disk. If the value is not zero, then swapping occurs and the system is short of memory.

The si and so columns under the page column indicate the number of swap-ins and swap-outs per second, respectively. Swap-ins and swap-outs should always be zero.

The sr column under the page column indicates the scan rate. High scan rates are caused by a shortage of available memory.

The pi and po columns under the page column indicate the number of page-ins and page-outs per second, respectively. It is normal for the number of page-ins and page-outs to increase. Some paging always occurs even on systems with sufficient available memory.

8.2.2 sar

Depending on the switches that you supply with the command, use the sar (system activity reporter) command to display cumulative activity counters in the operating system.

On an HP-UX system, the following command displays a summary of Input-Output activity every ten seconds:

$ sar -b 10 10

The following example shows the output of this command:

13:32:45 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
13:32:55       0      14     100       3      10      69       0       0
13:33:05       0      12     100       4       4       5       0       0
13:33:15       0       1     100       0       0       0       0       0
13:33:25       0       1     100       0       0       0       0       0
13:33:35       0      17     100       5       6       7       0       0
13:33:45       0       1     100       0       0       0       0       0
13:33:55       0       9     100       2       8      80       0       0
13:34:05       0      10     100       4       4       5       0       0
13:34:15       0       7     100       2       2       0       0       0
13:34:25       0       0     100       0       0     100       0       0

Average        0       7     100       2       4      41       0       0

The sar output provides a snapshot of system Input-Output activity at a given point in time. If you specify the interval time with multiple options, then the output can become difficult to read. If you specify an interval time of less than 5, then the sar activity itself can affect the output.

8.2.3 iostat

Use the iostat command to view terminal and disk activity, depending on the switches that you supply with the command. The output from the iostat command does not include disk request queues, but it shows which disks are busy. You can use this information to balance the Input-Output loads.

The following command displays terminal and disk activity five times, at five-second intervals:

$ iostat 5 5

The following is sample output of the command on Solaris:

tty          fd0           sd0           sd1           sd3          cpu
 tin tout Kps tps serv  Kps tps serv  Kps tps serv  Kps tps serv  us sy wt id
   0    1   0   0    0    0   0   31    0   0   18    3   0   42   0  0  0 99
   0   16   0   0    0    0   0    0    0   0    0    1   0   14   0  0  0 100
   0   16   0   0    0    0   0    0    0   0    0    0   0    0   0  0  0 100
   0   16   0   0    0    0   0    0    0   0    0    0   0    0   0  0  0 100
   0   16   0   0    0    0   0    0    2   0   14   12   2   47   0  0  1 98

Use the iostat command to look for large disk request queues. A request queue shows how long the Input-Output requests on a particular disk device must wait to be serviced. Request queues are caused by a high volume of Input-Output requests to that disk or by Input-Output with long average seek times. Ideally, disk request queues should be at or near zero.

8.2.4 swap, swapinfo, swapon, or lsps

Use the swap, swapinfo, swapon, or lsps command to report information about swap space usage. A shortage of swap space can stop processes responding, leading to process failures with Out of Memory errors. The following table lists the appropriate command to use for each platform:

The following example shows sample output from the swap -l command on Solaris:

swapfile             dev        swaplo blocks        free  
/dev/dsk/c0t3d0s1    32,25      8      197592        162136

8.2.5 AIX Tools

The following sections describe tools available on AIX systems:

• Base Operation System Tools

• Performance Toolbox

• System Management Interface Tool

8.2.6 HP-UX Tools

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:

8.2.7 Linux Tools

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

8.2.8 Solaris Tools

On 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  wt idl
  0    0   0    1    71   21   23    0    0    0    0    55    0   0   0  99
  2    0   0    1    71   21   22    0    0    0    0    54    0   0   0  99
CPU minf mjf xcal  intr ithr  csw icsw migr smtx  srw syscl  usr sys  wt idl
  0    0   0    0    61   16   25    0    0    0    0    57    0   0   0 100
  2    1   0    0    72   16   24    0    0    0    0    59    0   0   0 100

8.3.1 Allocating Sufficient Swap Space

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:

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

Set the swap space to the level listed for your platform in the respective platform-specific installation guide, based on your physical RAM and memory features used.

8.3.2 Controlling Paging

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.

The following table lists the important columns from the output of these commands:

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.

8.3.3 Adjusting Oracle Block Size

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.

8.3.4 Allocating Memory Resource

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.

8.4.1 Using Automatic Storage Management

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.

8.4.2 Choosing the Appropriate File System Type

Depending on the operating system, you can choose from a range of file system types. Each file system type has different characteristics. This fact can have a substantial impact on database performance. The following table lists common file system types:

The suitability of a file system for an application is usually not documented. For example, even different implementations of the Unified file system are hard to compare. Depending on the file system that you choose, performance differences can be up to 20 percent. If you choose to use a file system, then:

• Make a new file system partition to ensure that the hard disk is clean and unfragmented.

• Perform a file system check on the partition before using it for database files.

• Distribute disk Input-Output as evenly as possible.

• If you are not using a logical volume manager or a RAID device, then consider placing log files on a different file system from data files.

8.5.1 Monitoring Disk Performance on Other Operating Systems

To monitor disk performance, use the sar -b and sar -u commands.

The following table describes the columns of the sar -b command output that are significant for analyzing disk performance:

An important sar -u column for analyzing disk performance is %wio, the percentage of CPU time spent waiting on blocked Input-Output.

Key indicators are:

• The sum of the bread, bwrit, pread, and pwrit column values indicates the level of activity of the disk Input-Output subsystem. The higher the sum, the busier the Input-Output subsystem. The larger the number of physical drives, the higher the sum threshold number can be. A good default value is no more than 40 for 2 drives and no more than 60 for 4 to 8 drives.

• The %rcache column value should be greater than 90 and the %wcache column value should be greater than 60. Otherwise, the system may be disk Input-Output bound.

• If the %wio column value is consistently greater than 20, then the system is Input-Output bound.

8.5.2 Using Disk Resync to Monitor Automatic Storage Management Disk Group

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.

8.6.1 Determining the Size of the SGA

You can determine the SGA size in one of the following ways:

• Run the following SQL*Plus command to display the size of the SGA for a running database:

  SQL> SHOW SGA
  

  The result is shown in bytes.

• When you start the database instance, the size of the SGA is displayed next to the Total System Global Area heading.

• Run the ipcs command as the oracle user.

8.6.2 System Resource Verifier Utility

The System Resource Verifier utility (sysresv) is available with Oracle Database 8i 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.

sysresv [-i] [-f] [-d on|off] [-l sid1[ sid2 ...]]

$ sysresv
IPV Resources for ORACLE_SID "sales" :
Shared Memory:
ID                            KEY
10345                    0x51c051ad
Semaphores
ID
10345                    0x51c051ad
Oracle Instance alive for sid "sales"

8.6.3 Shared Memory on AIX

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 AIX 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.

Large Page Feature on AIX POWER4- and POWER5-Based Systems

To turn on and reserve 10 large pages each of size 16 MB on a POWER4 or POWER 5 system, run the following command:

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

This command proposes bosboot and warns that a restart is required for the changes to take affect.

Oracle recommends specifying enough large pages to contain the entire SGA. The Oracle database instance attempts to allocate large pages when the LOCK_SGA parameter is set to true.

The 16 MB pages are always pinned, and cannot be used for standard memory. If a pool of 16MB size pages is configured, then this memory will be unusable for allocation of standard memory even if no other application is currently using large pages.

The POWER5 based systems support 64 K pages. Oracle uses them for SGA if they are available. These 64K pages do not require any additional configuration and do not depend on LOCK_SGA parameter setting.

To monitor use of large pages, use the following command:

$ vmstat -P all

For the AIX operating system to use 16MB pages, or pinned memory when allocating shared memory, the Oracle user ID must have CAP_BYPASS_RAC_VMM and CAP_PROPAGATE capabilities. User ID that is used to start the database instance must also have the same capabilities. In particular, when using large pages on an Oracle Real Application Cluster (Oracle RAC) database, where the srvctl command is used to start and stop the Oracle RAC database instances, it is also necessary to set the CAP_BYPASS_RAC_VMM and CAP_PROPAGATE capabilities for the root user.

Capabilities can be set and examined using the following commands:

• Run the following command to check the current capabilities:

  $ lsuser –a capabilities oracle
  

• Add the CAP_BYPASS_RAC_VMM and CAP_PROPAGATE capabilities to this user ID:

  $ chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle