B Administering Oracle Database on HP-UX

This appendix provides information about administering Oracle Database on HP-UX. It contains the following topics:

HP-UX Shared Memory Segments for an Oracle Instance
HP-UX SCHED_NOAGE Scheduling Policy
Lightweight Timer Implementation
Asynchronous I/O
Large Memory Allocations and Oracle Database Tuning
CPU_COUNT Initialization Parameter and HP-UX Dynamic Processor Reconfiguration
Network Information Service (NIS) external naming support

B.1 HP-UX Shared Memory Segments for an Oracle Instance

When an Oracle Database instance starts, it creates memory segments by dividing the shared memory allocated for creating the Oracle Shared Global Area (SGA) by the value of the HP-UX shmmax kernel parameter. For example, if 64 GB of shared memory is allocated for a single Oracle instance and the value of the shmmax parameter is 1 GB, then Oracle Database creates 64 shared memory segments for that instance.

Performance degradation can occur when an Oracle instance creates multiple shared memory segments. This is because each shared memory segment receives a unique protection key when Oracle Database creates the instance. The number of protection keys available depends on the system architecture as shown in the following table.

Architecture	Number of Protection Keys
PA-RISC	6
Itanium	14

If the Oracle instance creates more shared memory segments than the number of protection keys, then the HP-UX operating system displays protection key faults.

Oracle 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 requires only one protection key.

To display the list of active shared memory segments on the system, run the following command:

$ ipcs -m

If Oracle Database creates more segments for the instance than the number of protection keys, then increase the value of the shmmax kernel parameter.

B.2 HP-UX SCHED_NOAGE Scheduling Policy

On HP-UX, 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, when it is holding a latch. HP-UX 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. The use of the SCHED_NOAGE policy with Oracle Database can increase performance by 10 percent or more in OLTP environments.

The SCHED_NOAGE policy does not provide the same level of performance gains in decision support environments because there is less resource competition. Because each application and server environment is different, you should test and verify that your environment benefits from the SCHED_NOAGE policy. When using SCHED_NOAGE, Oracle recommends that you exercise caution in assigning highest priority to Oracle processes. Assigning highest SCHED_NOAGE priority to Oracle processes can exhaust CPU resources on your system, causing other user processes to stop responding.

B.2.1 Enabling SCHED_NOAGE for Oracle Database

To permit 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:

Log in as the root user.
Using any text editor, open the /etc/privgroup file, or create it if necessary.
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 restarts:
```
dba RTPRIO RTSCHED
```
Save the file, and quit the text editor.
Enter the following command to grant the privileges to the OSDBA group:
```
# /usr/sbin/setprivgrp -f /etc/privgroup
```
Enter the following command to verify that the privileges are set correctly:
```
# /usr/sbin/getprivgrp dba
```

Add the HPUX_SCHED_NOAGE initialization parameter to the parameter file for each instance, setting the parameter to an integer value to specify process priority levels. The supported range of values is 178 to 255. Lower values represent higher priorities. If the parameter setting is out of range, then 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.

B.3 Lightweight Timer Implementation

With Oracle Database 10g, you can collect run-time statistics at all times if the dynamic initialization parameter STATISTICS_LEVEL is set to TYPICAL (default) or ALL. This parameter setting implicitly sets the TIMED_STATISTICS initialization parameter to true. Oracle Database on HP-UX systems uses the gethrtime() system library call to calculate elapsed times during the collection of the statistics. The use of this lightweight system library call enables you to collect run-time statistics at all times while running an Oracle instance, without affecting performance.

This system library call can provide a performance improvement of up to 10 percent over an Oracle release that does not use the gethrtime() system library call when the TIMED_STATISTICS initialization parameter is explicitly set to true. In addition, there is no negative impact on the OLTP performance of an Oracle Database while using the gethrtime() system library call to collect run-time statistics at all times.

B.4 Asynchronous I/O

The asynchronous I/O pseudo-driver on HP-UX enables Oracle Database 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 servers and workstations.

B.4.1 MLOCK Privilege

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

