B Administering Oracle Database on HP-UX

Enabling SCHED_NOAGE for Oracle Database

To allow Oracle Database to use the SCHED_NOAGE scheduling policy, the OSDBA group (typically the dba group), 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:

1. Log in as the root user.

2. Using any text editor, open the /etc/privgroup file, or create it if necessary.

3. Add or edit the following line, which begins with the name of the OSDBA group, specifying the privileges RTPRIO and RTSCHED that you want to grant to this group every time the system reboots:

   dba RTPRIO RTSCHED
   
   

4. Save the file and exit from the text editor.

5. Enter the following command to grant the privileges to the OSDBA group:

   # /usr/sbin/setprivgrp -f /etc/privgroup
   
   

6. Enter the following command to verify that the privileges are set correctly:

   # /usr/sbin/getprivgrp dba
   
   

Add the HPUX_SCHED_NOAGE parameter to the parameter file for each instance, setting the parameter to an integer value to specify process priority levels. On HP-UX 11i the range is 178 to 255. Lower values represent higher priorities. If the parameter setting is out of range, Oracle Database automatically 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. Whenever the highest priority is assigned to Oracle processes, either by the user or by automatic readjustment, Oracle Database generates a message in the alert_sid.log file warning about the possibility of exhaustion of CPU resources on the system. Oracle recommends that you set the parameter to assign the priority level you want for Oracle processes. If the parameter setting is out of range, Oracle10i 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.

MLOCK Privilege

To allow Oracle Database to execute asynchronous I/O operations, the OSDBA group (dba) must have the MLOCK privilege. To give the dba group the MLOCK privilege:

1. Log in as the root user.

2. Using any text editor, open the /etc/privgroup file, or create it if necessary.

3. Add or edit the following line, which begins with the name of the OSDBA group, specifying the privilege MLOCK:

   
   

   Note: You must use only one line to specify the privileges for a particular group in this file. If the file already contains a line for the dba group, add the MLOCK privilege on the same line.

   
   

   dba RTPRIO RTSCHED MLOCK
   
   

4. Save the file and exit from the text editor.

5. Enter the following command to grant the privileges to the OSDBA group:

   # /usr/sbin/setprivgrp -f /etc/privgroup
   
   

6. Enter the following command to verify that the privileges are set correctly:

   # /usr/sbin/getprivgrp dba
   
   

Implementing Asynchronous I/O

If you want to use asynchronous I/O on HP-UX, you must either use Automatic Storage Management (ASM) or raw partitions for database files. If you choose to use raw partitions, use the System Administrator Management (SAM) utility to configure the asynchronous disk driver into the HP-UX kernel.

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

1. Enter the following command as the root user:

   # sam
   
   

2. Choose the Kernel Configuration area.

3. Choose the Drivers area.

4. Choose the asynchronous disk driver (asyncdsk).

5. Select Actions>Add Driver to Kernel.

6. Select List>Configurable Parameters.

7. Choose the MAX_ASYNC_PORTS parameter.

8. Select Action>Modify Configurable Parameter.

9. Specify a new value for the parameter, using the following guidelines, then choose OK.

   The MAX_ASYNC_PORTS parameter is a configurable HP-UX 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.

10. Choose Actions>Process a New Kernel.

11. 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:

1. Enter the following command to move the new kernel into place:

   # /usr/sbin/kmupdate
   
   

2. Enter the following command to reboot the system:

   # /sbin/shutdown -r now
   
   

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

1. Log in as the root user.

2. Enter the following command to create a new device file:

   # /sbin/mknod /dev/async c 101 0x0
   
   

3. 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 oracle   dba     101 0x000000 Oct 28 10:32  /dev/async
   
   

4. If necessary, give the device file the UNIX owner and permissions consistent with those of the Oracle software owner and OSDBA group.

   If the Oracle software owner is oracle and the OSDBA group is dba, enter the following commands:

   # /usr/bin/chown oracle:dba /dev/async
   # /usr/bin/chmod 660 /dev/async
   
   

Verifying Asynchronous I/O

To verify asynchronous I/O, first verify that the HP-UX asynchronous driver is configured for Oracle Database, then verify that Oracle Database is executing asynchronous I/O through the HP-UX device driver.

Asynchronous Flag in SGA

Oracle Database on HP-UX uses a non-blocking polling facility provided by the HP-UX 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-UX requires that this flag be in shared memory.

Oracle Database configures an asynchronous flag in the SGA for each Oracle process. Oracle Database on HP-UX 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, Oracle Database was able to execute I/O operations only from shared memory using the HP-UX asynchronous driver. Oracle Database 10g executes I/O operations from both shared memory and process-private regions using the new HP-UX asynchronous driver. However, I/O operations through the asynchronous driver are not asynchronous in nature. This is because Oracle Database must perform a blocking wait to check the status of I/O operations submitted to the asynchronous driver. This causes some Oracle processes, for example the database writer process, to essentially execute synchronous I/O.

Persistent Private SQL Areas and Memory

When a user submits a SQL statement, Oracle Database automatically performs the following memory allocation steps:

1. It checks the shared pool in the Oracle SGA to see if a shared SQL area for an identical statement already exists. If a shared SQL area exists, Oracle uses it to run subsequent new instances of the statement. If a shared SQL area does not exist, Oracle allocates a new shared SQL area in the shared pool for the SQL statement.

