Overview of Unix Resources

In this Document

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

This note provides information about administering Oracle Database on UNIX-based operating systems. You must set Oracle Database environment variables, parameters, and user settings for Oracle Database to work.

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

5       Environment Variables

To display the current value of an environment variable, use the env command. For example, to display the value of the ORACLE_SID environment variable, run the following command:
$ env | grep ORACLE_SID

To display the current value of all environment variables, run the env command as follows:
$ env | more

1. Oracle Database Environment Variables

Below are some environment variables used with Oracle Database and their descriptions:

NLS_LANG: Specifies the language, territory, and character set of the client environment. The client character set specified by NLS_LANG must match the character set of the terminal or terminal emulator.

ORA_NLS10: Specifies the directory where the language, territory, character set, and linguistic definition files are stored.

ORA_TZFILE: Specifies the full path and file name of the time zone file. The Oracle Database Server always uses the large time zone file ($ORACLE_HOME/oracore/zoneinfo/timezlrg_number.dat).

ORACLE_BASE: Specifies the base of the Oracle directory structure for Optimal Flexible Architecture compliant installations.
 
ORACLE_HOME: Specifies the directory containing the Oracle software.
 
ORACLE_PATH: Specifies the search path for files used by Oracle applications such as SQL*Plus. If the full path to the file is not specified, or if the file is not in the current directory, then the Oracle application uses ORACLE_PATH to locate the file.
 
ORACLE_SID: Specifies the Oracle system identifier.
 
ORACLE_TRACE: Enables the tracing of shell scripts during an installation.

ORAENV_ASK: Controls whether the oraenv or coraenv script prompts or does not prompt for the value of the ORACLE_SID environment variable.
 
SQLPATH: Specifies the directory or list of directories that SQL*Plus searches for a login.sql file.
 
TNS_ADMIN: Specifies the directory containing the Oracle Net Services configuration files.

TWO_TASK: Specifies the default connect identifier to use in the connect string. If this environment variable is set, then do not specify the connect identifier in the connect string.

NLS_OS_CHARSET: Specifies the Oracle character set name corresponding to the UNIX locale character set in which the file names and user names are encoded by the operating system.

2. UNIX Environment Variables

Below are some of the UNIX environment variables used with Oracle Database:

HOME: The home directory of the user.

LD_LIBRARY_PATH: Environment variable to specify the path used to search for libraries on UNIX and Linux. The environment variable may have a different name on some operating systems, such as LIBPATH on AIX, and SHLIB_PATH on HP-UX.

PATH: Used by the shell to locate executable programs; must include the $ORACLE_HOME/bin directory.
 
TEMP, TMP, and TMPDIR: Specifies the default directories for temporary files; if set, tools that create temporary files create them in one of these directories.

6       Memory Requirements

The following are the memory requirements for installing Oracle Database 11g Release 2 (11.2):
Minimum: 1 GB of RAM
Recommended: 2 GB of RAM or more

To determine the RAM size, enter the following command:

# grep MemTotal /proc/meminfo

 If the size of the RAM is less than the required size, then you must install more memory before continuing.

On Linux, the HugePages feature allocates non-swappable memory for large page tables using memory-mapped files. If you enable HugePages, then you should deduct the memory allocated to HugePages from the available RAM before calculating swap space.

7       Configuring Swap Space

Swap space holds the virtual memory of the system. Every program you run occupies a certain amount of virtual memory. Once all your virtual memory has been allocated to specific applications you cannot start new programs and currently running programs may fail if they try to grow. Physical memory is what you know as the RAM of the system. If you use up all the RAM, your system may run more slowly, but you can still start more programs because the swap space absorbs excess data. Oracle does not access nor use swap space directly. If it is used indirectly, it would be provided to Oracle as "virtual memory" by the Linux / UNIX OS, as a consequence of the Linux / UNIX system running out of RAM memory.

Oracle's Recommendations for swap size on Linux / UNIX systems are best summarized in:
Note 169706.1 Oracle Database on Unix AIX,HP-UX,Linux,Mac OS X,Solaris, Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

To see the amount of swap space on the machine and its usage, check the following:

• 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

8       Configuring Huge Pages

HugePages is a feature integrated into the Linux kernel with release 2.6. This feature basically provides the alternative to the 4K page size (16K for IA64) providing bigger pages. 

Some HugePages Facts/Features

• HugePages can be allocated on-the-fly but they must be reserved during system startup.
  Otherwise the allocation might fail as the memory is already paged in 4K mostly.
