Running Oracle on 10g on Windows Server 2003 x64 Editions: Unlimited Scalability and Performance

Oracle has been available on Windows Server since Windows NT shipped in 1994. In that time there have been many changes to Oracle as features have been added and improved. One of the most significant improvement to Oracle on Windows has just been introduced (this was a year ago they supported 64 bit AMD/EM64T) without much fanfare or publicity. This is the introduction of Oracle 10g on Microsoft Windows Server x64.

The Intel x86 architecture was introduced in 1978 as a 16-bit processor. This was replaced in 1986 with a 32-bit version. I don’t think that anyone expected the 32-bit architecture to hang around this long. The 32-bit architecture allowed for up to 4 GB of virtual address space and 4 GB of RAM. This was later improved to allow 64 GB of RAM with PAE, but the virtual memory (process address) limit remained at 4 GB.

This remained in effect (for the PC server platform) until the introduction of the AMD Opteron and Intel EM64T processors. The EM64T and Opteron processors can access up to 256 Terabytes of virtual memory (248 bytes). The architecture allows this limit to be increased to a maximum of 16 exabytes (264 bytes). The EM64T and Opteron processors can currently address up to 1 Terabyte of physical memory ( 240 bytes). The architecture allows this limit to be increased to a maximum of 4 petabytes (252 bytes).

The table below shows some of the general maximum memory addressable by Windows 32 bit and 64 bit:

Oracle is made up of many independent processes (or threads) that perform independent functions. The server processes are created when a user connects into the Oracle instance and does work on behalf of the user such as retrieving data from the SGA, or reading data from the data files, modifying data in the SGA and presenting data back to the user. The background processes do work on behalf of the Oracle instance and consist of things such as the DB writer, Log writer, archiver, system and process monitors and many others. These are independent entities that make the Oracle RDBMS work.

In most operating systems the above mentioned entities are processes. In Oracle for Windows these entities are threads. The difference between a process and a thread is how memory is used. In essence, a thread is a subcomponent of a process. A process has its own memory and registers and acts somewhat independently. With the Intel x86 architecture a process can address up to 2 GB of memory, or 3 GB if the /3GB flag is used upon Windows startup. The operating system loads the process’s memory and registers into the CPU and start the process running. Whenever the process’s time slice is up or it puts itself to sleep waiting on an I/O or other resources the CPU unloads the process, loads another process’s memory and registers and starts it up. This is known as a process or context switch. The number of context switches per second can be seen in perfmon under the system object. A context switch is a very expensive operation.

In order to make this more efficient, Oracle was developed to use the Windows thread architecture. A thread is a subcomponent of a process. With a thread, multiple entities can use the same process, thus avoiding context switches. The process allocates the memory and each thread has a pointer into the same memory. The downside of using threads is that all Oracle entities, such as the server processes and background processes share the same 3 GB address space. This has caused an issue with memory, since each Oracle server process and background process can consume significant memory. The end result is that the number of Oracle user sessions can be limited on Windows.

If you were to look into the task manager on a Windows system running Oracle you would see a process called oracle.exe is made up of a number of threads (20-30, depending on what options you are using). If you expand the columns to include thread count Oracle 10g supports asynchronous I/O to all files on Windows Server 2003 x64 bit. Also logical and raw file partitions are fully supported (faster than NTFS) Internal file I/O is 64 –bit and the maximum file size is 64GB (max. DB size is 4 petabytes).

So, as you can see, the decision to use Windows threads for Oracle has turned out to be somewhat of a liability, since the virtual memory address space limitation has caused the number of supported sessions to be somewhat limited. However, the upside is that the reduction of contexts switches provides a performance improvement.

With the switch from Oracle 32-bit to Oracle 64-bit the virtual memory limitation is no longer an issue. With no more session limitations and the additional advantages that you get from Oracle on Windows such as Active Directory integration, ease of use and stability it has become a great platform for Oracle.

The move to 64 bit is easy. There is complete interoperability between 32 bit clients and 64 bit servers and vice-versa. Also there is improved performance, availability and scalability on the 64 bit platform. TPC (http://www.tpc.org) has several benchmarks for database workloads performed on 32 bit and you can compare these with the ‘free’ performance gains on 64 bit platform without changing any code.

Please refer to http://www.oracle.com/technology/tech/windows/faq.html for supported Oracle DB versions with Windows Server 64 bit. In future blog entries I will elaborate on this topic in more detail and cover additional areas around Oracle on Windows.