15
Tuning

Once capacity planning as described in Chapter 14, "Capacity Planning" is complete, and the necessary hardware acquired, it is important to perform some test runs to figure out if the hardware and software combination is yielding the desired levels of performance. This chapter gives guidelines for tuning an Oracle Internet Directory installation. This chapter contains these topics:

• About Tuning

• Tools for Performance Tuning

• CPU Usage Tuning

• Memory Tuning

• Disk Tuning

• Database Tuning

• Performance Troubleshooting

About Tuning

The two main performance metrics for any installation of Oracle Internet Directory are:

• The average latency of individual operations at peak load

  This is the time for each operation to complete.

• The overall throughput of Oracle Internet Directory expressed in operations per second at peak load

  This is the rate at which an instance of Oracle Internet Directory is capable of completing client operations

If the performance tests yield poor results, the performance problems may be identified and fixed using the information provided in the following sections.

Tools for Performance Tuning

Knowledge of the following tools is recommended for Solaris and most other UNIX operating systems:

Tool	Description
top	Displays the top CPU consumers on a system
vmstat	Shows running statistics on various parts of the system including the Virtual Memory Manager
mpstat	Shows an output similar to vmstat but split across various CPUs in the system. This is available on Solaris only.
iostat	Shows the disk I/O statistics from various disk controllers

Knowledge of the following tools is recommended for Windows NT:

Tool	Description
Windows NT Performance Monitor	Provides a customized view of the events in the system
Windows NT Task Manager	Provides a high level output (like `top' on UNIX) of the major things happening in the system.

Knowledge of the following tools is recommended for Oracle8i:

• utlbstat.sql and utlestat.sql

• The ANALYZE function in the DBMS_STATS package

  See Also: Oracle8i Reference for information about utlbstat.sql and utlestat.sql Oracle8i Concepts for information about the ANALYZE function in the DBMS_STATS package

In addition to the operating system tools, the LDAP applications being used in a customer environment must be able to provide latency and throughput measurement.

In addition, the Database Statistics Collection Tool (oidstats.sh), located at $ORACLE_HOME/ldap/admin, is provided to analyze the various database 'ods' schema objects to estimate the statistics.

See Also: "OID Database Statistics Collection Tool Syntax"

CPU Usage Tuning

The CPU is perhaps the most important resource available for any software. While Chapter 14 gives a rough estimate of the required CPU horsepower for a given application load, sometimes insufficient tuning can cause inefficient use of the CPU resources. Consider tuning CPU resources if either of the following cases is true:

• At peak loads the CPU is 100 percent utilized.

• At peak loads the CPU is underutilized, there is a significant amount of idle time in the system, and this idle time cannot be eliminated at even higher loads.

Internal benchmarks show that Oracle Internet Directory performs best when approximately 70 to 75 percent of the CPU resources are consumed by Oracle Internet Directory processes, and the remaining (about 25 to 30 percent) are consumed by the Oracle foreground processes corresponding to the database connections. While monitoring CPU usage, it is also important to monitor the percentage of time spent in the system space compared to user space. Internal benchmarks show best throughput numbers at about 85 percent user and 15 percent system time.

This section contains these topics:

• Tuning CPU for Oracle Internet Directory Processes

• Tuning CPU for Oracle Foreground Processes

• Taking Advantage of Processor Affinity on SMP Systems

• Other Alternatives for a CPU Constrained System

Tuning CPU for Oracle Internet Directory Processes

The demands placed by Oracle Internet Directory processes on the CPU can be controlled by the ORCLSERVERPROCS and ORCLMAXCC parameters. This table lists suggested values for these parameters for various client loads:

Parameters	500 Concurrent LDAP Clients	1000 Concurrent LDAP Clients	1500 Concurrent LDAP Clients	2000 Concurrent LDAP Clients
Server processes ORCLSERVERPROCS	10 to 15	20 to 30	30 to 40	40 to 60
Database connections ORCLMAXCC	10 to 15	15 to 20	15 to 20	15 to 20

If we take the example of 500 concurrent clients, a value of 10 for ORCLSERVERPROCS with a value of 15 for ORCLMAXCC will result in the following configuration:

• There will be ten server processes created.

• Each server process will spawn fifteen worker threads that will do the actual work.

• Each server process will also maintain a pool of sixteen database connections (15+1) that will be shared among the worker threads.

Tuning Oracle Internet Directory Processes When CPU Is 100 Percent Utilized

If the CPU usage of the system is at 100 percent, further tuning of the Oracle Internet Directory processes should be considered if both of the following conditions are met:

• At peak loads, Oracle Internet Directory processes consume more than 70 percent of all available CPU resources.

• At peak loads, the overall percentage of time spent in the `system' or `kernel' space is greater than 20 percent, and the percentage of time spent in the `user' time is less than 80 percent.

This condition indicates that the system has too manyOracle Internet Directory server processes and database connections configured. This results in several processes or threads contending for the same CPU resources. As a result, the computer wastes a great deal of time context-switching among runnable tasks. To avoid this, one must systematically decrease the values of ORCLSERVERPROCS and ORCLMAXCC until the best performance for the peak load is achieved and the system and user time are split up as follows:

• User time: 85 percent or higher

• System time: 15 percent or lower

Tuning Oracle Internet Directory Processes When CPU Is Under-Utilized

If the CPU usage at peak loads is not at 100 percent and the system is idle for a large percentage of the time (that is, more than 5 percent), this indicates that Oracle Internet Directory processes are under-configured and are not making the best utilization of the CPU resources. To solve this problem, one must systematically increase the values of ORCLSERVERPROCS and ORCLMAXCC until the CPU utilization reaches 100 percent and the system and user time are split up as follows:

• User time: 85 percent or higher

• System time: 15 percent or lower

Tuning CPU for Oracle Foreground Processes

Tuning of CPU resources for Oracle Foreground processes should be considered only if both of the following conditions are met:

• The CPU usage is close to 100 percent at peak loads.

• Oracle foreground processes consume more than 30 percent of all available CPU resources.

If Oracle foreground processes are consuming excessive CPU, it implies that the queries that Oracle Internet Directory is making against the database are using too many CPU cycles. Although there is very little control available to the users on the types of underlying operations performed by the database, the following should be attempted:

• Database statistics on all of the tables and indices associated with the ODS user on the database must be collected using the ANALYZE command. This helps the cost based optimizer make better execution plans for the queries generated by Oracle Internet Directory.

• If the ANALYZE fails to produce better results, and the LDAP queries used have a lot of filters in them, then a simple reorganization of the order in which the filters are specified (with the most specific filter in the beginning and the most generic filter at the end) helps reduce the CPU consumption of the Oracle foreground processes.

Taking Advantage of Processor Affinity on SMP Systems

Several Symmetric Multi-Processor (SMP) systems offer the capability to bind a particular process to a particular CPU. While it is generally a good idea not to bind any process to any processor, it may improve performance if the following conditions are met:

• The CPU utilization of the entire system is close to 100 percent.

• There are more than two CPUs on the computer.

• Oracle Internet Directory processes consume around 70 to 75 percent of the CPU resources.

• The database processes consume around 25 to 30 percent of the CPU resources.

Under the conditions noted above, allowing the database foreground process to run on any CPU can potentially cause many hardware cache misses for other tasks. This is because the database processes need to reference a large amount of data as part of their regular execution, and this often exceeds the limits of L2 caches available on most systems. As a result, when the database process executes on a CPU, most of L2 cache contains pages from the System Global Area (SGA). If a task switch occurs and an Oracle Internet Directory process is activated, all of its fetches from memory will be much slower because the task preceding it on the processor dirtied the L2 cache.

Restricting all of the Oracle foreground processes to execute on only one processor avoids many of the cache misses for Oracle Internet Directory processes. This, in turn, improves the overall performance.

Other Alternatives for a CPU Constrained System

If none of the tips stated in the preceding sections solve CPU related performance problems, the following options are available:

• Upgrade the processing power of the computer, that is, add more CPUs or replace slower CPUs with faster ones.

• Keep the Oracle directory server and the associated Oracle8i database on separate computers.

Memory Tuning

After the CPU, memory is the next most important thing to tune. The primary consumer of memory in an Oracle Internet Directory installation is the Oracle8i database. The SGA of the back-end database must be made as large as possible while leaving room for Oracle Internet Directory and Oracle processes to operate their private stacks and heaps. This section provides some details on determining various components of the SGA.

This section contains these topics:

• Tuning the System Global Area (SGA) for Oracle8i

• Other Alternatives for a Memory-Constrained System

Tuning the System Global Area (SGA) for Oracle8i

The SGA should be sized based on the available physical memory on the system running Oracle8i.

See Also: Oracle8i Performance Guide and Reference for more information on determining appropriate sizes for the SGA. This book tells how to ensure that the SGA size does not cause increased paging swapping activity. The latter is very detrimental to performance.

Once the available size of the SGA is determined, two primary tuning items need to be considered:

• Size of the shared pool

• Size of the buffer cache

An initial estimate for the shared pool size is .5 MB per concurrent database connection determined above.

If this estimate consumes more than 30 percent of the total SGA, use 30 percent of the total SGA instead.

Divide 60 percent of the remaining available SGA size by the block size for the database and use this value for the number of DB_BLOCK_BUFFERS. Both of these values should be initial estimates and can be refined using BSTAT/ESTAT and other RDBMS monitoring tools to determine more accurate sizes for best performance.

Other Alternatives for a Memory-Constrained System

If there is insufficient memory to run both the database and the Oracle directory server on the same computer, then one can put the database on a different computer.

Disk Tuning

Balancing Disk I/O is an important consideration in overall RDBMS, and hence Oracle Internet Directory performance. Typically, one can maximize the I/O throughput by using one or more of the following techniques:

• Striping logical volumes so that the I/O operations use multiple disk spindles

• Putting different tablespaces in different logical and physical disk volumes

• Distributing the disk volumes on multiple I/O controllers

  See Also: Oracle8i Performance Guide and Reference for general information about balancing and tuning disk I/O

This section contains these topics:

• Balancing Tablespaces

• RAID

Balancing Tablespaces

The Oracle Internet Directory schema is distributed among several tablespaces at installation time for ease of maintenance and performance. Each tablespace contains a grouping of Oracle Internet Directory schema objects appropriate for co-location on disk storage. As available, it is also beneficial to distribute the following objects onto separate logical disks.

See Also: "RAID" for more discussion about logical disks

Separate the following:

• OLTS_ATTRSTORE and OLTS_IND_ATTRSTORE

  Separating the attribute store table from its index

• OLTS_CT_DN and OLTS_IND_CT_DN

  Separating the DN catalog from its index

• OLTS_xxxx and OLTS_IND_xxxx

  (Empirically, separate the storage tablespace from the associated index)

• OLTS_IND_ATTRSTORE and OLTS_IND_CT_DN

  Alternating the attribute store and DN catalog indexes. This helps even if there are only two logical disks available (one containing OLTS_CT_DN and OLTS_IND_ATTRSTORE and the other containing OLTS_IND_CT_DN and OLTS_ATTRSTORE)

RAID

The information on balancing tablespaces is given in terms of separating Oracle Internet Directory tablespaces onto different logical drives. This assumes that a 'logical drive' is manifested on a separate disk or set of disks from other 'logical drives', and thus represents a division among disks for I/O. (Two logical drives on the same physical disk media do not really provide the same combined I/O throughput of two logical drives located on different physical media.) If a logical drive can be manifest on a striped or RAID disk subsystem, then this may increase the I/O capacity of that logical drive. However, the tablespace locations considered earlier remain applicable when considering, for instance, different logical drives of a volume manager.

Database Tuning

This section describes the other tunable parameters available to an Oracle Internet Directory installation.

The following table gives a quick overview of the recommended values of RDBMS parameters for various client loads. These parameters are configurable in the initialization parameter file.

Parameters	500 Concurrent LDAP Clients	1000 Concurrent LDAP Clients	1500 Concurrent LDAP Clients	2000 Concurrent LDAP Clients
Open cursors	100	100	100	100
Sessions	225	600	800	1200
Database block buffers	200 to 250 MB	200 to 250 MB	200 to 250 MB	200 to 250 MB
Database block size	8192	8192	8192	8192
Shared pool size	30 to 40 MB	30 to 40 MB	30 to 40 MB	30 to 40 MB
Processes	400	800	1000	1500

This section describes each of the RDBMS tunable parameters in more detail. It contains these topics:

• Required Parameter

• Parameters Dependent on Oracle Internet Directory Server Configuration

• SGA Parameters Dependent on Hardware Resources

Required Parameter

Configure the OPEN_CURSORS parameter as follows:

OPEN_CURSORS=100

The Oracle8i default of 50 or so is too small to accommodate Oracle Internet Directory server cursor cache. Note that this value is not dependent on other Oracle Internet Directory server parameters, such as # SERVERS and # WORKERS. The value of 100 is sufficient for any size DIT.

Parameters Dependent on Oracle Internet Directory Server Configuration

Configure the SESSIONS parameter as follows:

PROCESSES = (# OID server processes per instance) x 

        (# DB Connections per server + 1) x 
        (# of OID instances) + 20

SESSIONS = 1.1 *PROCESSES + 5

Each Oracle Internet Directory server process requires a number of concurrent database connections equal to the number of worker threads configured for that server plus one. The total number of concurrent database connections allowed must therefore include this number per server, per instance. The additional 20 connections added to the parameter value accounts for the Oracle background processes plus other Oracle Internet Directory processes such as OID Monitor, OID Control, Oracle directory replication server, and bulk tools.

Using Multi-Threaded Server (MTS)

Depending on the total number of concurrent database connections required, and as determined by the setting for the SESSIONS parameter, enabling MTS may help balance overall system load better. If the total number of concurrent database connections required is over 300, then configure MTS. One shared server should be configured for every 10 database connections required.

Note: The number of requires concurrent database connections depends on the hardware selected. See Net8 Administrator's Guide and Oracle8i Administrator's Guide for further information about MTS configuration.

SGA Parameters Dependent on Hardware Resources

The main parameters that contribute to the SGA are discussed in "Memory Tuning". The following are a few more parameters that may be tuned:

• Sort area

  Set to 262144 (256k) to ensure sufficient sort area available to prevent on-disk sorts.

• Redo Log Buffers

  Set to 32768 (32k) as an initial estimate. If log write performance becomes a performance problem, use a large enough value to make sure (redo log space requests / redo entries) > 1/5000 to prevent the LGWR process from falling behind. This overall has little size effect on the variable SGA size, so making this a little bit too large should not be a problem.

Performance Troubleshooting

This section gives some quick pointers for common performance related problems.

If LDAP search performance is poor, make sure that:

• The attributes on which the search is being made are indexed

• Schema associated with the `ODS' user is `ANALYZED'

  For searches involving multiple filter operands, make sure that the order in which they are given goes from the `most specific' to the `least specific'. For example, &(l=Chicago)(state=Illinois)(c=US) is better than &(c=US)(state=Illinois)(l=Chicago).

If LDAP add/modify performance is poor, make sure that:

• There are enough redo-log files in the database

• There are enough rollback segments in the database

• The schema associated with the `ODS' user is `ANALYZED'