Log in as the root user.
Using any text editor, open the /etc/privgroup file, or create it if necessary.
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, then add the MLOCK privilege on the same line.
```
dba RTPRIO RTSCHED MLOCK
```
Save the file, and quit the text editor.
Enter the following command to grant the privileges to the OSDBA group:
```
# /usr/sbin/setprivgrp -f /etc/privgroup
```
Enter the following command to verify that the privileges are set correctly:
```
# /usr/sbin/getprivgrp dba
```

B.4.2 Implementing Asynchronous I/O

If you want to use asynchronous I/O on HP-UX, then you must use one of the following storage options for database files:

Raw devices (partitions or logical volumes)
An Automatic Storage Management disk group that uses raw partitions

B.4.3 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.

B.4.3.1 Verifying That HP-UX Asynchronous Driver is Configured for Oracle Database

To verify that the HP-UX asynchronous driver is configured properly for Oracle Database:

Start Oracle Database with a few parallel query slave processes.
Start the GlancePlus/UX utility as follows:
```
$ gpm
```
In the main window, click Reports and then click Process List.
In the Process List window, select one parallel query slave process, select Reports, and then select Process Open Files.

The list of files currently opened by the parallel query slave process is displayed.
In the list of open files, check for the /dev/async file or the 101 0x000000 mode.

If either is in the list, then the /dev/async file has been opened by the parallel query slave process, and the HP-UX asynchronous device driver is configured properly to enable Oracle processes to run asynchronous I/O. Make a note of the file descriptor number for the /dev/async file.

B.4.3.2 Verifying that Oracle Database is Using Asynchronous I/O

To verify that Oracle Database is using asynchronous I/O through the HP-UX asynchronous device driver:

Attach the HP-UX tusc utility to the same Oracle parallel query slave that you selected in GlancePlus in the preceding procedure.
Run an I/O bound query in your environment.
Check the pattern of read and write calls in the tusc output.

You can do this, for example, by entering the following command, where pid is the process ID of a parallel query slave supposed to process asynchronous I/O:
```
$ tusc -p pid > tusc.output
```
After running the query, press Ctrl+c to disconnect from the process, and then open the tusc.output file.

The following example shows a sample tusc.output file:
```
( Attached to process 2052: "ora_p000_tpch" [ 64-bit ])
................... 
........................ 
[2052] read(9, "80\0\001\013  \b\0\0\0\0\0\0\0\0".., 388) .. = 28 
[2052] write(9, "\0\0\00e\0\0\0\080\0\001\013Ð \0".., 48) .. = 48 
[2052] read(9, "80\0\001\013¢ 18\0\0\0\0\0\0\0\0".., 388) .. = 28 
[2052] write(9, "\0\0\00e\0\0\0\080\0\001\01bd4\0".., 48) .. = 48 
```
If the DISK_ASYNCH_IO initialization parameter is not explicitly set to false (set to true by default), then the tusc.output file shows a pattern of asynchronous read/write calls of the same file descriptor (9 in the preceding example) back to back.

Map the file descriptor number in the tusc.output file to that used by /dev/async file in GlancePlus. They should match for the particular parallel query slave process. This verifies that I/O through the HP-UX asynchronous driver is asynchronous. With synchronous I/O, or if the DISK_ASYNC_IO initialization parameter is explicitly set to false, you do not see the asynchronous read/write pattern described previously. Instead, you see calls to lseek or pread/pwrite. You also see a number of different file descriptors (the first argument to read/write) instead of just a single file descriptor.

B.4.4 Asynchronous Flag in SGA

Oracle Database on HP-UX uses a nonblocking 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.

Releases of Oracle Database earlier than release 8.1.7 were able to run I/O operations only from shared memory by using the HP-UX asynchronous driver. Oracle Database 10g runs 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, such as the database writer process, to essentially process synchronous I/O.

B.5 Large Memory Allocations and Oracle Database Tuning

Applications running on Oracle Database 10g can use significantly more memory than applications running on earlier releases. There are two reasons for this:

