8 Tuning for Oracle Database on UNIX

Common Tools

The following sections provide information on common tools:

• vmstat

• sar

• iostat

• swap, swapinfo, swapon, or lsps

vmstat

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

• HP-UX and Solaris:

  $ vmstat -S 5 6
  
  

• AIX, Linux, and Tru64 UNIX:

  $ vmstat 5 6
  
  

The following example shows output from the command on Solaris:

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 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, swapping is occurring and your 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 lots of available memory.

sar

Use the sar (system activity reporter) command to display cumulative activity counters in the operating system, depending on the switches that you supply with the command. The following command displays a summary of I/O activity ten times, at ten-second intervals:

$ sar -b 10 10

The following example shows output from the command on Solaris:

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 I/O activity at a point in time. If you specify the interval time with more than one option, the output can become difficult to read. If you specify interval time of less than 5, the sar activity itself can affect the output. For more information about sar, refer to the UNIX man pages of your operating system.

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. This information is valuable when you need to balance I/O loads.

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

$ iostat 5 5

The following example shows output from 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 I/O requests on a particular disk device must wait to be serviced. Request queues are caused by a high volume of I/O requests to that disk or by I/O with long average seek times. Ideally, disk request queues should be at or near zero.

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

AIX Tools

The following sections describe tools available on AIX systems.

HP-UX Tools

The following sections describe tools available on HP-UX systems.

Linux Tools

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

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 reboot; 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

Allocate Sufficient Swap Space

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

If your system is swapping and you must conserve memory:

• Avoid running unnecessary system daemon processes or application processes.

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

• Decrease the number of UNIX file buffers, especially if you are using raw devices.

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

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

Set the swap space to between two and four times the system’s physical memory. Monitor the use of swap space and increase it as required.

Control Paging

Paging might 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 might not noticeably affect the performance of your 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. See the man pages or your operating system documentation for information about interpreting the results for your platform. The following columns from the output of these commands are important on Solaris:

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

• Install more memory.

• Move some of the work to another system.

• Configure the SGA to use less memory.

Adjust Oracle Block Size

A UNIX system reads entire operating system blocks from the disk. If the database block size is smaller than the UNIX file system block size, I/O bandwidth is inefficient. If you set the Oracle database block size to be a multiple of the file system blocksize, you can increase performance by up to five percent.

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

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

Use Automatic Storage Management

If you choose to use Automatic Storage Management for database storage, all database I/O is balanced across all available disk devices in the ASM disk group. ASM provides the performance of raw device I/O without the inconvenience of managing raw devices.

By using ASM, you avoid the need to manually tune disk I/O.

Choose the Appropriate File System Type

Depending on your operating system, you can choose from a range of file system types. Each file system type has different characteristics which can have a substantial impact on database performance. The following table lists common file system types available on UNIX platforms:

The suitability of a file system to an application is usually not documented. For example, even different implementations of the Unified file system are hard to compare. Performance differences can vary from 0 to 20 percent, depending on the file system you choose. If you choose to use a file system:

• 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 I/O as evenly as possible.

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

Determine the Size of the SGA

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

• Enter 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.

• Determine the size of the SGA when you start your database instance. The SGA size is displayed next to the heading Total System Global Area.

• Enter the ipcs command as the oracle user.

Shared Memory on AIX

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.With Oracle Database on AIX, shared memory can be pinned to prevent paging and to reduce I/O overhead. To do 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.Enter the following command to make pinned memory available to Oracle Database on AIX systems:

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

Enter 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_of_real_memory

When using the maxpin% 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, allowing 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 enter the following command:

$ /usr/sbin/vmo -r -o maxpin%=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.

Large Page Feature on AIX POWER4-Based Systems

To turn on and reserve IO large pages each of size 16 MB on a POWER4 system, enter the following command:

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

This command will propose bosboot and warn that a reboot is required for the changes to take affect.

Oracle recommends specifying enough large pages to contain the entire SGA. The Oracle instance attempts to allocate large pages when the LOCK_SGA parameter is set to TRUE. If the SGA size exceeds the size of memory available for pinning, or large pages, the portion of the SGA exceeding these sizes is allocated to ordinary shared memory.