Oracle on Windows

Oracle has been around on Windows Server for many years (more than a decade – since NT). In that time there have been many changes to Oracle as features have been added and improved. However, in my opinion, the most significant improvement to Oracle on Windows was introduced about a year ago without much fanfare or publicity. This is the introduction of Oracle 10g on Microsoft Windows Server x64.

The introduction of Oracle on Windows Server x64 has overcome a limitation that has existed since the introduction of Oracle on Windows. The number of user sessions is limited with Oracle on Windows due to the way Oracle is architected on Windows and the basic architecture 32-bit chipset. Before I get into that, let’s review the 32-bit architecture and Oracle architecture.

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) limits remain 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 ( 2^48 bytes). The architecture allows this limit to be increased to a maximum of 16 exabytes (2^64 bytes). The EM64T and Opteron processors can currently address up to 1 Terabyte of physical memory ( 2^40 bytes). The architecture allows this limit to be increased to a maximum of 4 petabytes (2^52 bytes).

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. Our experiments have shown connection failures at around 300 – 400 users.

If you were to look into the task manager on a Windows system running Oracle you would see a process called oracle.exe. If you expand the columns to include thread count you would see that oracle.exe is made up of a number of threads (20-30, depending on what options you are using).

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. Thus the liability has been turned into an asset. 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. Our tests in the lab have seen no connectivity problems well over 1,000 sessions.

I am often asked by customers what platform they should choose for Oracle. I feel that there are many viable platforms. You should look at what you are comfortable with. If you are a Windows shop and are comfortable with Windows and have significant Windows expertise, then this is the platform that you should choose. Adding a foreign OS into a Windows shop just to run Oracle can be a costly mistake.

In the next few weeks I will be elaborating on some of the additional features that makes Oracle on Windows a great platform to use.