The CURSOR_SPACE_FOR_TIME initialization parameter is changed from the default value false, to true.
Oracle Database 10g changes the default setting for virtual memory data pages from D (4KB) to L (4 GB) on HP-UX systems.

B.5.1 Persistent Private SQL Areas and Memory

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

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, then Oracle uses it to run subsequent new instances of the statement. If a shared SQL area does not exist, then Oracle allocates a new shared SQL area in the shared pool for the SQL statement.
Oracle allocates a private SQL area on behalf of the user session.

Private SQL areas contain data such as bind information and run-time 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, then part of the private SQL area is kept in the SGA.

The CURSOR_SPACE_FOR_TIME initialization parameter specifies if 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 the SQL area of each active cursor is present in memory and never ages out.
It improves application performance, because Oracle Database does not have 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 Database 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, then you may 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.

B.5.2 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 HP-UX v2 (11i.23). 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 3 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 required 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 may lead processes to indicate large memory allocations, followed by an Out of memory error message. If this happens, then you must lower the page size to a value between the D (default) size of 4 KB and the L (largest) size of 4 GB.

With the lowest page size setting (4 KB), CPU utilization can be 20 percent higher than that with a larger page size setting. With the highest setting of L, the memory utilization can be 50 percent 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 may show reasonable performance with a 4 MB virtual memory page setting.

B.5.3 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 as true, unless the system indicates library cache misses when running the application. If that happens, then the shared pool may be small enough to hold the SQL areas for all concurrent open cursors.
Decrease the virtual memory data page size for Oracle Database as required. 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
```

B.6 CPU_COUNT Initialization Parameter and HP-UX Dynamic Processor Reconfiguration

HP-UX 11i supports dynamic run-time reconfiguration of processor sets (Psets) and dynamic reassignment of workload between processor sets by valid users.

HP-UX Virtual Partitions (VPAR) enable users to configure their systems in multiple logical partitions where each partition is assigned its own set of processors, memory, and I/O resources, and can run a separate instance of the HP-UX operating system. HP-UX Processor Sets integrated with vPars support dynamic processor migration from one virtual partition to another without requiring a restart of any virtual partition. This helps to provide efficient resource partitioning between applications to minimize interference and guarantees necessary resource allocation to each application running on the HP-UX server.

The Oracle Database CPU_COUNT initialization parameter specifies the number of CPUs available to Oracle Database. Oracle Database 10g on HP-UX 11i can dynamically detect changes to the CPU host configuration by periodically querying the operating system. If there are any changes to the number of CPUs in the system, then Oracle adjusts the CPU_COUNT parameter to the correct value to reallocate its internal resources. This enables new workloads to take advantage of the newly added processors, and database performance can improve without any changes by the DBA, provided high CPU usage was the cause of the bottleneck.

Some initialization parameter values are calculated based on the CPU_COUNT value at system startup. If changes occur to the number of CPUs after system startup, then Oracle does not dynamically update these initialization parameters to account for the new number of CPUs. This may sometimes result in suboptimal database configuration if the new number of CPUs is significantly different from the original. If the number of CPUs on a system increases significantly, then the database may not take advantage of on the additional processing power.

If the number of CPUs on the system increases by a small number, for instance from 2 to 4, then no action is required.

If the number of CPUs on the system increases by a large number, for instance from 2 to 32, then follow these steps:

Use one of the following methods to set the CPU_COUNT initialization parameter to the new value:
- If the database uses a server parameter file (spfiledbname.ora), then run the following SQL*Plus command as the SYS user to specify a new value for the parameter:
```
SQL> ALTER SYSTEM SET CPU_COUNT=32 SCOPE=SPFILE
```
- If the database uses an initialization parameter file (initsid.ora), then edit the file and specify a new value for the parameter.
Restart the database.

B.7 Network Information Service (NIS) external naming support

NIS external naming adapter is supported on hp-ux. To configure and use NIS external naming, refer to the "Configuring External Naming Methods" section of Oracle Database Net Services Administrator's Guide.