Release 8.0
A54638-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8(TM) Server Tuning Release 8.0 A54638-01 |
|
This chapter explains how to tune the operating system for optimal performance of the Oracle Server. Topics include:
Operating system performance issues commonly involve process management, memory management, and scheduling.
If you have tuned the Oracle instance performance and still need faster performance you should verify your work, or try to reduce system time. Make sure there is enough I/O bandwidth, CPU power, and swap space. Do not expect, however, that further tuning of the operating system tuning will have a big effect on application performance.
Sometimes there is simply not a lot of room for improvement on the operating system side. Changes in the Oracle configuration or in the application itself are likely to make a bigger difference in operating system efficiency than changing the O/S directly.
For example, if your application gives rise to a lot of buffer busy waits, the number of system calls will increase. If you reduce the buffer busy waits by tuning the application, then the number of system calls will go down. Similarly, if you turn on the Oracle initialization parameter TIMED_STATISTICS, then the number of system calls will increase; if you turn it off, then system calls will decrease.
See Also: For detailed information on operating system tuning, see your Oracle platform-specific documentation, and your operating system vendor's documentation.
Operating systems and device controllers provide data caches which do not directly conflict with Oracle's own cache management, but which can consume resources with no benefit to the user. This is most marked when running under UNIX with the database container files held in the UNIX file store: by default all database I/O will go through the file system cache. On some UNIX-based systems direct I/O is available to the filestore. This allows the database container files to be accessed within the UNIX file system, bypassing the file system cache. This saves CPU resource and allows the file system cache to be dedicated to non-database activity such as program texts and spool files.
On NT this is not an issue. All file requests by the database bypass the caches in the file system.
Evaluate the use of raw devices on your system. They involve a lot of work on the part of the DBA, and may provide some performance benefit.
Raw devices impose a penalty on full table scans, but may be essential on UNIX-based systems if the UNIX implementation does not support write through cache. The UNIX file system speeds up full table scans by reading ahead when the server starts requesting contiguous data blocks. It also caches full table scans. If your UNIX-based system does not support the write-through option on writes to the file system, then it is essential to use raw devices to ensure that at commit and checkpoint, the data which the server assumes is safely established on disk has actually gotten there. If this is not the case then recovery from a UNIX or system crash may not be possible.
Raw devices on NT are similar to UNIX raw devices, however all NT devices support write through cache.
Many processes ("threads", on NT systems) are involved in the operation of Oracle, and they all access the shared memory resources in the SGA.
Be sure that all Oracle processes, both background processes and user processes, have the same process priority. When you install Oracle, all background processes are given the default priority for your operating system. Do not change the priorities of background processes, and do make sure that all user processes have the default operating system priority.
Assigning different priorities to Oracle processes may exacerbate the effects of contention. Your operating system may not grant processing time to a low priority process if a high priority process also requests processing time. If a high priority process needs access to a memory resource held by a low priority process, the high priority process may wait indefinitely for the low priority process to obtain the CPU, process, and release the resource.
The key statistics to extract from any operating system monitor are
Look at CPU utilization to see the ratio between time spent running in application mode and time spent running in operating system mode. Look at runqueues to see how many processes are runable, and how many system calls are being executed. See if paging or swapping is occurring, and check the number of I/Os being performed.
See Also: Your Oracle platform-specific documentation, and your operating system vendor's documentation.
This section provides hints for tuning various systems.
Familiarize yourself with platform-specific issues, so that you know what performance options your operating system provides. For example, some platforms have post wait drivers, which allow you to map system time and thus reduce system calls, enabling faster I/O.
See Also: Your Oracle platform-specific documentation, and your operating system vendor's documentation.
On UNIX-based systems, try to find a good ratio between the amount of time the operating system runs (fulfilling system calls and doing process scheduling), and the amount of time the application runs. Your goal should be running 60% to 75% of the time in application mode, and 25% to 40% of the time in operating system mode. If you find that the system is spending 50% of its time in each mode, then you should investigate to determine what is wrong.
The ratio of time spent in each mode is only a symptom of the underlying problem, which might have to do with:
If such conditions exist, then there is less time available for the application to run. The more time you can release from the operating system side, the more transactions your application can perform.
On NT systems, as with UNIX-based systems, you should establish an appropriate ratio between time in application mode and time in system mode. On NT you can easily monitor many factors with Performance Monitor: CPU, network, I/O, and memory are all displayed on the same graph, to assist you in avoiding bottlenecks in any of these areas. Note that the term "process" as used in this tuning manual refers to a "thread" in the NT environment.
Consider the paging parameters on a mainframe, and remember that Oracle can exploit a very large working set.
Free memory in a VAX/VMS environment is actually memory which is not currently mapped to any operating system process. On a busy system free memory is likely to contain a page which belongs to one or more currently active process. When that access occurs a "soft page fault" will take place, and the page will be included in the working set for the process. If the process cannot expand its working set, then one of the pages currently mapped by the process will have to be moved to the free set.
Any number of processes may have pages of shared memory within their working sets. The sum of the sizes of the working sets can thus markedly exceed the available memory. When Oracle Server is running, the SGA, the Oracle kernel code, and the Oracle Forms runtime executable are normally all shareable and account for perhaps 80% or 90% of the pages accessed.
Adding more buffers is not necessarily better. Each application has a threshold number of buffers at which the cache hit ratio stops rising. This is typically quite low (approximately 1500 buffers). Setting higher values simply increases the management load for both Oracle and the operating system.
