Skip Headers

Oracle® Database Platform Guide
10g Release 1 (10.1) for Windows

Part Number B10113-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

8 Tuning Windows to Optimize Oracle Database

This chapter describes how to tune the Windows Server operating system to ensure that Oracle Database is running in the best possible environment.

This chapter contains these topics:

Windows Tuning Overview

Windows Server operating systems offer considerably fewer tuning adjustments than UNIX. This difference constrains systems administrators when they try to optimize Windows Server performance, but it also makes Windows Server easier to use.

There are still ways, however, to make Windows Server a better application server environment for Oracle Database. Most of the operating system specific procedures described in this chapter enable Oracle Database to reserve more system resources, such as CPU, memory, and disk I/O.

In addition, because Oracle Database is a high-performance database management system that effectively uses resources of your Windows computer, it should not also serve as any of the following:

These configurations consume network, memory, and CPU resources. In addition, the Windows computer that is running Oracle Database should not be locally accessed with a high frequency or intensively used for local user processing, unless it has enough resources to accommodate all this activity.


Information presented in this chapter does not apply to Oracle Database installations on Windows NT Workstation. It does apply to Oracle Database installations on Windows NT Server and Windows NT Server 4.0, Enterprise Edition. Windows NT Server 4.0, Enterprise Edition includes capability for using additional products. One such product is Microsoft Cluster Server, which is required to implement Oracle Fail Safe.

Large Page Support

Large page support is a new feature of Oracle Database 10g. It provides a performance boost for memory-intensive database instances running on Windows Server 2003. By taking advantage of newly introduced operating system support, Oracle Database 10g can now make more efficient use of processor memory addressing resources. Specifically, when large page support is enabled, the CPUs in the system will be able to access the Oracle Database buffers in RAM more quickly. Instead of addressing the buffers in 4KB increments, the CPUs are told to use 4MB page sizes when addressing the database buffers.

This feature is particularly useful when the Oracle buffer cache is several gigabytes. Smaller-sized configurations will still see a gain when using large pages, but the gain will not be as great as when the database is accessing large amounts of memory.

To enable large page support, set ORA_LPENABLE to 1 in the Oracle key of the Windows Server 2003 registry.

User Threads

Oracle Database for Windows executes in a single process with multiple threads servicing user requests and background tasks. These threads are scheduled by the Windows operating system. In some circumstances, performance can be improved by running Oracle in user-thread mode. User threads are light-weight, application-scheduled threads, also referred to in Windows terminology as fibers. In user-thread mode, Oracle threads are scheduled not by the operating system, but by Oracle itself. Oracle can then use its knowledge about contention around shared resources to achieve more efficient and scalable thread scheduling.

When to Use User-Thread Mode

User-thread mode should only be enabled as a means to improve the performance of an optimized Oracle Database. That is, you should first attempt to optimize the default configuration of Oracle Database for Windows as discussed elsewhere in this chapter.

User-thread mode gives performance gains only when an opportunity exists to improve the scheduling of Oracle threads. This situation typically occurs when an Oracle instance has fully utilized the CPU and is running a load that contains a high ratio of concurrent user requests to number of CPUs. Oracle wait events may provide clues that such opportunities exist.

See Also:

Oracle Database Performance Tuning Guide for more information on Oracle wait events

Almost every wait event has a scheduling component. Each time a thread waits for a certain event, part of the wait time can be due to scheduling. Some of the scheduling delay may be mandatory. For example, if the system is heavily loaded, then there will be scheduling delay because not all runnable threads can run on a given CPU at the same time. But some of the delay may be due to sub-optimal scheduling. By enabling user threads, you allow Oracle to optimize the scheduling of different tasks and therefore improve the average wait time of any wait event that has a non-mandatory scheduling component.

For example, the Oracle wait event log file sync measures the time it takes for an Oracle thread executing a user task to commit a transaction. This includes the time taken to actually write to the log file, measured by log file parallel write, and some scheduling delay. Even though the transaction is committed, the wait time continues to grow until the thread waiting for log file sync is allowed to run again. Therefore, one indicator that user threads should be used is if log file sync has a high average wait time and other wait events that are included in the log file sync wait time (for example, log file parallel write) have a low average wait time.

Another example is the Oracle wait event free buffer waits, which happens when the DBWR thread is unable to write out dirty buffers fast enough. One possible cause of this wait event is too-seldom scheduling of the DBWR background thread. By enabling user-threads, you give Oracle control over when the DBWR background thread is scheduled. Oracle can then schedule DBWR often enough to keep free buffer waits at a minimum.


User-thread mode should be used only after other means of reducing free buffer waits have been exhausted. See Oracle Database Performance Tuning Guide for more information.

Other wait events with a scheduling component include db file sequential read, db file parallel read, and db file scattered read. Part of the measured time for these wait events is the actual time it takes for the I/O to complete. But during the remainder of the wait time a thread is waiting to be rescheduled. By looking at disk statistics, using tools like Windows Perfmon, you can find out what portion of wait time is I/O latency and what portion is scheduling delay. User-thread mode can help reduce the non-mandatory scheduling delay.

