D
Tuning for Oracle9i on Tru64

Requirements to Run the Directed Placement Optimizations

The system must meet the following requirements for Oracle9i directed placement optimizations to work:

• The system must be a Compaq GS80, GS160, or GS320 AlphaServer or similar locality sensitive Compaq system. The Oracle9i optimizations only affect systems that are locality sensitive.

• The operating system must be Compaq Tru64 UNIX V5.1 or higher. Previous operating system versions do not include the required operating system support for Oracle9i to perform directed process and memory placement.

Enabling Oracle Directed Placement Optimizations

To enable Oracle directed placement optimizations, follow these steps:

1. Shut down the Oracle instance.

2. Relink the Oracle server by entering the following commands:

   $ cd $ORACLE_HOME/rdbms/lib
   $ make -f ins_rdbms.mk numa_on
   $ make -f ins_rdbms.mk ioracle
   
   

If you are not using a compatible version of Tru64 UNIX, the following message is displayed:

Operating System Version Does not Support NUMA.
Disabling NUMA!

If you enable Oracle directed placement optimizations, and later change Tru64 to an incompatible version, disable Oracle directed placement optimizations as described in the following section.

Disabling Oracle Directed Placement Optimizations

To disable Oracle directed placement optimizations, follow these steps:

1. Shut down the Oracle instance.

2. Relink the Oracle server using the numa_off option:

   $ cd $ORACLE_HOME/rdbms/lib
   $ make -f ins_rdbms.mk numa_off
   $ make -f ins_rdbms.mk ioracle
   
   

Using Oracle Directed Placement Optimizations

The Oracle directed placement optimizations assume an equi-partitioned configuration. This means that all RADs are configured with the same number of CPUs and the same amount of memory. The Oracle server is assumed to run across all RADs on the system.

Oracle Initialization Parameters

To make the most efficient use of the local environment, Oracle9i adjusts some initialization parameters automatically depending on the server configuration as reported by the operating system. This practice eliminates common errors in correctly computing subtle dependencies in these parameters.

Tru64 UNIX System Parameters

You must set the system parameters in the following table to realize the full benefits of a NUMA system:

There are 63 rad_gh_regions parameters in the vm subsystem in Tru64 V5.1. Set only the parameters for the total number of RADs on the system. For example, if there are 4 RADs on the system (a GS160) and the SGA size is 10 GB, then set rad_gh_regions[0], rad_gh_regions[1], rad_gh_regions[2], and rad_gh_regions[3] to 2500. Note that you might have to raise this value slightly to 2501 or 2502 to successfully start the instance.

If CPUs and memory are taken off-line, Oracle9i continues to function, but loses performance. If you anticipate frequent off-lining of RADs or equi-partitioning is not feasible, Oracle Corporation recommends running Oracle9i Real Application Clusters, using one instance per RAD. Using Oracle9i Real Application Clusters, you can configure individual instances with different sets of initialization parameters to match the actual RAD configuration. You can also start up or shut down specific instances without affecting overall application availability.

Process Affinity to RADs

You can improve performance by directing the operating system to run the processes on specific RADs. If connections to the database are made through the Oracle Listener process, and there is a corresponding network interconnect adapter on the RAD, you can run a listener on each RAD. To run the listener on a particular RAD, enter the following command:

$ runon -r lsnrctl start [listener_name]

All Oracle shadow processes are automatically created on the same RAD as the Oracle listener.

Reliable Data Gram

Reliable Data Gram (RDG) is an IPC infrastructure for the Tru64 TruCluster platform. It is the default IPC method for Oracle9i on Tru64 and is optimized for Oracle9i Real Application Clusters environments.

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
$ make -f ins_rdbms.mk ioracle

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk ipc_udp
$ make -f ins_rdbms.mk ioracle

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
$ make -f ins_rdbms.mk ioracle

CLUSTER_INTERCONNECTS Initialization Parameter (Formerly TRU64_IPC_NET)

In Oracle9i release 2 (9.2.0.1.0) on Tru64 UNIX, the TRU64_IPC_NET parameter is replaced by the CLUSTER_INTERCONNECTS parameter. This parameter requires the IP address of the interconnect instead of the device name. The CLUSTER_ INTERCONNECTS parameter allows your system to specify multiple IP addresses. Oracle9i Real Application Clusters traffic is distributed between all of the specified IP addresses.

