Oracle on Windows

I just wanted to let you know that I am finalizing the last bits of my latest book; Oracle VM Implementation and Administration Guide. You can use Oracle VM to host both Windows and Linux guest machines. The book (according to Amazon) should be out in early August. Enjoy. Ed W.

I was recently asked if Oracle 10.2.0.5 was available for CRS for Windows. At first I started looking up to see if 10.2.0.5 had CRS components, and then I decided to ask about the end goal. It turns out that the final goal was to install a new RAC cluster with the 10.2.0.5 database.

With this new information in mind I immediately recommended that the 11gR2 Grid Infrastructure be installed. This provides the most stable and robust clusterware and ASM software and at the same time extending the lifetime of these components before they need to be upgraded.

In addition, by using the 11gR2 Grid Infrastructure both 10g and 11g RAC databases can be created. This will allow you to create an 11gR2 database for testing. Since the architecture of RAC has changed slightly in 11gR2 it is best to move to this structure for any new installations, even if a 10g database is the end result.

So, what has changed? In Oracle 10g and 11gR1 there were three separate components and/or Oracle homes that were used for RAC; the CRS, the ASM home and the database home. In 11gR2 the clusterware and ASM homes are combined into the Grid Infrastructure and have one home. In addition, the confusion as to which listener to use is clarified by having the cluster, ASM and master listener run out of the Grid Infrastructure. Now, instead of three Oracle homes, there are two; Grid Infrastructure and database.

So, if you are planning any new RAC installations on Windows, do yourself a favor and use the 11gR2 Grid Infrastructure, even if you are installing a 10g database.

Oracle RAC and the Parallel Query Option can be a powerful combination, but if you don’t entirely understand how it works; it might not be doing exactly what you think it is. The parallel query option allows some long running operations such as table scans to be divided into smaller chunks and run by multiple server processes. Since operations such as table scans are very I/O intensive, this essentially allows other processes to be doing work on your behalf while some of them are simply waiting on the I/O to return. Let’s look at an example.

Let’s say that you have a table scan that is going to read 1,000,000 blocks. Furthermore, ignoring all disk caching, driver optimizations, etc. let’s assume that each physical I/O takes 2ms. So, in order to read this entire table into the SGA it will take 1,000,000 I/Os x 0.002 sec/I/O = 2,000 sec which is 33.3 minutes. So, it will take a half an hour (plus overhead) just to read the data into the SGA. Most of the time which is spent idle waiting on the I/O to return.

Now let’s turn on parallel query with a parallelism of 10. Oracle will internally divide this 1,000,000 block table into multiple pieces and pass those pieces off to the 10 parallel query processes. If you are using anything other than a single SATA drive for your database the I/O subsystem can easily handle the parallelism and will now return that same 1,000,000 blocks in 1/10^th of the time, or 3.33 minutes.

Note: I prefer to have control over my parallelism, so I will use the parallel hint to invoke parallel query as such; SELECT /*+ PARALLEL(table_alias, degree) */ FROM table;

Taking it up a notch, now let’s consider that the operations are consuming all of the CPU in the system. I can now additionally add nodes to use in a RAC cluster for parallel query, thus taking advantage of parallelism and RAC. This can be done by extending the hint to include the number of nodes to use in a parallel query operation as such; /*+ PARALLEL(table_alias, degree, instances) */ FROM table; This will allow me to specify not only the number of query slaves to use, but the number of instances to use as well.

This is only part of the story. Now it is necessary to specify whether we are talking about 10g or 11g. Let’s start with Oracle 10g.

Parallel Query and Oracle 10g RAC

With Oracle 10g, the number of RAC nodes that will participate in a parallel query operation depends on a couple of parameters; instance_groups and parallel_instance_group. The instance_groups parameter is used to set up instance_groups and the parallel_instance_group parameter is used to specify which group you belong to.

First, set up the instance groups. In this example I have a four node RAC cluster:

INSTANCE_GROUPS

Assign the node a set of instance groups that it participates in

• node1.instance_groups = node1,first_two,all_nodes
• node2.instance_groups = node2,first_two,all_nodes
• node3.instance_groups = node3,last_two,all_nodes
• node4.instance_groups = node4,last_two,all_nodes

The following graphic illustrates the configuration.

Setting the parallel_instance_group parameter will define which group you belong to, thus defining where your parallel queries can run.

For example, if I set my parallel_instance_group parameter to Node1, my parallel queries will only run on node 1 as shown here:

Setting the parameter parallel_instance_group in the init.ora file will specify the default value for each user who logs into that instance. An alter session command can be used to modify that parameter as shown here: SQL> alter session set parallel_instance_group=all_nodes;

This will allow the parallel query to span all nodes in the RAC cluster.

Note: You don’t have to be on the node that parallel query is configured to run on. If you happen to log onto node 3 and your parallel_instance_group is Node1 non-parallel queries will run on node 3, but any parallel queries will run on node 1.

Parallel Query and Oracle 11g RAC

So, what about Oracle 11g? If you are running on Oracle 11g forget everything I just told you. One of the best features about Oracle 11g is that it now uses services to specify which nodes your parallel query runs on. So, depending on which service you connect to, the preferred nodes for that service are the nodes that parallel queries will use.

Conclusions

Depending on the operation that you are doing, you might be able to achieve huge performance gains by parallelizing across multiple nodes in a RAC cluster. Of course your mileage might vary, and if you aren’t careful you might increase the load on nodes to a point where you are affecting other users. I have a saying that “If you aren’t careful with parallelism, you might paralyze your system.”