User-thread mode on Windows is not currently supported in the following configurations:

  • Real Application Clusters

  • Microsoft Transaction Server

  • Oracle Data Mining

  • Third party libraries used directly by Oracle

Enabling User-Thread Mode

Follow this procedure to enable user-thread mode:

  1. Shut down all instances.

  2. Stop all Oracle services (both for the database and for the listener).

  3. Exit any SQL*Plus sessions.

  4. Make sure that the ORACLE_HOME environment variable is set.

  5. Open a command prompt and enter cd /d %ORACLE_HOME%\bin

  6. Run enable_fibers.bat

Disabling User-Thread Mode

Follow this procedure to disable user-thread mode:

  1. Shut down all instances.

  2. Stop all Oracle services (both for the database and for the listener).

  3. Exit any SQL*Plus sessions.

  4. Make sure that the ORACLE_HOME environment variable is set.

  5. Open a command prompt and enter cd /d %ORACLE_HOME%\bin

  6. Run disable_fibers.bat

Reduce Priority of Foreground Applications on Server Console

One of the settings provided by default during installation of Windows Server gives interactive foreground applications priority over every background process. To prevent foreground applications on the server console from taking excessive processor time away from Oracle Database, you can reduce priority for foreground applications.

See Also:

Your Windows documentation for instructions on reducing priority of foreground applications

Configure Windows Server to Be an Application Server

Windows memory manager divides up system memory into three different pools described in Table 8-1.

Table 8-1 Windows Server Memory Shares

Pool Percent of Total Memory
Kernel and other system services 9%
File Cache 41%
Paged Memory 50%

Windows Server memory manager tries to balance each application's usage of memory by dynamically paging memory between physical RAM and a virtual memory paging file. If an application is particularly memory-intensive (like Oracle Database) or if a large number of applications are running concurrently, then combined memory requirements of the applications may exceed physical memory capacity.

The large proportion of memory reserved for file caching (41%) can be quite beneficial to file and print servers. But it may not be advantageous to application servers that often run memory-intensive network applications. A Windows Server file cache is particularly unnecessary for Oracle Database, which performs its own caching through System Global Area.

You can reset the Windows Server memory model from the default file and print server, with its large file cache, to a network applications model, with a reduced file cache and more physical memory available for Oracle Database.

See Also:

Your Windows documentation for instructions

Disable Unnecessary Services

After you have significantly reduced the file cache as described in "Configure Windows Server to Be an Application Server", you can retrieve additional physical memory for Oracle Database by disabling services not needed for core operating system functionality. Unnecessary services include:

Consult with your systems administrator to identify other unnecessary services.

Do not disable any of the following services:

See Also:

Your Windows documentation for instructions on disabling unnecessary services

Remove Unused Network Protocols

Remove all unnecessary network protocols on Windows so that processing time can be concentrated on servicing only critical protocols.

See Also:

Your Windows documentation for instructions on removing unnecessary network protocols

Reset Network Protocol Bind Order

If multiple protocols must be installed on the server, you can give the protocol most frequently used by Oracle Database highest priority by resetting the network protocol bind order.

See Also:

Your Windows documentation for instructions on resetting network protocol bind order

Apply Latest Reliable Windows Server Service Pack

Microsoft releases operating system patches, called Service Packs, on a quarterly basis. Service Packs are collections of bug fixes and product enhancements to the basic Windows Server release. In general, apply Service Packs as soon as it is safe to do so, since they fix bugs and can improve Windows Server performance or functionality.

While Service Packs are supposed to fix bugs, there have been reports (for example, the initial release of Windows NT Server 4.0 Service Pack 2) of bugs within the patch updates themselves. In general, it is safest to wait a few weeks after a Service Pack is released before implementing it. This allows time for other field sites to report any problems with the SP release.

The latest Windows Server Service Packs may be downloaded as self-extracting archives from

Unless there are assurances that the Service Pack works without flaws on Windows Server, choose to create an Uninstall directory. This enables the Service Pack to be removed and the original configuration to be restored.

Service Pack files overwrite similarly-named files in the previous Windows Server configuration. However, Service Pack files can be overwritten in turn by setup programs that copy files from the original installation media.

For example, installing a new network protocol or printer driver usually requires copying files from original Windows Server installation media. When Service Pack files are comprehensively or selectively overwritten, the Service Pack must be re-applied.

Use Hardware or Operating System Striping

Compared to CPU and memory speeds, hard disk drives are extremely slow. Now that hard disk drives are relatively inexpensive, Oracle recommends that Windows Server use logical volumes comprised of striped physical disks. Data striping is an effective means of reducing the impact of relatively slow hard drives by distributing file I/O across a number of hard drives simultaneously.