The CLUSTER_INTERCONNECTS parameter is useful only if Oracle9i Real Application Clusters and UDP IPC are enabled. They enable users to specify an interconnect for all IPC traffic that includes Oracle GCS, GES, and IPQ.

Use the CLUSTER_INTERCONNECTS parameter when the Memory Channel interconnect is overloaded. Overall cluster stability and performance might improve when you force Oracle GCS, GES, and IPQ over a different interconnect by setting the CLUSTER_INTERCONNECTS parameter. For example, to use the first fiber distributed data interface (FDDI) network controller whose IP address is 129.34.137.212 for all GCS, GES, and IPQ IPC traffic, set the CLUSTER_INTERCONNECTS parameter as follows:

CLUSTER_INTERCONNECTS=129.34.137.212

Use the /usr/sbin/ifconfig -a command to display the IP address of a device. This command provides a map between device names and IP addresses. To determine the IP address of a device, enter the following command:

$ /usr/sbin/ifconfig -a 
fta0: flags=c63<UP,BROADCAST,NOTRAILERS,RUNNING,MULTICAST,SIMPLEX>       inet 129.34.137.212 netmask fffffc00 broadcast 129.34.139.255 ipmtu 1500lo0:  flags=100c89<UP,LOOPBACK,NOARP,MULTICAST,SIMPLEX,NOCHECKSUM>       inet 127.0.0.1 netmask ff000000 ipmtu 4096 mc0:  flags=1100063<UP,BROADCAST,NOTRAILERS,RUNNING,NOCHECKSUM,CLUIF>       inet 10.0.0.1 netmask ffffff00 broadcast 10.0.0.255 ipmtu 7000 sl0:  flags=10<POINTOPOINT> tun0: flags=80<NOARP>  

In the preceding example, device fta0: has an IP address of 129.34.137.212 and device mc0: has an IP address of 10.0.0.1.

Remember the following important points when using the CLUSTER_INTERCONNECTS initialization parameter:

• The CLUSTER_INTERCONNECTS parameter is used only when UDP is enabled as the IPC implementation.

• The IP addresses specified for the different instances of the same database on different nodes should belong to network adaptors that connect to the same network. If you do not follow this rule, internode traffic may pass through bridges and routers or there may not be a path between the two nodes at all.

• Specify the CLUSTER_INTERCONNECTS parameter in the instance-specific initialization parameter file. Do not specify the CLUSTER_INTERCONNECTS parameter in the common initialization parameter file because the devices on different nodes connected to the same network have different IP addresses.

• If you specify multiple IP addresses for this parameter, list them in the same order for all instances of the same database. For example, if the parameter for instance 1 on node 1 lists the IP addresses of the alt0, fta0 and mc0 devices in that order, the parameter for instance 2 on node 2 should list the IP addresses of the equivalent network adaptors in the same order.

• If the interconnect device or IP address specified is incorrect or does not exist on the system, Oracle9i uses the default cluster interconnect device. On Tru64 UNIX V5.1, the default device is mc0. On Tru64 UNIX V5.1A and above, the default device is ics0.

  Oracle9i does not confirm which device is being used. To determine the IP address of the cluster interconnect device being used, perform the following steps:

  1. Enter the following command:

     $ /usr/sbin/clu_get_info
     
     

  2. In the output for this command, identify the cluster interconnect IP name, stored in the Hostname parameter, and the cluster interconnect address, stored in the Cluster interconnect IP address parameter. In the following example, the cluster interconnect IP name is server1 and the address is 10.0.0.1:

  3. Information on each cluster member Cluster memberid = 1 Hostname = server1.employee.records Cluster interconnect IP name = server1-mc0 Cluster interconnect IP address = 10.0.0.1 Member state = UP Member base O/S version = Compaq Tru64 UNIX V5.1 (Rev. 732) Member cluster version = TruCluster Server V5.1 (Rev. 389) Member running version = INSTALLED Member name = server1 Member votes = 1 csid = 0x20002 
     

aio_task_max_num Parameter

