Process Global Area (PGA)

OPEN_LINKS. The number of database links allowed open per user.

DB_FILES. The number of database files allowed for the database (up to the value of MAX_DATAFILES).

LOG_FILES. The maximum number of redo log file groups (up to the value of MAX_LOGFILES).

The PGA also contains session-related information if MTS is not used. In environments where MTS is used, the session information is placed in the SHARED_POOL region of the SGA if the LARGE POOL (in Oracle8,8i and 9i) is not configured. This session information consists of the user’s private SQL area and other session-specific data. The PGA will always hold the user’s stack information. The section of the shared or LARGE POOL allocated for the user is called the UGA, which stands for Users Global Area.

Another contributor to the memory footprint of each process is the size of the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE parameters. When a process executes a sort memory, the size of SORT_AREA_SIZE is allocated to the user. If SORT_AREA_RETAINED_SIZE is also set, then this amount of memory is reduced to that value and allowed to grow to SORT_AREA_SIZE. If the sort requires more space than is specified in SORT_AREA_SIZE, the sort is broken into SORT_AREA_SIZED chunks, which are swapped out to disk as needed.

Some System-Specific Notes

Each specific operating system contains certain items that a DBA needs to be aware of and take into account when installing Oracle. This section attempts to consolidate these system-specific notes in one location.

UNIX-Specific Notes

There are many different versions of UNIX, including Solaris, AIX, SV4, and the new kid on the block, Linux (while Linux has been around since the early 90's it is just now coming to the fore in business uses, hence the new kid label.) This subsection provides general guidelines and some specific suggestions for those systems I have experience with.

Datafiles 

On the install for the general-use database, the MAXDATAFILES parameter defaults to a value of 100. On older versions of UNIX there may be a kernel-based limit of 60 open files per process. This can be overcome by altering the OPEN_MAX value in the limits.h file. Under some versions of UNIX this may be different; to make the determination, look under configurable kernel parameters. Another UNIX-specific limit is on the total number of file extents.

System Global Area (SGA) 

In some cases, the SGA may exceed the available shared memory segment size; if this occurs, the UNIX administrator must relink or reconfigure the kernel to allow larger programs. There are system-specific shared memory parameters that control the maximum size of the SGA. These should be reviewed under configurable kernel parameters for your version of UNIX. The installation guide for your Oracle system will delineate which parameters to look at for your UNIX system. Under the HP-UX implementation the size of the SGA is limited to the size of swap space on the available disk drives. On a Sun, True64, or HP-UX system, the parameters that control how the SGA grows are SHMMAX, the size of a shared memory area, and SHMSEG, the number of shared memory areas a process can access. On SuSE7.2 and RedHat Linux you can dynamically set the memory and semaphore processes or load them into the configuration header files and relink the kernel. On systems such as NT and AIX, you have no control over memory and semaphore parameters, as they are automatically set for you.

Rollback Segments 

Most systems are not using the parallel instance option of Oracle (called real application clusters, or RAC, in Oracle9i.) Because of this, private, rather than public, rollback segments should be used. This will allow a single rollback segment to be taken offline for consolidation. If you will have large transactions, such as batch updates with numerous updates and adds between commits, a second INIT.ORA file should be created, which brings online a single large rollback segment to be used during batch operations. These limitations may be reduced or mitigated by using the new undo tablespaces in Oracle9i.

Raw Devices 

In Unix and NT/Windows systems there are several types of disk formats used. Generally, the highest performance comes from programs that can directly access the disk. In order to be directly accessed a disk must be configured in what as known as raw format meaning no OS buffering or access control is used.

While raw disks provide performance gains over many traditional disk formats they have several limitations that make their use difficult. An example is that only one file may be placed in a raw disk partition at one time, another is that raw disk partitions may require special backup commands. Finally, raw devices can be easily overwritten if the system administrator is not careful.

If you have tuned your application, I/O, and all applicable SGA parameters and still cannot get the performance you want on UNIX or NT, then consider using raw devices. Oracle is capable of reading and writing directly to raw devices. This can increase Oracle performance for disk I/O by over 50 percent and ensures that data integrity is maintained. But when raw devices are used, Oracle datafile names are restricted to a specified syntax. Another limitation is that the entire raw partition has to be used for only one file, which can lead to wasted disk space unless the areas are carefully planned. This will require the DBA to keep an accurate map of which devices belong to which tablespaces, log files, and so on.

Another method is to turn off UNIX buffering. Whether the option of removing UNIX buffering is open to you depends on the version of UNIX you are using.

There are also limitations on types of backup that can be used. Many third-party software packages that are designed for use with Oracle support backup of RAW devices. If you don’t have one of these packages, I suggest ensuring you have enough formatted (cooked) file systems to support a “dd” to a cooked file system followed by a normal backup.

There is some debate as to whether the reported up-to-50 percent increase in speed of access is due to the RAW device usage, or a good deal of it is an artifact of the conversion process from a cooked to araw system. Generally, a system with bad performance has other problems, such as chained rows and excessive table extents as well as improper placement of indexes, tables, redo, and rollback. The DBA converts to raw by exporting, dropping the database, doing the raw partitions, re-creating the database, and then importing. Usually, files will be better placed due to lessons learned. The chained rows and multiple table extents are eliminated by the export/import; and another major performance problem, brown indexes (the process by which excessive numbers of empty leaf nodes resulting from UPDATE and DELETE operations cause index broadening), is fixed by the import rebuild of the indexes. Voila! The system is 50 percent faster, and RAW gets the credit, when doing all of the above to the database on a cooked file system would have given the same improvements.