Striping data across a number of disks is one example of a redundant array of inexpensive disks (RAID). There are several different types of RAID, also referred to as RAID levels, ranging from high performance to high reliability. The three most common RAID levels in Oracle Database installations are RAID-0, RAID-1, and RAID-5. Descriptions of each RAID level follow Table 8-2, which shows each level's read and write penalties.

Table 8-2 RAID Levels in Oracle Database Installations

RAID Level Read PenaltyFoot  Write PenaltyFoot 
0 (Disk Striping) 1:1 1:1
1 (Disk Mirroring) 1:1 2:1
0 + 1 1:1 2:1
5 (Distributed Data Gathering) 1:1 4:1

Footnote Read penalty is ratio of I/O operations to read requests.
Footnote Write penalty is ratio of I/O operations to write requests.

Disk Striping

RAID level 0 enables high-performance, fault-intolerant disk striping. Multiple physical hard disks are aggregated into a logical whole, either by a disk controller or through the operating system (for example, Windows 2000 Server striped volumes). Data operations against the logical volume are broken down into as many chunks as there are physical drives in the array, making simultaneous use of all disks. Given identical hard disks, if one hard disk has a throughput rate of DISKRATE operations/second, then a RAID 0 logical volume has a rate of:

(DISKRATE * [number of physical  drives in array]) operations/second

The downside of RAID 0 is its lack of fault tolerance. If one disk in the logical volume fails, the whole volume fails and must be restored from a backup.

Disk Mirroring

RAID level 1 enables fault tolerant disk mirroring with some chance of a performance penalty. Essentially, every write to a mirrored disk is duplicated on another drive dedicated to this purpose (the mirror drive). If the mirrored disk fails, the mirror drive is brought online in real time. After the faulted drive is replaced, the mirror configuration can be reestablished.

The read penalty for RAID level 1 is nominally 1:1, but it may benefit from split reads on some controllers. When the controller knows which mirror can be accessed fastest, for example, it can lower seek times by directing I/O operations to that disk.

Disk Striping + Mirroring

RAID level 0+1 enables mirroring of an array of striped hard disks. This is a blend of RAID 0 and RAID 1, offering high-performing fault tolerance.

Distributed Data Guarding

RAID level 5, also known as disk striping with parity, eliminates the costly requirement to mirror. In RAID 5, multiple hard disks are aggregated into a striped logical volume, similar to RAID 0, but each drive contains parity information such that any single drive failure is tolerated. With one failed drive, a RAID-5 system can allow continued access to data, although access times are greatly reduced due to on-the-fly rebuilding of bytes from parity information. RAID-5 solutions usually allow hot-swapping of faulty drives with replacements, triggering a rebuild of the failed drive's data onto the replacement from parity information.

The write penalty of 4:1 results from 2 reads and 2 writes during parity calculation.

Multiple Striped Volumes for Sequential and Random Access

If there are enough physical disks in Windows Server, create at least two striped volumes (in addition to a standalone hard disk or striped volume for the operating system). One striped volume can be used for sequential data access, and the other can be used for random data access.

Oracle Database redo logs and archived redo logs, for example, are written in sequential order. Because of reduced head movement, hard disks perform best when reading or writing sequential data.

Oracle Database data files, however, are usually accessed in random order. Random access in a hard disk results in significant head movement, translating to slower data access.

Unless redo logs are separated from datafiles (at physical device level), undo file I/O contention may result, increasing access times for both types of files.

Multiplex Windows Server Virtual Memory Paging File

Some virtual memory paging is likely even if Oracle Database is the only network application running on Windows Server, because Windows Server memory manager will attempt to move seldom-used pages to disk to free up more physical memory for hot pages.

Multiplexing the Windows Server virtual memory paging file is a good strategy to boost overall system performance. Splitting the paging file onto at least two different physical volumes (or logical volumes as long as underlying physical volumes do not overlap) provides a significant performance boost to virtual memory swapping operations.

Even though this is a good technique to increase speed of virtual memory paging, too much paging activity is still a performance hit and should be corrected by adding more RAM to the server.

General Page File Sizing Tip

Oracle recommends that total combined size of your page files be at least equal to physical RAM on your computer. Configurations where combined size is two to four times the size of physical RAM are not uncommon. Minimize paging as much as possible. But situations in which the operating system runs out of or low on paging space are to be avoided at all costs. Adequately-sized paging files spaced across physical disks spread out I/O most efficiently, because the operating system spreads paging evenly across page files.


Internal read/write batch size for Windows is 4K.

Close All Unnecessary Foreground Applications

Once procedures in previous sections have been applied, remember to close any unnecessary foreground applications. First, remove all applications from Startup folders of Windows Server console operators. Second, minimize the window when executing long-running scripts from a command prompt, so that Windows Server can focus on the operation and not on a flood of window repaint messages. Third, disable screen savers, which can quickly saturate the CPU. If a screen saver must be run, choose Blank Screen, which uses the least amount of processing time.