As with Linux, Microsoft Windows offers some very simple yet highly productive tweaks that can be universally applied to any Windows virtual machines guest operating system install. And interestingly, some are conceptually the same as in the prior Linux section with just a different setting or syntax to accomplish the same tweak.

Tuning Windows for proper CPU setup is actually quite easy there are just two items to address. First, make sure to install the correct address space version to match your CPU architecture (i.e. 32-bit vs. 64-bit). I have not found this to make a substantial and directly measureable impact. It is simply that 64-bit environments offer larger address spaces and thus larger SGAs, which can often potentially reduce overall I/O. Second and most importantly, if possible standardize on Microsoft Windows 2003 Enterprise Edition Release 2 and make sure to install the specific version of Oracle for that platform . Because as much as it pains a Linux bigot like me to admit, I've seen Windows 2003 Enterprise with the right version of Oracle installed give Linux a run for its money. I will not publish any earth shattering results here so as not to get in any trouble or start any religious wars, but on the next page is a chart (Figure 8) I have used in some of my database benchmarking papers when comparing the various operating systems.

Figure 8:  Comparing Windows vs. Linux

The conclusion is clear: choose your database platform based upon your current hardware and staffing assets. If all your systems people currently know and are comfortable with is Windows, then why suffer learning and embracing a new OS when the performance is essentially the same? However, note that the above results were accomplished using the standard Oracle Windows binaries and not the Windows 2003 specific versions. I just want to state that my unpublished results make it clear that Oracle on Windows 2003 is a completely viable platform.

To improve I/O for file system based Oracle data files, Windows offers a little known and seldom used option that can yield between 50-150% performance improvements in standard database benchmarks like the TPC-C by simply changing the Windows registry setting as follows:

• HKEY_LOCAL_MACHINE\System\CurrentControlSet\ Control\FileSystem\ NtfsDisableLastAccessUpdate=1

What this does is set the operating system to Disable Last Access Update for directories and files on this Windows server, which translates into radically reduced total I/O. Since the Oracle background processes are accessing the data files every three seconds anyway and have their own headers with timestamps within them, why spend I/O resources to update time attributes for files or directories?

Some other common Windows registry tweaks for database servers include:

• Disable 8 dot 3 Name Creation - This setting controls whether MS-DOS compatible 8.3 file names should be generated on NTFS partitions. Disabling this feature can increase the performance on high usage partitions that have large amount of files with long filenames. Setting this option also toggles whether to permit extended characters to be

• Enable a large size file system cache This entry controls whether the system maintains a standard size or a large size file system cache. Enabling a larger cache makes sense for networked database servers with sufficient memory.

• Disable paging of the kernel code - This entry controls whether the user and kernel mode drivers and the kernel mode core system code itself can be paged. Disabling the paging of kernel code makes sense for database servers with sufficient memory.

• IO Page Lock Limit - This entry controls the maximum amount of RAM that can be locked for I/O operations. The default minimizes RAM usage. An I/O intensive system could benefit from larger buffer sizes. Caution: setting this parameter too high can result in slower performance. Set it in increments and see how it affects your system.

And whose corresponding recommended registry settings are as follows:

• HKEY_LOCAL_MACHINE\System\CurrentControlSet\ Control\FileSystem\NtfsDisable8dot3NameCreation = 1

• HKEY_LOCAL_MACHINE\System\CurrentControlSet\ Control\FileSystem\NtfsAllowExtendedCharacterIn8dot3Name = 0

• HKEY_LOCAL_MACHINE\System\CurrentControlSet\ Control\SessionManager\MemoryManagement\ LargeSystemCache = 1

• HKEY_LOCAL_MACHINE\System\CurrentControlSet\ Control\SessionManager\MemoryManagement\ DisablePagingExecutive=1

• HKEY_LOCAL_MACHINE\System\CurrentControlSet\ Control\SessionManager\MemoryManagement\

• IoPageLockLimit = N, where N is chosen as follows:

  • if RAM <= 32MB then

  • IoPageLockLimit = 512

  • if RAM > 32MB then

  • IoPageLockLimit = 4K

  • if RAM > 64MB then

  • IoPageLockLimit = 8K

  • if RAM > 128MB then

  • IoPageLockLimit = 16K

  • if RAM > 160MB then

  • IoPageLockLimit = 32K

  • if RAM > 256MB then

  • IoPageLockLimit = 64K

Note that I have included on the books DVD and download website a free Windows to easily set all these parameters on both local and remote database server as shown below (Figure 9).

Figure 9:  Windows Registry Settings

To improve I/O for ASM based oracle data files, simply double the default SGA sizing parameter for the ASM instance from 64MB to 128MB. Memory is far too cheap these days to haggle over such a small amount. As stated before, the results will more than justify the cost.

Quick Reference

Below is a summary of the recommended client OS optimizations:

Linux:

• Kernel

  • 64-bit

  • SMP Support

  • Compile and link a Monolithic kernel

  • Possibly recompile/re-link C-runtime library

• Memory

  • Huge Pages

• I/O      

  /etc/fstab add ,noatime
   

• Remove non-essential software components

• Disable non-essential background processes/services

Windows:

• Version

• 64-bit

• Windows 2003 Enterprise R2

• Registry (use my freeware program to update)

• Disable last access update

• Disable 8 dot 3 name creation

  • Enable large size file system cache

  • Disable paging of kernel code

  • IO Page Lock Limit >= 16K

Conclusion

In this chapter, we looked at how to best configure our client virtual machines and their guest operating systems in order to maximize database performance. All of these techniques should be considered as Best Practices and liberally implemented across all your virtual machines and their guest operating systems. The cost to implement each of these concepts is relatively small, but both their individual and cumulative performance impacts are well worth the trouble.

 
This is an excerpt from Oracle on VMWare: Expert tips for database virtualization by Rampant TechPress.