2. Oracle allocates a private SQL area on behalf of the user session.

Private SQL areas contain data such as bind information and runtime memory structures for processed SQL statements. Private SQL areas also contain parsed statements and other statement processing information.

Every user who submits the same SQL statement has a cursor that uses a single shared SQL area. In this way, many private SQL areas can be associated with the same shared SQL area. If a user session is connected through a dedicated server, then private SQL areas are located in the server process PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.

The CURSOR_SPACE_FOR_TIME initialization parameter specifies whether a SQL cursor can be deallocated from the library cache to make room for a new SQL statement. When this parameter is set to TRUE, Oracle Database can deallocate a shared SQL area from an Oracle library cache only when all application cursors associated with the SQL statement are closed. Setting the parameter to TRUE also prevents the deallocation of private SQL areas associated with open cursors, making the user’s private SQL area persistent.

Compared to earlier Oracle releases, setting the CURSOR_SPACE_FOR_TIME initialization parameter to TRUE in Oracle Database has the following advantages:

• It accelerates SQL execution calls, because each active cursor’s SQL area is present in memory and never aged out.

• It improves application performance, because Oracle Database does not need to verify that a shared SQL area is in the library cache. By retaining private SQL areas between SQL statement executions, it also helps to save cursor allocation and initialization time.

Compared to earlier Oracle releases, setting the CURSOR_SPACE_FOR_TIME initialization parameter to TRUE in Oracle Database has the following disadvantages:

• It increases the memory requirements of user processes because of an increased memory allocation for the persistent private SQL areas.

• It significantly increases cursor memory, which leads to larger memory allocations for Oracle Database shadow processes.

If you set the value of CURSOR_SPACE_FOR_TIME parameter to FALSE, you might experience degraded overall SQL execution and performance. Setting the parameter to FALSE can result in rapid deallocation of shared SQL areas from the library cache.

Default Large Virtual Memory Page Size

By default, Oracle Database uses the largest virtual memory page size setting available on HP-UX for allocating process-private memory. It is defined by the value L, largest, and is currently 1 GB on HP-UX 11i. This value is set as one of the LARGE_PAGE_FLAGS options when linking an Oracle executable.

When the virtual memory page size is set to L, HP-UX allocates the available process-private memory to pages of 1 MB, 4 MB, 16 MB and so on, until it reaches the 1 GB limit, or until it reaches the total amount of allocated memory. If you allocate enough memory to the Oracle PGA for the operating system to be able to allocate memory in larger data page size units, then the operating system allocates the maximum page size at once. For example, if you allocate 48 MB for the Oracle PGA, then your system can have either three pages each of 16 MB, or a series of pages in unit sizes with the smaller multiples, for example, four 1 MB pages, three 4 MB pages, and two 16 MB pages. If you allocate 64 MB to the PGA, then the operating system allocates one page of 64 MB, as the data page unit size matches the available memory.

In general, large memory pages yield better application performance by reducing the number of virtual memory translation faults that must be handled by the operating system, freeing more CPU resources for the application. Large pages help to reduce the total number of data pages needed to allocate the process-private memory. This reduction decreases the chances of translation lookaside buffer (TLB) misses at the processor level.

However, if applications are constrained in memory and tend to run a very large number of processes, then this drastic page size increase might lead processes to indicate large memory allocations, followed by an out of memory error. If this happens, you must lower the page size to a value between the D (default) size of 4 KB and the L (largest) size of 1 GB.

With the lowest page size setting (4 KB), CPU utilization can be 20% higher than that with a larger page size setting. With the highest setting of L, the memory utilization can be 50% higher than that with a 4 MB setting. In cases where the system shows memory constraints, Oracle recommends that you set the page size to match the requirements of the particular application, within the constraints of available memory resources.

For example, an application that has problems with the L setting might show reasonable performance with a 4 MB virtual memory page setting.

Tuning Recommendations

To address tuning for the increased memory allocation required for Persistent Private SQL Areas and Large Virtual Memory Page Sizes, Oracle recommends the following:

• Keep the value of the CURSOR_SPACE_FOR_TIME parameter to TRUE, unless the system indicates library cache misses when running the application. If that happens, the shared pool might be small enough to hold the SQL areas for all concurrent open cursors.

• Decrease the virtual memory data page size for Oracle Database as necessary. Use the following command to alter the page size setting:

  # /usr/bin/chatr +pd newsize $ORACLE_HOME/bin/oracle
  
  

  In the preceding example, newsize represents the new value of the virtual memory page size.

  Display the new setting using the chatr command, as follows:

  # /usr/bin/chatr $ORACLE_HOME/bin/oracle
  
  

Tuning Hyper Messaging Protocol Parameters

Oracle recommends that you review parameters and, for optimal performance, tune them according to HP guidelines. The list of Hyper Messaging Protocol (HMP) parameters that you can tune is available in the skclic.conf file in the /opt/clic/lib/skgxp directory.

To enable Oracle to use HMP and to relink the Oracle binary with HMP, complete the following steps:

1. Set up the environment on all nodes.

2. Shut down the database.

3. Enter the following commands on all nodes:

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