Windows as an OS Platform for Oracle Database – Where Do I Start?

July 13, 2010 (Modified July 14, 2010, July 22, 2010, February 22, 2011)

I try to follow the discussions on the Oracle-L mailing list, even though I do not participate in that mailing list.  Just a couple of days ago I saw an interesting discussion thread in the group that forced me to think for a moment… maybe one should use a shared server configuration rather than a dedicated server configuration when Oracle Database is running on Windows XP?  Think about it for 10 seconds – why should someone do this, and why not?

While you think about it, let’s chain together a couple of forum threads to compile a collection of notes about the combination of Windows and Oracle Database.

I have worked with the Windows operating system a bit, having read the Windows 95 Resource Kit book cover to cover roughly 15 years ago (and a book about the Windows 95 registry from the same time period), the Windows Vista Resource Kit book a couple of years ago, and several books that described Microsoft’s server operating systems.  I have also posted a couple of responses to various forums, answering questions about the sometimes unexpected behavior of the Windows operating systems.

Take, for example, this thread where the original poster asked what are the advantages of running Oracle Database on a server edition of Windows compared to a desktop edition of Windows.  This is the response that I provided (the italics section should have been included in the original post):

There are a couple of important differences between the two operating systems:

• Windows XP is limited to 10 simultaneous external connections. It is easy to reach that limit even when other computers are not attempting to remotely connect to the Windows XP computer. The System event log will display a message “TCP/IP has reached the security limit imposed on the number of concurrent TCP connect attempts.” when the 10 connection limit is attempted to be exceeded. No such limit exists on Windows 2003.
• Memory handling algorithms are different between the two operating systems. There are memory limits in the operating systems that are independent of the amount of physical memory installed. For example, what do you do if the following message appears in the System event log: “The server was unable to allocate a work item 2 times in the last 60 seconds.” On Windows 2003, the problem can be fixed by creating a MaxWorkItems entry in the Windows registry.
• The Home and Home Starter (I am not sure if that is the correct name) versions of Windows XP do not fully support Oracle Database, because those versions of the operating system do not permit user controlled/configurable local user groups, user permissions (logon as batch), nor file level permissions (file permissions/file ownership are there – they just cannot be modified through the user interface).
• [Server version supports large memory pages, while the desktop version does not.]
• Yes, there are other differences [between the server and desktop operating systems], even though the two operating systems share the same kernel. The R2 version of Windows 2003 is preferred over the original release.

With the above bullet points in mind, how would you answer the original poster in the Oracle-L discussion thread (incidentally, Windows 95 did not impose the 10 simultaneous external connection limit)?

Let’s take a look at memory, since it was mentioned in the above quote.  From another OTN thread that I participated in:

> I have a vendor recommending shared server due to a windows thread limitation.  Upon reading up on windows threads, it seems to be more an issue of an application not properly managing memory withing the memory stack.
> Does anyone know of a way to monitor thread utilization on a per session basis?
> Has anyone ever seen an Oracle based application hit a windows thread limit?
> Thanks in advance.
—————-

A quick Google search finds this link that suggests that 500,000 threads are possible, but the author was only able to reach 13,000 threads:
http://blogs.msdn.com/oldnewthing/archive/2005/07/29/444912.aspx
32 bit Windows: “Because the default stack size assigned by the linker is 1MB, and 2000 stacks times 1MB per stack equals around 2GB, which is how much address space is available to user-mode programs.”

Metalink Note:46001.1: “Oracle Database and the Windows NT memory architecture, Technical Bulletin” confirms that by default Oracle allocates 1MB per thread. The number of possible threads is thus limited by either the maximum per process limit for memory, or the combination of system RAM and swap space, with each session requiring a minimum of 1MB just to connect.

The number of threads can be viewed in Task Manager on the server – it is necessary to manually add the “Thread Count” column to Task Manager to see the current number of threads in a process. There is typically one thread per session, plus several background threads.

Niall Litchfield, who authored the chapter Running Oracle on Windows in the “Expert Oracle Practices” book provided a brief follow-up to my post in that thread.

Continuing with the discussion of memory on Windows, another one of my posts on the OTN forums mentioned the following:

