B
Tuning for Oracle9i on HP

This appendix contains the following tuning information for Oracle9i on HP:

HP-UX Shared Memory Segments for a 64-Bit Oracle Instance
HP SCHED_NOAGE Scheduling Policy
Lightweight Timer Implementation
Asynchronous I/O

HP-UX Shared Memory Segments for a 64-Bit Oracle Instance

When a 64-bit Oracle server creates a database instance, the server creates memory segments by dividing the available shared memory by the value of the HP-UX SHMMAX kernel parameter. For example, if 64 GB of shared memory is available for a single Oracle instance and the value of the SHMMAX parameter is 1 GB, the Oracle server creates 64 shared memory segments for that instance.

In laboratory tests, performance degradation occurs when an Oracle instance creates more than six shared memory segments. This is because each shared memory segment receives a unique protection key when the Oracle server creates the instance. There are six protection keys available for shared memory segments on the PA-RISC processor. If your system has more than six shared memory segments, the HP-UX operating system displays protection key faults.Oracle Corporation recommends that you set the SHMMAX parameter value to the amount of available physical memory on the system. Doing this ensures that the entire shared memory for a single Oracle instance is assigned to one shared memory segment and your instance needs only one protection key. To display the list of active shared memory segments on the system, enter the following command:

$ ipcs -m

If the Oracle server creates more than six segments for the instance, increase the value of the SHMMAX kernel parameter.

See Also:

Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems for more information on recommended kernel parameter settings.

HP SCHED_NOAGE Scheduling Policy

On HP, most processes use a time sharing scheduling policy. Time sharing can have detrimental effects on Oracle performance by descheduling an Oracle process during critical operations, for example, holding a latch. HP has a modified scheduling policy, referred to as SCHED_NOAGE, that specifically addresses this issue. Unlike the normal time sharing policy, a process scheduled using SCHED_NOAGE does not increase or decrease in priority, nor is it preempted.

This feature is suited to online transaction processing (OLTP) environments because OLTP environments can cause competition for critical resources. In laboratory tests, the use of the SCHED_NOAGE policy with Oracle9i increased performance by up to 10 percent in OLTP environments.

The SCHED_NOAGE policy creates little or no gains in decision support (DSS) environments because there is little resource competition in these environments. Because each application and server environment is different, you should test and verify whether your environment benefits from the SCHED_NOAGE policy.

Enabling SCHED_NOAGE for Oracle9i

To allow Oracle9i to use the SCHED_NOAGE scheduling policy, the group that the Oracle software owner belongs to (dba), must have the RTSCHED and RTPRIO privileges to change the scheduling policy and set the priority level for Oracle processes. To give the dba group these privileges, as the root user enter the following command:

# setprivgrp dba RTSCHED RTPRIO

To retain these privileges after rebooting, create the /etc/privgroup file, if it does not exist on your system, and add the following line to it:

dba RTSCHED RTPRIO

Add the HPUX_SCHED_NOAGE parameter to the initialization file for each instance, setting the parameter to an integer value to specify process priority levels. On HP-UX 11.0, the range is 153 to 255 and on HP-UX 11i, the range is 178 to 255. If the parameter setting is out of range, Oracle9i sets the parameter to a permissible value and continues with the SCHED_NOAGE policy with the new value. It also generates a message in the alert_sid.log file about the new setting. Oracle Corporation recommends that you set the parameter to the required priority level for Oracle processes. If the parameter setting is out of range, Oracle9i release 2 (9.2.0.1) sets it to a permissible value. It also generates a message about the new setting in the alert file. You should set the parameter value to achieve the process priority level that you want.

See Also:

The HP documentation, the rtsched(1)man page, and the rtsched(2) man page for more information on priority policies and priority ranges.

Lightweight Timer Implementation

Unlike Oracle9i release 1 (9.0.1), Oracle9i release 2 (9.2.0.1.0) on HP-UX systems uses the gethrtime() system library call whether the TIMED_STATISTICS initialization parameter is set to TRUE or FALSE. This enables you to collect run-time statistics at any time while running an Oracle instance. When the TIMED_STATISTICS initialization parameter is set to TRUE, Oracle9i release 1 (9.0.1.0) on HP-UX systems uses the system library call to calculate elapsed time.

In laboratory tests, this library call provides a performance improvement of up to 10 percent over an Oracle system not using the gethrtime() system library call when the TIMED_STATISTICS initialization parameter is set to TRUE. In addition, there is no negative impact on Oracle9i release 2 (9.2.0.1.0) OLTP performance while using the gethrtime() system library call when the TIMED_STATISTICS initialization parameter is set to FALSE.

See Also:

Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems for information on HP patches required for Oracle9i.

Asynchronous I/O

The asynchronous I/O pseudo-driver on HP allows the Oracle server to perform I/O to raw disk partitions using an asynchronous method, resulting in less I/O overhead and higher throughput. You can use the asynchronous I/O pseudo-driver on both HP-UX 9000 servers and workstations.

