Oracle on Windows

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.

I have been asked the question "Does Oracle support NAS on Windows". Officially Oracle does not support native NAS devices per-se on Windows. I am speaking specifically of the CIFS protocol. This is not true for all platforms; Oracle does support NAS on Unix with NFS, but not on Windows. However, with the introduction of iSCSI Oracle does now support NAS on Windows.

The iSCSI protocol uses embedded SCSI commands within IP packets. This allows for storage to be accessed over the network, but at the same time take advantage of a proven storage protocol. The main benefits of iSCSI is that it is cheaper and potentially easier to use than the fibre channel equivalent.

In June of 2003 Microsoft released the iSCSI initiator and driver for Windows 2003. In addition, Microsoft has qualified a number of storage vendor’s products for use with Windows 2003. At the same time, Microsoft has introduced Windows Storage Server 2003, which allows a Microsoft Server to become the iSCSI NAS.

With iSCSI Oracle on Windows is completely supported as both a stand-alone Oracle database server or an Oracle RAC server. Yes, Oracle does support NAS on Windows.