D Administering Oracle Database on Tru64 UNIX

Requirements to Run the Directed Placement Optimizations

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

• The system must be an HP GS series, ES47 or ES80 AlphaServer or similar locality sensitive system. The Oracle Database optimizations only affect systems that are locality sensitive.

• The operating system must be Tru64 UNIX V5.1B or higher.

Enabling Oracle Directed Placement Optimizations

To enable Oracle directed placement optimizations, follow these steps:

1. Shut down the Oracle instance.

2. Relink Oracle Database 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 UNIX 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 Oracle Database 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. Oracle Database is assumed to run across all RADs on the system.

Oracle Initialization Parameters

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

Tru64 UNIX Subsystem Attributes

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

There are 63 rad_gh_regions attributes in the vm subsystem in Tru64 UNIX V5.1B. Set only the parameters for the total number of RADs on the system. For example, if there are 4 RADs on the system all of which are used by Oracle and the SEGSZ column total is 10248 MB with log_buffer set to 2097152 (2 MB), set rad_gh_regions[0] to 2568 and rad_gh_regions[1], rad_gh_regions[2], and rad_gh_regions[3] to 2560. To successfully start the instance, you might have to raise this value slightly, by 1 or 2.

If CPUs and memory are taken off-line, Oracle Database continues to function, but loses performance. If you anticipate frequent off-lining of RADs or equi-partitioning is not feasible, Oracle recommends running Oracle Real Application Clusters, using one instance per RAD. Using Oracle 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, where rad_number is the number of the RAD:

$ runon -r rad_number lsnrctl start [listener_name]

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

Restricting Oracle Database to a Subset of the Number of RADs on the System

You can restrict Oracle Database to run on a subset of the number of RADs on the system. When an instance starts, Oracle Database looks for the numa_config_sid.ora file in the $ORACLE_HOME/dbs directory. If the file does not exist, Oracle Database uses all of the RADs on the system. If the file exists, it instructs the instance to use only the RADs specified in the file, and to run processes on only the RADs specified.

The numa_config_sid.ora file has the following format:

number_RADs
group1
...
groupn

In this example, number_RADs is the number of processor groups or RADs that Oracle should use and group1 to groupn are the numbers of the RADs for Oracle to use.

Use the following guidelines when creating the file:

• Do not include any blank lines

• Include each number on a separate line

• Do not include anything else on the line

The RADs are numbered from zero to the maximum for the particular system. If the system is a fully integrated AlphaServer GS320, the RADs are numbered 0,1,2,3,4,5,6,7. For example, if you want to restrict Oracle Database to RADs 1 and 3 of a fully configured GS320, create a numa_config_sid.ora file similar to the following:

2
1
3

The group numbers do not have to be consecutive or in increasing numerical order.

Parallel query slaves and the shadow processes for bequeath connections are created on the RADs specified in the file. For remote TCP connections, you must bind the listener to the RADs explicitly, as described in the "Process Affinity to RADs" section.

Reliable Data Gram

Reliable Data Gram (RDG) is an IPC infrastructure for the Tru64 UNIX TruCluster platform. It is the default IPC method for Oracle Database on Tru64 UNIX and is optimized for Oracle 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

Simultaneous Enabling of Oracle Real Application Clusters and NUMA

Oracle certifies that Oracle Real Application Clusters and Directed Placement (NUMA) can run simultaneously. For most workloads on Alphaserver GS series, ES47, and ES80 servers, enabling the NUMA option on systems running Oracle Database 10g release 1 (10.1) and Real Application Clusters provides significant performance improvements.

aio_task_max_num Attribute

Set the aio_task_max_num kernel subsystem attribute for a single instance to 8193.

You should adjust the setting of the aio_task_max_num attribute to accommodate any other applications that use asynchronous I/O, including multiple Oracle Database 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 1 can issue a maximum of 10 simultaneous asynchronous I/O requests, application 2 can issue 100 simultaneous asynchronous I/O requests, and application 3 can issue 1000 simultaneous asynchronous I/O requests, set the aio_task_max_num parameter to at least 1000.

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

Single Instance Requirements

Oracle Database has the following requirements for single instance installations:

• Tru64 UNIX V5.1B or later with the appropriate patch kits.

  
  

  See Also: For more information about Tru64 UNIX patch kits, see the Oracle Database Installation Guide for UNIX Systems.

  
  

• Oracle data files stored on an AdvFS file system or in an ASM disk group.

• The disks that use the AdvFS file system must be physically connected to the computer running the Oracle Database 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.1B 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.1B systems and higher, Tru64 UNIX 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.1B Clustered Systems

Oracle supports CFS only on Tru64 UNIX V5.1B 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 (Oracle Real Application Clusters)

If you use the CFS file system for data file storage, Oracle Real Application Clusters requires that Direct I/O is enabled on your system. The disks that use the CFS 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 CFS 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 CFS 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

Oracle Database running on an AdvFS file system with direct I/O support enabled should perform as well as Oracle Database running on raw devices. In most cases, an Oracle 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 uses 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 with Oracle Database 10g. 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 Oracle Database 10g. 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 UNIX:

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.

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.

To set the value of these subsystem attributes in the /etc/sysconfigtab file, add 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 Oracle Database. The following Spike optimization flags are certified to run with Oracle Database:

-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. Oracle Database checks Spike optimizations used on itself at the beginning of instance startup. If Oracle Database detects an optimization not known to work for the Oracle Database binary, or if the binary had been previously optimized with OM (the predecessor to Spike from HP), 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 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 feedback:

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 there is enough free 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 Oracle Database, 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 about 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.