MLOCK Privilege

To allow Oracle9i to execute asynchronous I/O operations, the group that the Oracle software owner belongs to (dba) must have the MLOCK privilege. To give the dba group the MLOCK privilege:

As the root user, enter the following command:
```
# setprivgrp dba MLOCK 
```
To retain the MLOCK privilege after rebooting, create the /etc/privgroup file, if it does not exist on your system, and add the following line to it:
```
dba MLOCK 
```
Note:
If the Oracle software owner does not have the MLOCK privilege, Oracle9i on HP-UX generates trace files that include the following error:
Ioctl ASYNC_CONFIG error, errno = 1

Implementing Asynchronous I/O

If you want to use asynchronous I/O on HP, you must use raw partitions for database files. Use the System Administrator Management (SAM) utility to configure the asynchronous disk driver into the HP kernel.

To add the asynchronous disk driver and configure the kernel using the SAM utility:

Enter the following command as the root user:
```
# sam
```
Choose the Kernel Configuration area.
Choose the Drivers area.
Choose the asynchronous disk driver (asyncdsk).
Select Actions>Add Driver to Kernel.
Select List>Configurable Parameters.
Choose the MAX_ASYNC_PORTS parameter.
Select Action>Modify Configurable Parameter.
Specify a new value for the parameter, then choose OK.

The MAX_ASYNC_PORTS parameter is a configurable HP kernel parameter that controls the maximum number of processes that can open the /dev/async file simultaneously.

The system displays an error when a process tries to open the /dev/async file after the maximum number of processes have opened the file. This error can reduce performance on systems with a large number of shadow processes or many parallel query slaves performing asynchronous I/O. This error is not recorded. To avoid this error, estimate the highest likely number of processes that can access the /dev/async file and set the MAX_ASYNC_PORTS parameter to this value.
Choose Actions>Process a New Kernel.
Select one of the following options, then choose OK:
- Move Kernel Into Place and Shutdown System/Reboot Now
- Do Not Move Kernel Into Place: Do Not Shutdown/Reboot Now
If you choose the second option, the new kernel, vmunix_test, and the system.SAM configuration file used to create it, are both created in the /stand/build directory.

To use the new kernel:

Enter the following command to move the new kernel into place:
```
# /usr/sbin/kmupdate
```
Enter the following command to reboot the system:
```
# shutdown -r
```

To enable asynchronous I/O operations using the HP asynchronous device driver:

Log in as the root user.
Enter the following command to create a new device file:
```
# /sbin/mknod /dev/async c 101 0x0
```
Enter the following command to verify that the /dev/async device file exists and has the major number 101:
```
# ls -l /dev/async
```
The output of this command should look similar to the following:
```
crw------- 1 orcle   oracle     101 0x000000 Oct 28 10:32  /dev/async
```
If necessary, give the device file the UNIX owner and permissions consistent with those of the Oracle software owner.

If the Oracle software owner is oracle, enter the following commands:
```
# /usr/bin/chown oracle:dba /dev/async
# /usr/bin/chmod 660 /dev/async
```

Verifying Asynchronous I/O

To verify that asynchronous I/O is working:

Set the DISK_ASYNCH_IO initialization parameter to TRUE.
Start the Oracle database.
Enter the following command to start the GlancePlus/UX utility:
```
$ gpm
```
In the main window, choose Reports>Process List.
In the Process List window, select the database writer process and choose Reports>Process Open Files.

The list of files currently opened by the database writer process appears.
Look for the /dev/async file or the mode 101 0x000000 in the list of open files.

If either the /dev/async file or the mode 101 0x000000 is in the list, the /dev/async file has been opened by the database writer process. This means that the database writer process is executing I/O through the HP asynchronous device driver and is working properly.

Asynchronous Flag in SGA

Oracle9i on HP uses a non-blocking polling facility provided by the HP asynchronous driver to check the status of I/O operations. This polling is performed by checking a flag that is updated by the asynchronous driver based on the status of the I/O operations submitted. HP requires that this flag be in shared memory.

Oracle9i configures an asynchronous flag in the SGA for each Oracle process. Oracle9i on HP has a true asynchronous I/O mechanism where I/O requests can be issued even though some previously issued I/O operations are not complete. This helps to enhance performance and ensures good scalability of parallel I/O processes.

Before Oracle8i release 8.1.7, the Oracle server was able to execute I/O operations only from shared memory using the HP asynchronous driver. Oracle8i release 8.1.7 executes I/O operations from both shared memory and process-private regions using the new HP asynchronous driver. However, I/O operations through the asynchronous driver are not asynchronous in nature. This is because Oracle8i must perform a blocking wait to check the status of I/O operations submitted to the asynchronous driver. Doing this causes some Oracle processes, for example the database writer process, to essentially execute synchronous I/O.

B Tuning for Oracle9i on HP