Set the aio_task_max_num operating system parameter for a single instance to the higher of the following:

• Greater than the maximum of the DBWR I/O operations

• The value of the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter

The maximum number of DBWR I/O operations defaults to 8192.

You should adjust the setting of the aio_task_max_num parameter to accommodate any other applications that use asynchronous I/O, including multiple Oracle9i instances on a single node. Set the value of the parameter to the maximum number of I/O requests that any application can issue. For example, if three applications are running on a system and application one can issue a maximum of 10 simultaneous asynchronous I/O requests, application two can issue 100 simultaneous asynchronous I/O requests, and application three can issue 1000 simultaneous asynchronous I/O requests, you should set the aio_task_max_num parameter to at least 1000.

If you do not set the aio_task_max_num operating system parameter as described in this section, the performance of Oracle9i is reduced and spurious I/O errors might occur. These errors are stored in the alert log and trace files.

Single Instance Requirements

Oracle9i has the following requirements for single instance installations:

• Tru64 UNIX V5.1 or later with the appropriate patchkits.

  
  

  See Also: Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems for information on Tru64 patchkits.

  
  

• Oracle datafiles stored on a Tru64 UNIX AdvFS file system.

• The disks that use the AdvFS file system must be physically connected to the computer running the Oracle9i instance. This includes disks attached by fiber channel. This specifically excludes cases where I/O must be served by another node because of a lack of physical connectivity.

On Tru64 UNIX V5.1 systems and higher in a non-clustered system environment, the AdvFS file system and direct I/O give almost all of the performance of raw devices because the file system cache is not used. In addition to this, the file system allows you to more easily manage the database files.

Clustered Systems

On V5.1 systems and higher, Tru64 supports Clustered File Systems (CFS). CFS provides a single namespace file system for all nodes in a cluster. All file systems mounted in a cluster are automatically seen by all nodes in the cluster. Because it is layered on top of the AdvFS file system, the CFS file system inherits much of the characteristics of non-clustered systems.

Tru64 UNIX V5.1 Clustered Systems

Oracle Corporation supports CFS only on Tru64 UNIX V5.1 or later because this file system now supports a concurrent direct I/O model. Any node that has physical connectivity to a drive can issue data I/O to its file systems without consulting with the owning node.

All metadata changes to a file, for example extending, closing, changing the access or modification date, are still served by the owner node and can still cause cluster interconnect saturation. Therefore, it is possible for the CREATE TABLESPACE, ALTER TABLESPACE, ADD DATAFILE, ALTER DATABASE DATAFILE, or RESIZE commands to perform poorly on a CFS file system when compared to raw devices.

Multiple Instance Requirements (Oracle9i Real Application Clusters)

Oracle9i Real Application Clusters requires that you store Oracle datafiles on the Tru64 AdvFS file system. The disks that use the AdvFS file system must be physically connected to all computers running the Oracle instances. This includes disks attached by fiber channel. It excludes cases where I/O must be served by another node because of physical connectivity.

If the database is running in archive mode and the archive logs are being written to disk, the destination AdvFS domain should be served by the node of the instance that is archiving the redo log. For example, if you have a three-node cluster with one instance on each node (nodea, nodeb, and nodec), you must also have three archive destination AdvFS domains (arcnodea, arcnodeb, and arcnodec). The domains should be served by nodea, nodeb, and nodec respectively and the LOG_ARCHIVE_DEST initialization parameter for each instance should specify their respective locations.

Disabling Direct I/O Support

An Oracle9i database running on an AdvFS file system with direct I/O support enabled should perform as well as an Oracle9i database running on raw devices. In most cases, an Oracle9i database that is stored on AdvFS volumes with direct I/O support enabled should perform the same as or better than the same database with direct I/O support disabled. However, the following workload attributes can reduce performance when direct I/O support is enabled:

• A high read to write ratio

• Oracle data blocks not cached in the SGA because the query utilizes parallel query slaves

• A Unix Buffer Cache (UBC) several megabytes or larger

• Full table scan queries where the same set of tables are scanned repeatedly

• Tables being scanned can fit in the UBC