The Standard Edition of 64 bit Windows 2003/2008 Server is artifically limited to 32GB in a flat memory model. The Enterprise Edition of 64 bit Windows 2003/2008 Server supports more memory than you can physically connect to the server’s motherboard. 64 bit Oracle Database Standard Edition One and greater is able to take advantage all of the memory in the server that is provided within the flat memory model without any special configuration – other than enabling large page support to decrease the overhead of managing a lot of memory.

The problem that you are encountering with 32 bit Windows is a limitation of 32 bit software – it can only address 2^32 GB of memory – that is 4GB. To access more than 4GB, a memory region is set aside to act as a translation window to peek into the upper memory region which can contain only the Oracle buffer cache. Articles on Metalink describe how this works. The 64 bit flat memory model does not have this limitation. With 64GB in the server [on a 64 bit operating system], you could specify a 32GB SGA_TARGET and 20GB PGA_AGGREGATE_TARGET, if you so desire.

Incidentally, the desktop 64 bit Windows operating systems do not impose the 32GB limit like the 64 bit Standard Edition of the server operating systems.

If you have run Oracle Database Database 10.1 or higher on Windows you might have seen a warning in Enterprise Manager stating that significant virtual memory paging is happening in the server.  There have been several threads on the OTN and other forums that mentioned this particular error message.  One of the threads that I participated in that is found on the OTN forums mentioned the following and included a link to one of my Usenet posts on the same topic:

The message may be a false alarm – even checking task manager on Windows 2003 may be misleading. On Windows 2003, the “PF Usage” statistic in task manager is not page file usage, but instead total virtual memory usage, which includes RAM and page file. Oracle Enterprise Manager, I believe, is showing the “PF Usage” statistic, and is making the assumption that this is page file usage.
For additional analysis see the discussion in this link:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/b62c4d761cace2a6

If I recall correctly, enabling large page support on Windows causes an incorrect amount of memory to be reported in Task Manager and by the TASKLIST command executed at the Windows command line.  However, enabling large page support may reduce kernel (system) mode CPU usage and the amount of memory needed for managing memory, so it might make sense to enable large page support.  I described the process in an OTN thread a couple of years ago:

Below are the steps that I used to enable large page support for 64 bit Windows.
Set up for Large Page Support (cannot use LOCK_SGA in init.ora/spfile):

1. Choose Start > Settings > Control Panel
2. Double-click Administrative Tools.
3. Double-click Local Security Policy.
4. Expand Local Policies and select User Rights Assignment.
5. Select Lock pages in memory and choose Action > Security…
6. Click Add…
7. Select the username that was used to install Oracle and the ora_dba group from the Name list.
8. Click Add.
9. Click OK to close the Select Users or Groups dialog.
10. Click OK to close the Local Security Policy Setting dialog.
11. Open Regedit
12. Navigate to:
    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
13. Create a registry key (REG_SZ) named ORA_LPENABLE and set the value to 1
14. Reboot the server.

Tuning Windows for Oracle… I might have replied to a couple of forum threads over the years in this topic.  One of those threads is this one, where I unfortunately had to correct a couple of suggestions that were quoted from another resource (just another reason why one should not blindly copy the work of others).

A couple notes about the above setting suggestions:

• I believe that you will find that NtfsDisable8dot3NameCreation is already set to 1 on 64 bit Windows server operating systems. The reasoning is that only 16 bit programs need the 8.3 names, and 64 bit Windows cannot run 16 bit applications.
• LargeSystemCache = 1 – this does not look correct, see:
  http://technet.microsoft.com/en-us/library/cc784562(WS.10).aspx

I suggest taking a look at the following document:
http://download.microsoft.com/download/2/8/0/2800a518-7ac6-4aac-bd85-74d2c52e1ec6/tuning.doc (edit Feb 22, 2011: note that the preceding link is for Windows 2003, use this link for the Windows 2008 version of the document)
Do not follow the directions related to TcpAckFrequency in the above document [think 45 minutes to transfer a file over a gigabit connection when the transfer should have completed in three to four seconds].

A couple other documents which you might find helpful (pick the version specific for your release of Oracle):
http://download.oracle.com/docs/html/B13831_01/tuning.htm (edit: the documentation site appears to be down at the moment, this link applies to Oracle Database 11.2.0.1 and Windows server operating systems and will be accessible when the documenation site comes back online – in the mean time the Google cache version)
http://download.oracle.com/docs/html/B13831_01/architec.htm

Tuning, of course, leads to questions, “Is Oracle Slower on Windows than on Linux”  as asked in another OTN thread.  I provided a couple of comments in that thread, with the following as the most relevant to this blog article:

