All good things come to an end

After a more than seven great years at Oracle Corporation I have decided to resign and pursue new opportunities. In July I started to work for Microsoft Corporation.

Disabling CPUs on Linux- Part Two

If the server is supporting Hyperthreading, Linux implements the following mechanism:

• First all the physical CPU's are enabled and then
• then the logical CPU's are enabled

This means that all the possible combinations can be enabled by specifying the maxcpu parameter in the boot command mentioned in the earlier post. For instance if the server has more than one physical CPU it is not possible to test one physical CPU with Hyperthreading enabled.

It goes without saying that it is necessary to be careful when limiting the number of CPUs to always create symmetric configurations. What this means is that should be avoided that some physical processors have Hyperthreading enabled and others not. This can can lead to very difficult to analyze queueing effects and perturbation.

Granularity of CPU statistics in V$SERVICE_STATS

After making several tests it turns out that the "DB CPU" statistics in the view V$SERVICE_STATS only has a granularity of centiseconds despite what the documentation says.

Disabling CPUs on Linux - Part One

For running performance tests and to determine the potential benefit of additional CPUs it can be very helpful to be able to disable CPUs. CPU's can be disabled by adding the following clause to the kernel configuration line in the grub.conf configuration line:

maxcpus=<NUM_CPUS>

The placeholder NUM_CPUS has to be replaced with the number of CPUs that will be enabled. After re-booting with the correct kernel verify that the correct number of CPUs are active. This can be verified with the file /proc/cpuinfo.

Disabling Hyper-Threading

The use of Hyper-Threading for server applications is questionable, especially if the number of I/O requests is high. To determine the cost or the potential performance improvements it makes sense to disable the Hyper-threading (from known on abbreviated with HT) support. The are two ways to disable HT:

• Disable Hyper-Threading in the kernel
• Disable Hyper-threading by the BIOS

Intel recommends to disable Hyper-Threading in the BIOS as it is more cleanly to disable at that point. To disable it in the BIOS look for a description logical CPU (this how it is called for Dell servers) or something similar.

The other option is to specify the noht switch in the kernel line of the grub configuration file. Unfortunately this doesn't work with Redhat AS 4.

To check if HT is really disabled check the file /proc/cpuinfo. This file should only report the physical CPU's.

Upgrade to RedHat AS 4.0

Upgrading to RedHat 4.0 introduces some new challenges. With the switch to the 2.6 linux kernel the udev device filesystem was introduced. By default the udev device tree does not support raw devices anymore. Luckily it is still supported with the Redhat distribution. It can be configured in the old way with the rawdevices service configuration.

To check if it is currently enabled run the following command:

# chkconfig --list

and search for rawdevices. If it is not enabled yet add the rawdevices service with

# chkconfig --level 345 rawdevices on.

Afterwards start the service in the usual way with service start rawdevices.

The next step is to set the permissions correctly. especially for Oracle database files, OCR and the voting disks it is important to set the correct file permissions. Setting the permissions on the raw devices directly will only work partially. As soon as the node gets rebooted the raw devives will be reset tohave root privileges. To make the change final it is necessary to edit the file /etc/udev/permissions.d/50-udev.permissions. Search for the substring "raw" in this file. You will find something like

raw/*:root:disk:0660

and change the line to

raw/*:oracle:dba:0660

For the future it makes sense to no longer use /dev/raw. There are plans to completely de-support it with kernel version 2.7.

Determining the IP address of the cluster interconnect in 9i

In Oracle database version 9i there is no way to determine the IP address through a database view. The only way to determine this IP address is with the oradebug command (Please keep in mind oradebug is not a supported product from Oracle, so if there are problems/crashes you are on your own). The oradebug ipc command creates a trace file. The example shows the process:

 SQL> oradebug setmypid

  SQL> oradebug ipc

  SQL> oradebug tracefile_name

The last command will show the name of the created trace file. The next step is opening the trace file and looking for the string SKGXPCTX. The following is a typical example.

SKGXPCTX: 0xcd1e730 ctx

 admno 0x7a0be402 admport:

 SSKGXPT 0xcd1e884 flags SSKGXPT_READPENDING     info for network 0

         socket no 8     IP 140.87.79.67         UDP 9152

         sflags SSKGXPT_WRITESSKGXPT_UP

         info for network 1

The cluster interconnect IP address is shown in bold. In the example above
it is 140.87.79.67.

How to determine SQL statements that cause hard parses

From a tuning point of view hard parses can be quite limiting to the scalability of database and an application. If the number of hard parses is high this is a serious problem. To tackle the problem the first step is to determine which SQL statements are causing hard parses. With the column FIRST_LOAD_TIME of the view V$SQL this can be determined. The following example shows how this knowledge can be used. It reports all statement that have been hard parsed in the last ten minutes

SELECT first_load_time, sql_text 

   FROM v$sql 

  WHERE first_load_time > to_char(sysdate-1/(24*6), 'YYYY-MM-DD/HH24:MI:SS')

Keep in mind that not necessarily each hard parse is bad. Actually every statements needs at least one hard parse. But if you see on the other hand a lot SQL statements that are not using bind variables the problem at hand is obvious.

Deleting the wrong cluster interconnect information from the OCR

The current configuration of the cluster interconnect information can be checked with the oifcfg command. The following example shows this:

$ oifcfg getif
 eth0  142.2.166.0  global  public
 ib0   192.169.1.0  global  cluster_interconnect

Let's assume that the address for the cluster interconnect was specified incorrectly. This will prevent cluster communication. The situation can be resolved by deleting the cluster interconnect IP from the OCR configuration. With the command below the interconnect IP address gets deleted.

$ oifcfg delif -global ib0

Afterwards the configuration can be verified for correctness.

$ oifcfg getif
 eth0  142.2.166.0  global  public

Now everything looks fine. The correct interconnect information can now be added with the oifcfg setif command.

Determining what IP address was configured in the OCR

In 10gR1 the IP address for the cluster interconnect is determined by default from the OCR (Oracle Cluster Repository). The configured address can be determined with the following command:

# oifcfg getif

 eth1  140.87.81.0  global  cluster_interconnect

If the database parameter cluster_interconnects is specified, the value that is obtained from OCR is not used.