• HugePage sizes vary from 2MB to 256MB based on kernel version and HW architecture
• HugePages are not subject to reservation /  release after the system startup unless there is system administrator intervention, basically changing the hugepages configuration (i.e. number of pages available or pool size)

Advantages of HugePages Over Normal Sharing Or AMM (see below)

• Not swappable: HugePages are not swappable. Therefore there is no page-in/page-out mechanism overhead.HugePages are universally regarded as pinned.
• Relief of TLB pressure:
• HugePages will let less translations to be loaded into the TLB when purge the TLB
• TLB entries will cover a larger part of the address space when use HugePages, there will be fewer TLB misses before the entire or most of the SGA is mapped in the SGA
• Fewer TLB entries for the SGA also means more for other parts of the address space
• Decreased page table overhead: Each page table entry can be as large as 64 bytes and if we are trying to handle 50GB of RAM, the pagetable will be approximately 800MB in size which is practically will not fit in 880MB size lowmem (in 2.4 kernels - the page table is not necessarily in lowmem in 2.6 kernels) considering the other uses of lowmem. When 95% of memory is accessed via 256MB hugepages, this can work with a page table of approximately 40MB in total. See also Document 361468.1.
• Eliminated page table lookup overhead: Since the pages are not subject to replacement, page table lookups are not required.
• Faster overall memory performance: On virtual memory systems each memory operation is actually two abstract memory operations. Since there are less number of pages to work on, the possible bottleneck on page table access is clearly avoided.

Size of a HugePage

The size of a single HugePage varies according to:

• Kernel version/linux distribution
• HW Platform

The actual size of the HugePage on a specific system can be checked by:

$ grep Hugepagesize /proc/meminfo

9       Configuring Kernel Parameters

For the Oracle Database to work appropriately, we need to ensure that the kernel parameters are set to values greater than or equal to the minimum value shown below.

The kernel parameter and shell limit values in this section are minimum values only. For production database systems, Oracle recommends that you tune these values to optimize the performance of the system. Refer to your operating system documentation for more information about tuning kernel parameters.

If the current value of any parameter is higher than the value listed in this table, then do not change the value of that parameter.

To view the current value specified for these kernel parameters and to change them if necessary, enter commands similar to the following to view the current values of the kernel parameters:

If the value of any kernel parameter is different from the recommended value, then using any text editor, create or edit the /etc/sysctl.conf file, and add or edit lines similar to the following:
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
After updating the values of kernel parameters in the /etc/sysctl.conf file, either restart the computer, or run the command sysctl -p to make the changes in the /etc/sysctl.conf file available in the active kernel memory.

For more information, please visit Oracle 11g Documentation guide, found via the following link:

The table in Document:144638.1 mentions the Unix and Linux kernel parameters that should be monitored and possibly increased after changes are made to the related init.ora parameter. Please check with your Operating System documentation for specific details on the parameter changes

10    Configuring Resource Limits

For each installation software owner, check the resource limits for installation

To check resource limits:

1. Log in as an installation owner.
2. Check the soft and hard limits for the file descriptor setting. Ensure that the result is in the recommended range. For example:

$ ulimit -Sn
4096
$ ulimit -Hn
65536

1.  Check the soft and hard limits for the number of processes available to a user. Ensure that the result is in the recommended range. For example:

$ ulimit -Su
2047
$ ulimit -Hu
16384

1. Check the soft limit for the stack setting. Ensure that the result is in the recommended range. For example:

$ ulimit -Ss
10240
$ ulimit -Hs
32768

1.  Repeat this procedure for each Oracle software installation owner.

If necessary, update the resource limits in the /etc/security/limits.conf configuration file for the installation owner. For example, add the following lines to the /etc/security/limits.conf file:

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  1024
oracle              hard    nofile  65536
oracle              soft    stack   10240

When the limits.conf file is changed, these changes take effect immediately. However, if the grid or oracle users are logged in, then these changes do not take effect until you log these users out and log them back in. You must do this before you attempt to use these accounts to install.

11    CPU Utilization

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%.  You can monitor the CPU utilization with the following commands:

Sun Solaris: sar -u
IBM: ps av (or) iostat 3 20
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.

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

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

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

Sun Solaris: /usr/sbin/mpstat
HP: /usr/sbin/sar -M 2 2
IBM: /usr/sbin/bindprocessor -q
Linux: xosview

12    I/O Utilization

You should regularly monitor disk IO statistics by using utilities such as:

"sar -d" or "iostat"

Average service times of 50ms or less are 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.