When direct I/O support is disabled, workloads that have most of the attributes in the preceding list rely heavily on the UBC. Because most if not all of the tables being scanned are cached in the UBC, the I/O requests issued by the parallel query are met by the UBC. As a result, the query completes much faster than if all of the data had to be read from disk, as it would with direct I/O enabled. When direct I/O support is enabled, Oracle data blocks are not cached in the UBC. They are read into process-private memory instead. This means that any query that reads a previously-scanned table must perform I/O requests to disk to retrieve the data. Disk I/O latencies are several orders of magnitude slower than memory latencies. Therefore, the query runs slower and performance suffers. If your workload has most of the attributes described in the preceding list, disabling direct I/O support will probably improve performance. However, often there are many different types of queries running on the system at the same time. Some queries only read data while others insert, modify, or delete data and the ratio of the various types of queries differ from site to site. Generally, if your site has more of an OLTP workload, disabling direct I/O support does not improve performance. Direct I/O support is enabled by default in Oracle9i release 2 (9.2.0.1.0). The undocumented _TRU64_DIRECTIO_DISABLED initialization parameter that is used to disable direct I/O support in Oracle9i release 1 (9.0.1) is removed in Oracle9i release 2 (9.2.0.1.0). The generic FILESYSTEMIO_OPTIONS initialization parameter is used instead. The following table describes the valid values for the FILESYSTEMIO_OPTIONS parameter as interpreted on Tru64:

The DISK_ASYNCH_IO initialization parameter controls the asynchronous I/O state for all database files, whether they are on file systems or raw devices. Therefore, if the DISK_ASYNCH_IO parameter is set to FALSE, all I/O requests to file system files are synchronous regardless of the value of the FILESYSTEMIO_OPTIONS parameter. The DISK_ASYNCH_IO parameter defaults to TRUE.

Preventing File Fragmentation

Because of an interaction between direct I/O and file allocation, files created with direct I/O support enabled can become severely fragmented. Severely fragmented files cause performance degradation and can lead to I/O errors, especially during backups and recovery. Oracle9i release 2 (9.2.0.1.0) solves this problem by temporarily disabling direct I/O support during file creation and file extension. If direct I/O support is enabled, the new file or extended file is reopened with direct I/O support enabled after the file create or resize operation is complete. On a file resize operation, direct I/O support is not temporarily disabled if the new file size is smaller than the current file size. This does not cause fragmentation because the file or extent already exists.

Using Spike

This section describes the system resources required by Spike, how and why to use Spike optimization flags, and the various ways to run Spike.

Table D-2 lists the system resources required to run Spike.

Table D-2 System Resource Requirements for Spike

To set the value of these parameters in the /etc/sysconfigtab file, edit the following lines:

proc:

    max-per-proc-address-space = 0x40000000
    max-per-proc-data-size = 0x40000000

vm:

    vm-maxvas = 0x40000000

Set the limits in your shell environment to the highest values. For the C shell, enter:

% limit datasize unlimited
% limit memoryuse unlimited
% limit vmemoryuse unlimited

Spike can run out of virtual memory if the stacksize limit is set too high. To avoid this problem, enter the following C shell command:

% limit stacksize 8192

Spike provides a large number of optimization flags. However, you cannot use all spike command optimizations with Oracle9i. The following Spike optimization flags are certified to run with Oracle9i:

-arch, -controlOpt, -fb, -feedback, -map, -nosplit, -nochain, -noporder, -noaggressiveAlign, -o, optThresh, -splitThresh, -symbols_live, -tune, -v, -V

When you run Spike, it places a copy of the optimization flags in the image header comment section of the binary that you are optimizing. Oracle9i checks Spike optimizations used on itself at the beginning of instance startup. If Oracle9i detects an optimization not known to work for the Oracle9i binary, or if the binary had been previously optimized with OM (the predecessor to Spike from Compaq), the instance startup fails with an ORA-4940 error message. If the instance startup fails, check the alert log file for more information.

Use one of the following methods to optimize an executable using Spike:

• Static spiking

• Running Spike with feedback

Static spiking requires only a few set-up steps and yields approximately half the performance benefit possible compared to running Spike with feedback.

Running Spike with feedback includes all of the optimizations of static spiking plus additional optimizations that are workload-related. Running spike with feedback provides the best possible performance benefit, however, it requires considerably more effort than static spiking.

