Release 1 (9.0.1) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel and Sun SPARC Solaris
Part Number A90347-02
 Contents |
 Index |
| Oracle9i Administrator's Reference Release 1 (9.0.1) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel and Sun SPARC Solaris Part Number A90347-02 |
|
This appendix contains the following tuning information for Oracle9i on Tru64:
Oracle9i release 1 (9.0.1) runs only on Tru64 UNIX V5.0A or higher. This is because Compaq changed the size of the long double data type from 64 bits on Tru64 UNIX V4.0x to 128 bits on Tru64 UNIX V5.x. This change causes certain Oracle operations to perform with increased precision. One of these operations stores statistics in the data dictionary after a table or index is analyzed.
The query optimizer within the Oracle server uses the statistics stored in the data dictionary to determine how best to execute a query. If the stored statistics do not match the statistic calculated by the query optimizer while it searches for the best plan, the query optimizer might use the wrong plan to execute the query. This can cause the query to perform poorly or fail.
For this reason, each schema should have all object statistics analyzed after the upgrade. The ANALYZE_SCHEMA package analyzes the entire schema using either estimate or calculate statistics. The ANALYZE_SCHEMA package estimates statistics reasonably quickly depending on the number of rows or percentage of rows sampled but it only produces statistics as accurate as the amount of data sampled. The ANALYZE_SCHEMA package takes longer to calculate statistics but it analyzes every block in the table or index to produce extremely accurate statistics.
The GATHER_SCHEMA_STATS package performs the same functions as the ANALYZE_SCHEMA package but offers more flexibility. One of its features is the ability to save the current table or index statistics in a table in case the new statistics cause problems.
Oracle Corporation recommends that you use the same analysis and sampling method when gathering the new statistics as you used in the previous Oracle version.
This section describes Oracle9i Real Application Clusters on Tru64.
Reliable Data Gram (RDG) is an IPC infrastructure for the Tru64 TruCluster platform. It is the default IPC method on Tru64 in Oracle9i and is optimized for Oracle9i Real Application Clusters environments.
RDG requires that the node be a member of the cluster and connected through the memory channel. Oracle Corporation recommends that you set the node-wide operating system parameters listed in Table C-1 when using RDG.
In Oracle9i, RDG is the default IPC method on Tru64. When the Oracle9i Real Application Clusters option is enabled, the Global Cache Service (GCS), Global Enqueue Service (GES), Interprocessor Parallel Query (IPQ), and Cache Fusion use RDG. The User Datagram Protocol (UDP) IPC implementation is still available but you must enable it explicitly.
You must enable the Oracle9i Real Application Clusters option before enabling UDP IPC. To enable the Oracle9i Real Application Clusters option, use the Oracle Universal Installer or enter the following commands:
$ cd $ORACLE_HOME/rdbms/lib $ make -f ins_rdbms.mk rac_on $ make -f ins_rdbms.mk ioracle
For the Oracle IPC routines to use the UDP protocol, you must relink the oracle executable. Before performing the following steps, shut down all instances in the cluster.
To enable the UDP IPC, enter the following commands:
$ cd $ORACLE_HOME/rdbms/lib $ make -f ins_rdbms.mk ipc_udp $ make -f ins_rdbms.mk ioracle
To disable UDP IPC and revert to the default implementation for Oracle9i Real Application Clusters, enter the following commands:
$ cd $ORACLE_HOME/rdbms/lib $ make -f ins_rdbms.mk rac_on $ make -f ins_rdbms.mk ioracle
The TRU64_IPC_NET initialization parameter is useful only if the Oracle9i Real Application Clusters and UDP IPC options are enabled. These options enable users to specify an interconnect for all IPC traffic that includes Oracle GCS, GES, and IPQ. Use the TRU64_IPC_NET parameter when the Memory Channel interconnect is overloaded. Overall cluster stability and performance might improve when you force Oracle GCS, GES, and IPQ traffic over a different interconnect by setting the TRU64_IPC_NET parameter. For example, to use the first fiber distributed data interface (FDDI) network controller for all GCS, GES, and IPQ IPC traffic, enter the following:
TRU64_IPC_NET="fta0"
You can set this parameter to any interconnect that can be configured on a Tru64 UNIX system. If the specified interconnect device cannot be configured or does not exist, Oracle9i uses the default network device for the system.
To determine the default network device for a system, enter the following command:
$ grep NETDEV= /etc/rc.config | grep -v #
If the default network device for this system is ee0, the command displays the following line:
NETDEV_0="ee0"
Oracle9i for Tru64 systems can perform either synchronous or asynchronous I/O. To improve performance, Oracle Corporation recommends that you use asynchronous I/O. Set the DISK_ASYNCH_IO parameter to TRUE to enable asynchronous I/O.
Oracle9i can use asynchronous I/O on any datafiles that are stored on AdvFS file systems, cluster file systems (CFS), or raw devices. You must tune some operating system parameters for optimal asynchronous I/O performance.
Use the following formulas to determine the asynchronous I/O parameter requirements for a single instance. Note that the recommended setting of the parameters must be adjusted to accommodate any other applications that use asynchronous I/O, including multiple Oracle9i instances on a single node. The actual setting of each parameter is the sum of the requirements of all the Oracle9i instances plus the requirements of any other applications. The following tables lists the required real-time (rt) subsystem parameters on Tru64 systems:
If you do not set these operating system parameters, the performance of Oracle9i is reduced and spurious I/O errors might occur. These errors are stored in the alert log and trace files.
This section includes the following topics:
Oracle9i has the following requirement on single instance installations:
On Tru64 UNIX V5.0A 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.
On V5.1 systems and higher, Tru64 supports 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.
Oracle Corporation supports CFS only on Tru64 UNIX 5.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.
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.
Many Oracle processes are timed, especially if the TIMED_STATISTICS initialization parameter is set to TRUE. These timing functions call the Tru64 kernel and can affect Oracle9i performance. There is a feature in Tru64 that gives a process direct access to the real time clock. Using this feature improves performance more on a heavily loaded system than on a lightly loaded system.
To enable this feature:
root.
# mknod /dev/timedev c 15 0 # chmod +r /dev/timedev
If your system is a cluster running Tru64 UNIX V5.1 or higher, enter these commands once on each cluster. If your system is a cluster running an earlier version of Tru64, enter the commands on each node.
The existence of the /dev/timedev file is checked only on instance startup.
Oracle Corporation recommends that you enable this feature on all instances in a cluster, and therefore on all nodes.
To set up raw devices/volumes on Tru64 systems:
A free partition is one that is not used for a Tru64 file system and complies with these restrictions:
/usr/sbin/mount command.
To determine whether a partition is free, obtain a complete map of the starting locations and sizes of the partitions on the device and check for free space. Some partitions may contain file systems that are currently not mounted and are not listed in the /usr/sbin/mount output.
Begin by verifying that the disk is partitioned. If it is not, use the disklabel command to partition it.
ls command to view the owner and permissions of the device file. For example:
$ ls -1a
chown command to change the ownership on the block and character files for the device. For example:
# chown oracle /dev/rdisk/dsk10c
# chmod 600 /dev/rdisk/dsk10c
$ ln -s /dev/rdisk/dsk10c /oracle_data/datafile.dbf
To verify that you have created the symbolic link, use the character special device (not the block special device) and enter the following command:
$ ls -Ll datafile
The following message should appear:
crwxrwxrwx oracle dba datafile
From SQL*Plus, enter the following SQL command:
SQL> CREATE DATABASE sid 2 LOGFILE '/oracle_data/log1.dbf' SIZE 100K 3 '/oracle_data/log2.dbf' SIZE 100K 3 DATAFILE '/oracle_data/datafile.dbf' SIZE 10000K REUSE;
To add the partition to a tablespace in an existing Oracle database instead, enter:
SQL> ALTER TABLESPACE tablespace_name 2 ADD DATAFILE '/dev/rdisk/dsk10c' SIZE 10000K REUSE;
You can use the same procedure to set up a raw device for the redo log files.
The Spike optimization tool (Spike) is a performance optimization tool that increases the performance of a Tru64 binary. In a testing environment, Spike, with feedback, increased the performance of the Oracle9i server by up to 23 percent on an OLTP workload.
For information on Spike, see the Tru64 documentation or enter one of the following commands:
Oracle9i requires Spike version V5.1 (1.2.2.31.2.4 ADK) Feb 22 2001 or later.
Enter the following command to check the version of Spike:
$ spike -V
You can download the latest version of Spike from the following URL:
http://www.tru64unix.compaq.com/spike/
|
Note:
Oracle Corporation does not support versions of the Oracle executable optimized using the |
This section describes the system resource required by Spike, how and why to use Spike optimization flags, and the various ways to run Spike.
Table C-2 shows the system resources required to run 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 = 0x40000000vm: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:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 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 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:
oracle image by entering the following command:
$ spike oracle -o oracle.spike -symbols_live
$ mv oracle oracle.orig $ ln -s oracle.spike oracle
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:
$ pixie -output oracle.pixie -dirname dir -pids oracle_image
In the preceding example, oracle_image is your original image.
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.
$ mv oracle oracle.orig $ ln -s oracle.pixie oracle
You cannot run as many users as you could 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.
$ ln -s oracle.orig oracle
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.
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
prof utility to merge several oracle.Counts.pid files.
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
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.
oracle image by entering the following command:
$ ln -s oracle.spike_fb oracle
Compaq GS80, GS160, and GS320 systems consist of smaller building blocks called Resource Affinity Domains (RADs). A RAD is a collection of tightly coupled CPUs, memory modules, and an I/O controller coupled through a fast interconnect. A second-level interconnect connects each of the RADs together to form a larger configuration.
Unlike previous generation servers which have only one common shared interconnect between CPUs, memory, and I/O controller, GS80, GS160, and GS320 servers can offer superior performance and memory access times when a particular CPU accesses memory within its own RAD or uses its local I/O controller. Because of the switched interconnect, all I/O activity and memory accesses within one RAD do not interfere with those within another RAD. However, because memory accesses between a CPU and memory module located across RAD boundaries must traverse two levels of interconnect hierarchy, these memory references take longer relative to memory references that are within a RAD.
Directed memory and process placement support (available on Tru64 UNIX V5.1 and higher) allows sophisticated applications to communicate their specific needs for process and memory layout to the operating system. This communication results in greater performance through increased localization of memory references within a RAD.
Oracle9i includes enhanced support for the special capabilities of high performance servers such as the GS80, GS160, and GS320. Directed placement optimizations specifically take advantage of hierarchical interconnects available in GS80, GS160, and GS320 class servers. All previous generation servers have a single shared interconnect, so these servers neither directly benefit from directed placement optimizations nor is there any loss of performance on these servers. Therefore, these optimizations are disabled in Oracle9i.
The system must meet the following requirements for Oracle9i directed placement optimizations to work:
To enable Oracle directed placement optimizations, follow these steps:
$ 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.
To disable Oracle directed placement optimizations, follow these steps:
numa_off option:
$ cd $ORACLE_HOME/rdbms/lib $ make -f ins_rdbms.mk numa_off $ make -f ins_rdbms.mk ioracle
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.
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.
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.
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.
|
 Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|