Operating System Tools
Several operating system tools are available to help you assess database performance and determine database requirements. In addition to providing statistics for Oracle processes, these tools provide statistics for CPU usage, interrupts, swapping, paging, context switching, and I/O for the entire system.
The following sections provide information on common tools:
- swap, swapinfo, swapon, or lsps
For more information about these tools, see the operating system documentation and UNIX man pages.
On Mac OS X, the
vmstat command to view process, virtual memory, disk, trap, and CPU activity, depending on the switches that 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:
- AIX, Linux, and Tru64 UNIX:
· $ vmstat -S 5 6
· $ vmstat 5 6
The following example shows output from the command on Solaris SPARC:
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
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
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
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.
|Note: The output from the
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. On a Solaris system, 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
|Note: On Tru64 UNIX systems, 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 an interval time of less than 5, the
sar activity itself can affect the output. For more information about
sar, refer to the man page. 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
iostatcommand 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
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
|Note: See the “Determining Available and Used Swap Space” section for more information about swap space on Mac OS X systems.|
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:
swap -l and
The following example shows sample output from the
swap -lcommand on Solaris:
swapfile dev swaplo blocks free
/dev/dsk/c0t3d0s1 32,25 8 197592 162136
The following sections describe tools available on AIX systems.
|See Also: For more information about these tools, see the AIX operating system documentation and man pages.|
AIX System Management Interface Tool
The AIX System Management Interface Tool (SMIT) provides a menu-driven interface to various system administrative and performance tools. Using SMIT, you can navigate through large numbers of tools and focus on the jobs that you want to perform.
Base Operation System Tools
The AIX Base Operation System (BOS) contains performance tools that are historically part of UNIX systems or are required to manage the implementation-specific features of AIX. The following table lists the most important BOS tools:
Displays the attributes of devices
Displays information about a logical volume or the logical volume allocations of a physical volume
Displays the contents of network-related data structures
Displays statistics about Network File System (NFS) and Remote Procedure Call (RPC) activity
Changes the initial priority of a process
Displays or sets network options
Displays the status of one or more processes
Reorganizes the physical-partition allocation within a volume group
Displays the elapsed execution, user CPU processing, and system CPU processing time
Records and reports selected system events
Manages Virtual Memory Manager tunable parameters
AIX Performance Toolbox
The AIX Performance Toolbox (PTX) contains tools for monitoring and tuning system activity locally and remotely. PTX consists of two main components, the PTX Manager and the PTX Agent. The PTX Manager collects and displays data from various systems in the configuration by using the
xmperf utility. The PTX Agent collects and transmits data to the PTX Manager by using the
xmserd daemon. The PTX Agent is also available as a separate product called Performance Aide for AIX. Both PTX and Performance Aide include the following monitoring and tuning tools:
Optimizes an executable program for a particular workload
Uses the trace facility to monitor and report the activity of the file system
Displays the placement of a file’s blocks within logical or physical volumes
Displays statistics about contention for kernel locks
Facilitates interactive placement of logical volumes within a volume group
Uses the trace facility to report on network I/O and network-related CPU usage
Simulates systems with various memory sizes for performance testing
Captures and analyzes information about virtual-memory usage
Records and counts system calls
Uses the trace facility to report CPU usage at module and source-code-statement levels
Reports the memory access patterns of processes
Permits subroutine-level entry and exit instrumentation of existing executables
|See Also: For more information about these tools, see the Performance Toolbox for AIX Guide and Reference, and for more information about the syntax of some of these tools, see the AIX 5L Performance Management Guide.|
The following sections describe tools available on HP-UX systems.
Performance Tuning Tools
The following table lists the tools that you can use for additional performance tuning on HP-UX:
|See Also: For more information about these tools, see the HP-UX operating system documentation and man pages.|
Collects run-time application data for system analysis tasks such as cache misses, translation look-aside buffer (TLB) or instruction cycles, along with fast dynamic instrumentation. It is a dynamic performance measurement tool for C, C++, Fortran, and assembly applications.
Creates an execution profile for programs.
Monitors the program counter and calls to certain functions.
Monitors the network.
Reports statistics on network performance.
Displays statistics about Network File System (NFS) and Remote Procedure Call (RPC) activity.
Captures network events or packets by logging and tracing.
Creates an execution profile of C programs and displays performance statistics for your program, showing where your program is spending most of its execution time.
Copies program counter information into a buffer.
Displays the top processes on the system and periodically updates the information.
HP-UX Performance Analysis Tools
The following HP-UX performance analysis tools are also available on HP-UX systems:
- HP PAK
GlancePlus/UX This HP-UX utility is an online diagnostic tool that measures the system’s activities. GlancePlus displays how system resources are being used. It displays dynamic information about the system’s I/O, CPU, and memory usage in a series of screens. You can also use the utility to monitor how individual processes are using resources. HP PAK HP Programmer’s Analysis Kit (HP PAK) currently consists of two tools, Puma and Thread Trace Visualizer (TTV):
- Puma collects performance statistics during a program run. It provides several graphical displays for viewing and analyzing the collected statistics.
- TTV displays trace files produced by the instrumented thread library,
libpthread_tr.sl, in a graphical format. It allows you to view how threads are interacting and to find where threads are blocked waiting for resources.
HP PAK is bundled with the HP Fortran 77, HP Fortran 90, HP C, HP C++, HP ANSI C++, and HP Pascal compilers.
On Linux systems, use the
cat /proc/meminfo command to view information about swap space, memory, and buffer usage.
Mac OS X Tools
On Mac OS X systems, you can use the following additional performance tuning tools:
- Use the
topcommand to display information about running processes and memory usage.
- Use the Apple Computer Hardware Understanding Developer (CHUD) tools, such as Shark and BigTop, to monitor system activity and tune applications.
For more information about the CHUD tools, see the following Web site:
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
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
Tuning Memory Management
Start the memory tuning process by measuring paging and swapping space to determine how much memory is available. After you have determined your system memory usage, tune the Oracle buffer cache.
The Oracle buffer manager ensures that the more frequently accessed data is cached longer. If you monitor the buffer manager and tune the buffer cache, you can have a significant influence on Oracle Database performance. The optimal Oracle Database buffer size for your system depends on the overall system load and the relative priority of Oracle over other applications.
Allocate Sufficient Swap Space
Try to minimize swapping because it causes significant UNIX overhead. To check for swapping, use the
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.
On Mac OS X systems, swap space is allocated dynamically. If the operating system requires more swap space, it creates additional swap files in the
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.
For more information about these commands, see your operating system documentation.
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.
vm_stat on Mac OS X) 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:
||Indicates the number of address translation page faults. Address translation faults occur when a process refers to a valid page not in memory.|
||Indicates the number of valid pages that have been reclaimed and added to the free list by page-out activity. This value should be zero.|
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 block size, 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.
Tuning Disk I/O
Balance I/O evenly across all available disks to reduce disk access times. For smaller databases and those not using RAID, ensure that different data files and tablespaces are distributed across the available disks.
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:
|S5||AIX, HP-UX, Solaris||UNIX System V file system|
|UFS||AIX, HP-UX, Mac OS X, Solaris, Tru64 UNIX||Unified file system, derived from BSD UNIX
Note: On Mac OS X, Oracle does not recommend the use of the UFS file system for either software or database files.
|VxFS||AIX, Solaris, HP-UX||VERITAS file system|
|None||All||Raw devices (no file system)|
|ext2/ext3||Linux||Extended file system for Linux|
|AdvFS||Tru64 UNIX||Advanced file system|
|CFS||Tru64 UNIX||Cluster file system|
|JFS/JFS2||AIX||Journaled file system|
|HFS Plus, HFSX||Mac OS X||HFS Plus is the standard hierarchical file system used by Mac OS X. HFSX is an extension to HFS Plus that enables case-sensitive file names.|
|GPFS||AIX||General parallel file system|
|OCFS||Linux||Oracle Cluster file system|
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 that 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.
Monitoring Disk Performance
The following sections describe how to monitor disk performance.
Monitoring Disk Performance on Mac OS X
sar commands to monitor disk performance on Mac OS X systems. For more information about using these commands, see the man pages.
Monitoring Disk Performance on Other Operating Systems
To monitor disk performance, use the
sar -b and
sar -u commands.
Table 8-1 describes the columns of the
sar -b command output that are significant for analyzing disk performance.
Table 8-1 sar -b Output Columns
||Blocks read and blocks written per second (important for file system databases)|
||Partitions read and partitions written per second (important for raw partition database systems)|
sar -u column for analyzing disk performance is
%wio, the percentage of CPU time waiting on blocked I/O.
Not all Linux distributions display the
Key indicators are:
- The sum of the
pwritcolumns indicates the level of activity of the disk I/O subsystem. The higher the sum, the busier the I/O 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 two drives and no more than 60 for four to eight drives.
%rcachecolumn value should be greater than 90 and the
%wcachecolumn value should be greater than 60. Otherwise, the system may be disk I/O bound.
- If the
%wiocolumn value is consistently greater than 20, the system is I/O bound.
System Global Area
The System Global Area (SGA) is the Oracle structure that is located in shared memory. It contains static data structures, locks, and data buffers. Sufficient shared memory must be available to each Oracle process to address the entire SGA.
The maximum size of a single shared memory segment is specified by the shmmax kernel parameter (shm_max on Tru64 UNIX). The following table shows the recommended value for this parameter, depending on your platform:
|HP-UX||The size of the physical memory installed on the system.
See Also: HP-UX Shared Memory Segments for an Oracle Instance for information about the shmmax parameter on HP-UX.
|Linux||Half the size of the physical memory installed on the system.|
|Mac OS X||Not applicable. The largest SGA size on Mac OS X is 1000 MB|
|Solaris||4294967295 or 4 GB minus 16 MB. Can be greater than 4 GB on 64-bit systems.|
|Tru64 UNIX||4294967295 or 4 GB minus 16 MB.
Note: The value of the shm_max parameter must be at least 16 MB for the Oracle instance to start. If your system runs both Oracle9i and Oracle Database 10g instances, you must set the value of this parameter to 2 GB minus 16 MB.
If the size of the SGA exceeds the maximum size of a shared memory segment (shmmax or shm_max), Oracle Database 10g attempts to attach more contiguous segments to fulfill the requested SGA size. The shmseg kernel parameter (shm_seg on Tru64 UNIX) specifies the maximum number of segments that can be attached by any process. Set the following initialization parameters to control the size of the SGA:
Alternatively, set the SGA_TARGET initialization parameter to enable Oracle to automatically tune the SGA size.
Use caution when setting values for these parameters. When values are set too high, too much of the system’s physical memory is devoted to shared memory, resulting in poor performance.
Oracle databases configured with Shared Server require a higher setting for the SHARED_POOL_SIZE initialization parameter, or a custom configuration that uses the LARGE_POOL_SIZE initialization parameter. If you installed the database with the Oracle Universal Installer, then the value of the SHARED_POOL_SIZE parameter is set automatically by Database Configuration Assistant. However, if you created a database manually, increase the value of the SHARED_POOL_SIZE parameter in the parameter file by 1 KB for each concurrent user.
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.
- On systems other than Mac OS X, enter the
ipcscommand as the
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
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 10 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 proposes bosboot and warns 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.
For more information about enabling and tuning pinned memory and large pages, see the AIX documentation.
Tuning the Operating System Buffer Cache
To take full advantage of raw devices, adjust the size of the Oracle Database buffer cache and, if memory is limited, the operating system buffer cache.
The operating system buffer cache holds blocks of data in memory while they are being transferred from memory to disk, or from disk to memory.
The Oracle Database buffer cache is the area in memory that stores the Oracle database buffers. Because Oracle Database can use raw devices, it does not need to use the operating system buffer cache.
If you use raw devices, increase the size of the Oracle Database buffer cache. If the amount of memory on the system is limited, make a corresponding decrease in the operating system buffer cache size.
sar command to determine which buffer caches you must increase or decrease. For more information about the
sar command, see the man page.
For Tru64 UNIX, do not reduce the operating system buffer cache, because the operating system automatically resizes the amount of memory that it requires for buffering file system I/O. Restricting the operating system buffer cache can cause performance issues.