For both running Spike with feedback and static spiking, Oracle Corporation recommends running the spiked Oracle binary in a test environment before moving it to a production environment.

Static spiking performs optimizations that are not specific to your workload, such as manipulating the global pointer (gp) register and taking advantage of the CPU architecture. In a test environment, roughly half of the performance optimization gain possible from Spike was through static spiking. Furthermore, static spiking is relatively straight-forward and simple. The combination of simplicity and performance gain makes static spiking worth the effort.

Perform the following steps to use static spiking:

1. Shut down the database.

2. Spike the oracle image by entering the following command:

   $ spike oracle -o oracle.spike -symbols_live
   
   

3. Save the original image and create a symbolic link to the spiked image by entering the following commands:

   $ mv oracle oracle.orig
   $ ln -s oracle.spike oracle
   
   

4. Start up the database.

   
   

   Note: Before contacting Oracle for support, you must use the original image to reproduce any problems.

   
   

Running Spike with feedback performs all of the same optimizations as static spiking plus optimizations that are workload-related such as hot and cold basic block movement. In a test environment, approximately half of the performance optimizations gained from Spike was due to the optimizations that depend on feedback information. Running Spike with feedback requires multiple steps and considerably more effort than static spiking. However, performance sensitive customers may find the extra effort worthwhile.

Perform the followings steps to run Spike with feeback:

1. Instrument the Oracle binary by entering the following command:

   $ pixie -output oracle.pixie -dirname dir -pids oracle_image
   
   

   In the preceding example, oracle_image  is your original image and dir is the name of the directory into which the instrumented executable writes the profiling data files.

   
   

   Note: The -dirname option saves the oracle.Counts.pid files in the dir  directory. Because these files are large and may be numerous depending on the workload, make sure that the directory has enough disk space.

   
   

   This step also creates an oracle.Addrs file that is required later.

   The output of the pixie command might contain errors. You can safely ignore these errors.

2. Shut down the database.

3. Save the original image and create a symbolic link to the pixie image by entering the following commands:

   $ mv oracle oracle.orig
   $ ln -s oracle.pixie oracle
   
   

4. Start up the database and run your workload.

   You cannot run as many users as you can with the standard executable because the pixie executable is larger and slower. As you use the Oracle9i server, several oracle.Counts.pid files are created, where pid is the process ID for the corresponding Oracle process. Keep track of the process id of each Oracle process for which the optimization is aimed. These could be the shadow Oracle processes of the clients.

5. Shut down the database.

6. Create a symbolic link to replace the original executable by entering the following command:

   $ ln -s oracle.orig oracle
   
   

7. If you can identify one oracle.Counts.pid file as representative of your workload, perform step a. If you must merge several counts files together to better represent your workload, perform step b.

   1. Make sure that the oracle.Addrs file created by the pixie command, the oracle.Counts.pid files, and the original Oracle executable are available.

      Use the process id (pid) to pick a representative oracle.Counts.pid file and then copy it by entering the following command:

      $ cp oracle.Counts.pid oracle.Counts
      
      

   2. Use the prof utility to merge several oracle.Counts.pid files. See the prof man pages for more information on this utility.

      If you are using the parallel query option, merge the oracle.Counts.pid files generated by the query slaves and the query coordinator, which is the shadow Oracle process of the query-initiating client.

      If you are not using the parallel query option, merge the oracle.Counts.pid files from the Oracle foreground processes that use the most memory.

      To merge the oracle.Counts.pid files, enter the following command:

      $ prof -pixie -merge oracle.Counts $ORACLE_HOME/bin/oracle \
      oracle.Addrs oracle.Counts.pid1 oracle.Counts.pid2
      
      

8. Make sure that the oracle.Addrs and oracle.Counts files are available in the current directory, then run Spike using the feedback information by entering the following command:

   $ spike oracle -fb oracle -o oracle.spike_fb -symbols_live
   
   

   The output of the spike command might contain errors. You can safely ignore these errors.

9. Create a symbolic link to the new oracle image by entering the following command:

   $ ln -s oracle.spike_fb oracle
   
   

10. Start up the database.