There are a lot of factors which may have contributed to the slower than expected performance on Windows 2003 that you noticed. Those factors include:

• Not collecting system (CPU) statistics on Oracle 9i or higher
• Not collecting data dictionary statistics on Oracle 9i or higher
• Not configuring large page support, if it is able to be used
• Not taking advantage of a battery backed cache to improve write performance
• Leaving the default db_file_multiblock_read_count set to 16 (I believe that is what the DBCA sets on 10g and above) rather than allowing Oracle to auto-tune the parameter. Limiting extent sizes to smaller than 1MB could also cause performance problems.
• Installing a virus scanner on the server, especially if it is permitted to scan program and data files used by Oracle.
• Using inappropriate parameters for memory allocations.
• If a database release upgrade was involved, not directly attacking the specific performance problem which is the source of the problem – could just be just a couple bad execution plans, possibly caused by the upgraded query optimizer.
• … (Niall Litchfield, or someone else might be able to provide additional causes)

A couple things to keep in mind about Windows:

• Oracle on Windows uses direct, asynchronous I/O, which helps minimize concurrency problems. But, this also means that the operating system’s file cache probably is of little use to Oracle on Windows.
• Oracle on Windows uses a thread model, while Oracle on Linux (and other Unix like operating systems) uses a process model. The thread model is less negatively impacted by context switches than is the process model, which should help improve performance in some cases, such as repeatedly calling a PL/SQL function from a SQL statement.
• Windows uses a single CPU run queue, while the Linux 2.6 and later kernel uses a separate run queue for each CPU. That means that processes (and threads) tend to float between CPUs more frequently on Windows than on Linux, which may reduce the benefit of large L1 and L2 caches built into the CPUs on Windows. The Linux 2.6 kernel is also able to automatically throttle very CPU intensive processes, which might provide a little more headroom for multiple concurrent Oracle sessions.
• There is a paper on Microsoft’s website titled “Performance Tuning Guidelines for Windows Server 2003” http://download.microsoft.com/download/2/8/0/2800a518-7ac6-4aac-bd85-74d2c52e1ec6/tuning.doc (edit Feb 22, 2011: note that the preceding link is for Windows 2003, use this link for the Windows 2008 version of the document) – take a look at that document, but do not implement the suggested TcpAckFrequency Windows registry modification.
• Try to minimize the amount of memory used by the operating system for file caching.
• Properly configured, the same server running 64 bit Windows should be able to achieve roughly the same performance as the same server running 64 bit Linux (assuming direct, asynchronous I/O is enabled on Linux).
• … (Niall Litchfield, or someone else might be able to provide additional things to keep in mind)

The general advice that you will likely receive is to use the operating system that you know best for Oracle.

Niall Litchfield as well as several others provided responses in the above OTN thread – it is an interesting read.

—–

Late addition July 22, 2010:

Another OTN forum thread that I recently participated in:

When I tested it [Windows XP Home running Oracle Database] a while ago (maybe a year or two in the past) certain features did not work.  Because there are certain Oracle Database features that require the ORA_DBA security group and the Local Security Policy control panel, neither of which exist in Windows XP Home.  If you are able to get Oracle Database installed and a database created (I was able to do both), you might encounter the limitations or you might not depending on which features you use.  This is what I recall from the testing:

• “CONNECT / AS SYSDBA” will not work – the logged in user must be in the ORA_DBA group which does not exist
• Datapump import/export does not work.
• Cannot schedule jobs using Enterprise Manager Database Control – the OS user specfiied must have the LOGON AS BATCH permission which is assigned in the Local Security Policy control panel (I experimented with borrowing this control panel item from XP Pro but it refused to run on XP Home).
• RMAN from the command line works to backup and restore databases, but of course it is not possible to schedule the RMAN backups using Enterprise Manager Database Control.
• File level permissions cannot be adjusted without removing the hard drive and installing it as a secondary drive in a Windows XP Pro (or higher) computer, at which point the user logged into the Windows XP Pro computer will need to take ownership of the files before modifying file permissions.

I suspect that there might be problems with UTL_FILE, BFILE datatypes, and some of the other features.

I did not perform much testing.  When one considers the time involvement, the Windows Anytime upgrade that I mentioned earlier is the least expensive solution.

—–

Back to the Oracle-L thread… shared or dedicated?