Tuning for Oracle9i on HP

Tuning for Oracle9i on HP

HP SCHED_NOAGE Scheduling Policy

On HP, most processes run under 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, Oracle9i performance increased by up to 10 percent in OLTP environments using the SCHED_NOAGE policy.
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 154 to 255.
Lightweight Timer Implementation

Oracle9i release 1 (9.0.1) on HP-UX uses a new system library call to calculate elapsed time when the TIMED_STATISTICS initialization parameter is set to TRUE. Laboratory tests show that the new library call provides a performance improvement of up to 10 percent over the previous implementation.
Before running Oracle9i, ensure that the required operating system patches are installed on your system. If you run Oracle9i on unpatched versions of the HP kernel, you will receive undefined and unresolved references to the gethrtime() library call.

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 
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, 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 computers 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
Reboot the system by entering the following command:
# 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 release 1 (9.0.1) 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 only able to execute I/O operations 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.

Disabling Data Prefetch on the HP Superdome for Oracle

HP Superdome systems have a data prefetch feature that might impair Oracle performance in update or insert intensive applications. Oracle Corporation and Hewlett-Packard Corporation recommend that you disable this feature for these types of applications.
Prefetching data usually improves application performance. However, the Oracle server is developed to run well on all HP systems, including those that do not include the prefetch feature. In update and insert intensive applications, enabling the data prefetch feature unintentionally creates contention on redo allocation and redo copy latches. This contention is increased as the number of processors is increased. Disabling the data prefetch feature helps to reduce redo latch contention.
HP Superdome systems can run multiple instances of the HP-UX 11i operating system on a single server by defining multiple partitions within a Superdome server. Each partition on a HP Superdome system acts as a logical server running a single instance of HP-UX. Each system can boot, reboot, and operate independently of other partitions and hardware within the Superdome system. Each partition has its own console.
To disable data prefetch on a HP Superdome system, disable the prefetch option in each partition on which the Oracle server is running. To disable the prefetch option for each partition:
Telnet to the Superdome system:
$ telnet superdome_hostname
The Guardian Server Processor (GSP) prompt appears.
At the prompts, enter your GSP username and password.
At the GSP prompt, enter the following command to access the Consoles menu:
GSP> CO
At the prompt, enter the number of the partition running the Oracle server.

The partition console appears.
Respond to the prompts that appear.

Log in to UNIX as the root user.
Enter the following command to determine the partition hostname:
# hostname
Enter the following command to reboot the partition:
# shutdown -r 
The following message appears:
To discontinue boot, hit space bar within 10 secs.... 
Press the space bar within ten seconds to display the Boot Console Handler (BCH) menu.

If you do not press the space bar within ten seconds, the partition reboots and you cannot access the BCH menu. If this happens, repeat steps 6 through 8.
At the BCH menu prompt, enter CO to display the BCH Configuration menu.
At the Configuration Menu prompt, enter the following command:
DataPrefetch DISABLE
In UNIX, enter the following command to reboot the partition:
# shutdown -r
The following message appears:
To discontinue boot, hit space bar within 10 secs.... 
Ignore this message and allow the partition to reboot completely.

After the partition reboots, enter exit to exit the partition.
To disable data prefetch on another partition where the Oracle server is running, perform steps 4 through 14.