Microsoft recently introduced Hyper-v with Windows 2008 Server. In order to determine the overhead of running Oracle on Hyper-v we performed a benchmark on identical hardware using both Hyper-v and a non-virtual server using Oracle 10g for Windows. The resulting whitepaper is available for download from our website: www.perftuning.com.

With Windows Server 2008, everything needed to support database server virtualization is available as an integral part of the operating system – Windows Server 2008 Hyper-v. This whitepaper demonstrates the performance capabilities of Oracle 10g on Hyper-v. It also has provides several best practices and resources essential to virtualization of Oracle database workloads.

Please visit our website and take a look.

I’ve mentioned several times that I strongly recommend running Oracle for Windows using 64-bit. I’ve had a number of questions about this, so I wanted to clarify the importance of 64-bit Oracle on Windows. If you look back, you will see that this is the subject of my first blog back in May of 2006.

Oracle on Microsoft Windows is unlike any other platform that Oracle supports. When Oracle was originally ported to windows, the decision was made to take advantage of Windows threads. A thread, sometimes known as a lightweight process shares the same context as the calling process, thus the context switch is replaced by a thread switch (which uses much less CPU resources). With Oracle for windows, all traditional processes (background and server) are implemented as threads. This provides for more efficient processing, but causes another problem.

Within the thread model, all threads within a process share the same virtual memory address space. With Windows 32-bit, this virtual address space is 3 GB in size (with /3GB flag, 2 GB otherwise). Thus all of the Oracle processes share the same 3 GB virtual memory limit. In a process environment such as Unix, each process has its own 3 GB virtual memory address space. This limitation can cause the virtual memory space to be completely used, thus causing failures. The symptom of this in 32-bit Oracle is a failure to allocate memory for new dedicated server processes.

This problem has been solved with the 64-bit version of Oracle for Windows. The 64-bit version of Oracle for Windows supports a 16 Terabyte virtual memory limit. In addition, memory above 4 GB can be accessed directly, indirect data buffers need not be used. This makes the use of memory above 4 GB much more efficient and faster.
When implementing Oracle with an SGA that is more than 4 GB in size I always recommend the use of large memory pages. Large memory pages are much more efficient for large SGAs and makes a huge performance difference, but that’s going to be the subject of my next blog. For information on how to use large memory pages in Oracle on Windows see Metalink note 422844.1.

The limitations in this article lead me to strongly recommend the use of 64-bit Oracle on 64-bit Windows. So, "Just say no, to Oracle on 32-bit Windows".

It's been a while since I've added anything to this block. I guess I just got busy with other things. Well, I'm going to try to change that. First, I'm going to be at IOUG Collaborate 2008 and I am giving a session on "Advantages of Running Oracle 11g on Microsoft Windows". I hope to see you there. Second, I'm soliciting topics for future blogs. Please reply to this post with ideas and I will take them under considerations. Questions are also fine, I'll answer them if I can.

Labels: oracle windows

I recently had one of my blog readers ask me for some advice on some new hardware that he was going purchase for running Oracle on Windows. It is difficult to give specific advice on what to purchase, but I can provide a few general guidelines.

1. Get something that is expandable. If you don’t need 4 CPUs now you can get a system capable of supporting 4 CPUs, but only purchase one or two. Make sure that you can add sufficient memory as necessary. Start with 2 or 4 GB but make sure that there are free slots in case you need to add more.
2. If you will be running Oracle 10g, absolutely go 64-bit. Any recent Xeon or Opteron processor supports 64-bit Windows. The 64-bit version of Windows 2003 works great and is priced similar to the 32-bit version.
3. Get a name brand. HP, IBM, Dell, etc. Get something that is supported by the manufacturer.
4. If possible, separate the application tier from the database tier.
5. Get sufficient disk drives. I'm not saying that you need to start with 8, but you need enough so that IO performance is not a problem. How many do you need? I can't tell you without knowing more about the database and application, etc.
6. Absolutely use a RAID controller and disk mirroring (RAID 1). If you lose your data, you are out of business.
7. In larger systems I recommend separating the log drives and data drives, since the loss of one of the two is a recoverable failure. The loss of both is catastrophic.
8. If you don't already have one, get something to back up your database with. This can be tape, DVD, etc..
9. Get trained, get some books, etc.. I'm trying to convince Oracle Press to let me do an Oracle on Windows book.

I hope that this is helpful. If you have any comments or a suggestion for a future topic, drop me an email at ewhalen@perftuning.com.

There has been much discussion as to whether Oracle on Windows or Oracle on Linux is a better platform. My opinion has always been that if you are choosing between the two, the platform that better fits into your environment is the better choice. If you are a windows shop and have extensive expertise on Windows and no Linux experience it doesn't make much sense to put a foreign Operating System into your data center. On the other hand, if you are a Unix only shop, and have no in-house Windows expertise, Linux might be a better choice.

In addition, I feel that the way Oracle had developed the Oracle Database Server for Windows using the threading model would turn into an asset once 64-bit Windows is adopted. Remember that 32-bit Oracle on Windows suffers from virtual memory issues that are solved with 64-bit Windows. Thus the liability of using the thread model has turned into an asset (see previous blog).

In order to dispel any rumors or conjecture on whether Windows or Linux works better on the same hardware we recently ran a comparison. This comparison was done using the SwingBench tool. The result of this comparison is provided in a white paper which we have just published on our website. In order to get to this whitepaper follow this link.

Choosing the best OS for your environment involves more than just the performance of the database server. The Oracle Database Server on Windows provides compatibility with Active Directory and your entire integrated environment.