Database Parameters

This section describes the key Oracle database parameters that can affect OpenText PPM system performance. It also provides parameter settings recommended for the OpenText PPM environment.

For detailed information about the Oracle parameters described in the following sections, see your Oracle database documentation.

In this topic:

ARCHIVE_LAG_TARGET

ARCHIVE_LAG_TARGET limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after the specified amount of time elapses.

A 0 value disables the time-based thread advance feature; otherwise, the value represents the number of seconds. Values larger than 7200 seconds are not of much use in maintaining a reasonable lag in the standby database. The typical, or recommended value is 1800 (30 minutes). Extremely low values can result in frequent log switches, which could degrade performance; such values can also make the archiver process too busy to archive the continuously generated logs.

Recommended Setting

Set the parameter to 0 to help lower "log file sync" waits.

_B_TREE_BITMAP_PLANS

The _B_TREE_BITMAP_PLANS parameter enables creation of interim bitmap representation for tables in a query with only binary index(es).

Recommended Setting

Set the _B_TREE_BITMAP_PLANS parameter value to false. We recommend that you set this parameter at the instance level instead of at the system level. You can use the ON LOGON trigger so that the setting does not interfere with other application schemas that use the database.

To set the parameter to false, run:

ALTER SYSTEM SET "_B_TREE_BITMAP_PLANS"=false scope=both

This prevents Oracle from unnecessary conversions between ROWID and BITMAPS when running queries.

CURSOR_SHARING

The CURSOR_SHARING parameter determines what kind of SQL statements can share the same cursors. Although this optional parameter accepts the following three values, OpenText PPM supports only the values SIMILAR and EXACT.FORCE is not supported.

• FORCE. This value forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

  Caution: Setting CURSOR_SHARING=FORCE results in errors during OpenText PPM project creation.

• SIMILAR. This setting causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

• EXACT. This value only enables statements that have identical text to share a cursor. We recommend that you set the CURSOR_SHARING parameter to EXACT. If any other value is used, PPM Server will try to force the value of this parameter to EXACT for all PPM-related databases transactions.

DB_BLOCK_SIZE

The DB_BLOCK_SIZE parameter is used to specify the size (in bytes) of Oracle database blocks. After the database is created, you cannot change this parameter.

Recommended Setting

Set the DB_BLOCK_SIZE parameter value to 8192 (8 KB).

DB_CACHE_SIZE

The DB_CACHE_SIZE parameter value specifies the size (in KB or MB) of the default buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE parameter).

Recommended Setting

Specify a DB_CACHE_SIZE parameter value of at least 500 (expressed in MB).

FILESYSTEMIO_OPTIONS

FILESYSTEMIO_OPTIONS specifies I/O operations for file system files.

Available values include none, setall, directIO, and asynch.

Recommended Setting

Set the FILESYSTEMIO_OPTIONS parameter to setall to enable both direct I/O and asynchronous I/O where possible.

_GC_POLICY_TIME

The _GC_POLICY_TIME parameter allows you to control whether or not to disable Dynamic Resource Manager.

Recommended Setting

Set the _GC_POLICY_TIME parameter to 0 to disable Dynamic Resource Manager, eliminating cluster related waits.

_GC_UNDO_AFFINITY

The _GC_UNDO_AFFINITY parameter allows you to control whether or not to disable Dynamic Resource Manager.

Recommended Setting

Set the _GC_UNDO_AFFINITY parameter to False to disable Dynamic Resource Manager, eliminating cluster related waits.

GLOBAL_NAMES

The GLOBAL_NAMES parameter value determines whether a database link must have the same name as the database to which it connects.

Recommended Setting

Set GLOBAL_NAMES to false. If you set the value to true, loopback database link creation fails.

To create a loopback database link with this parameter set to true:

create database link <User_Name.Oracle_SID.Domain_Name> connect to <User_Name> identified by <Password> using <Oracle_SID>

Example 1

create database link kinadm.dlngrd02.world connect to kinadm identified by <Password> using 'dlngrd02'

To use the database link you created:

select * from <Table_Name>@<Oracle_SID>

Example 2

select * from clis_users@dlngrd02

_LIKE_WITH_BIND_AS_EQUALITY

In situations in which the LIKE pattern is expected to match very few rows, you can set the hidden parameter LIKE_WITH_BIND_AS_EQUALITY to true. The optimizer treats the predicate as though it were COLUMN = :BIND, and uses column density as the selectivity instead of a fixed five percent selectivity factor. The optimizer treats expressions in the format
[indexed-column like :b1] in the same way it treats expressions in the format [index-column = :b1].

Oracle uses some defaults to estimate column selectivity for the LIKE operator, but most of the time this estimate is not precise and can cause an index path access to be rejected.

Recommended Setting

Set the parameter value to true.

LOG_BUFFER

The LOG_BUFFER parameter value determines the size (in bytes) of the memory area used to save transaction change information. When data is committed, the log buffer is flushed to disk. Small log buffers cause more frequent flushes to disk.

Recommended Setting

For systems with 50 or more concurrent users, set the parameter value to 25 (expressed in MB).

NLS_LENGTH_SEMANTICS

The initialization parameter NLS_LENGTH_SEMANTICS lets you create CHAR and VARCHAR2 columns using either byte- or character-length semantics.

Recommended Setting

You must set the NLS_LENGTH_SEMANTICS parameter to CHAR. After you do, the VARCHAR2 columns in tables use character-length semantics. This means that if, for example, you declare a column as VARCHAR2(30), the column stores 30 characters, and not 30 bytes. In a multibyte character set, this ensures that adequate space is available.

If you are using a single-byte character set, setting NLS_LENGTH_SEMANTICS to CHAR makes it easier to transition to a multibyte character set later.

OPEN_CURSORS

Oracle uses cursors to handle updates, inserts, deletes, and result sets that queries return. The OPEN_CURSORS parameter value determines the number of cursors one session can hold open at a given time.

Recommended Setting

Set the OPEN_CURSORS parameter value to 1000 or higher.

OPEN_LINKS

The OPEN_LINKS parameter setting affects only Deployment Management. It determines the number of open database link connections to other databases that can be active at a given time.

Recommended Setting

Set the OPEN_LINKS parameter value to 20.

OPEN_LINKS_PER_INSTANCE

The OPEN_LINKS_PER_INSTANCE parameter determines the maximum number of migratable open connections globally for each database instance.

Recommended Setting

Set the OPEN_LINKS_PER_INSTANCE parameter value to 4.

OPTIMIZER_INDEX_CACHING

The OPTIMIZER_INDEX_CACHING parameter gives the optimizer an estimate of the percentage of indexes cached in the buffer cache. The default value is 0. At this setting, Oracle does not "expect" any index to be cached while deciding the best access plan for a query. If you set this parameter to a higher value, Oracle favors using an index in the `IN-list iterator' and nested loop joins.

Recommended Setting

The range of values of OPTIMIZER_INDEX_CACHING is: 0 to 100. The default value is 0.

For most OLTP system, OPTIMIZER_INDEX_CACHING can be set to 90.

For most OpenText PPM deployments, set the OPTIMIZER_INDEX_CACHING parameter value to 90.

OPTIMIZER_INDEX_COST_ADJ

The OPTIMIZER_INDEX_COST_ADJ parameter is used to tune optimizer behavior for access path selection by making the optimizer more or less prone to selecting an index access path over a full table scan. The lower this parameter value, the likelier it is that the optimizer chooses index scan over table scan.

The range of values of OPTIMIZER_INDEX_COST_ADJ is 1 to 10000. The default value is 100. With this default value, the optimizer evaluates index access paths at regular cost. With any other value, the optimizer evaluates the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as with the default value.

For most OLTP system, OPTIMIZER_INDEX_COST_ADJ can be set from 10 to 50.

For OpenText PPM deployment, OPTIMIZER_INDEX_COST_ADJ = Full Scan Cost / Index Scan Cost (=20/80 * 100 =25).

Recommended Setting

Although there is no perfect OPTIMIZER_INDEX_COST_ADJ value for every OpenText PPM deployment, we recommend that you set this parameter to 25.

OPTIMIZER_MODE

The OPTIMIZER_MODE parameter establishes the default behavior for choosing an optimization approach for the instance.

Values:

• first_rows_n

  The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).

• first_rows

  The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

• all_rows

  The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

Recommended Setting

Set this parameter to all_rows.

OPTIMIZER_USE_SQL_PLAN_BASELINES

The OPTIMIZER_USE_SQL_PLAN_BASELINES parameter enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management Base, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.

Recommended Setting

Set this parameter to false. It should be set to true only when the optimizer_use_pending_statistics parameter is set to true.

PGA_AGGREGATE_TARGET

Automatic Program Global Area (PGA) memory management is enabled by default (unless PGA_AGGREGATE_TARGET is explicitly set to 0 or WORKAREA_SIZE_POLICY is explicitly set to MANUAL). PGA_AGGREGATE_TARGET defaults to 20 percent of the size of the SGA, unless explicitly set.

The PGA_AGGREGATE_TARGET parameter value determines the aggregate Program Global Area (PGA) memory available to all PPM Server processes attached to the instance. This parameter enables the automatic sizing of SQL working areas used by memory-intensive SQL operators such as sort, group-by, hash-join, bitmap merge, and bitmap create.

PGA_AGGREGATE_TARGET replaces the traditional SORT_AREA_SIZE parameter. Use it with the WORKAREA_SIZE_POLICY parameter set to AUTO.

Recommended Setting

Set the PGA_AGGREGATE_TARGET parameter value to a minimum of 450 MB. For very large databases, you can set the parameter to 1 GB.

PARALLEL_FORCE_LOCAL

PARALLEL_FORCE_LOCAL controls parallel execution in an Oracle RAC environment. By default, the parallel server processes selected to execute a SQL statement can operate on any or all Oracle RAC nodes in the cluster. By setting PARALLEL_FORCE_LOCAL to true, the parallel server processes are restricted so that they can only operate on the same Oracle RAC node where the query coordinator resides (the node on which the SQL statement was executed on).

Recommended Setting

Set the parameter value to true to avoid parallel operations across the cluster.

PROCESSES

The PROCESSES parameter value determines the maximum number of operating system user processes that can simultaneously connect to the Oracle database. OpenText PPM uses a pool of database connections. When database activity is required, connections are picked from the pool and the database activity is performed on this existing connection. This process saves the overhead of creating and cleaning up database connections.

Recommended Setting

Set the PROCESSES parameter value to 20 plus the number of total connections that might be used (MAX_DB_CONNECTIONS), times the number of nodes in your server cluster.

Although concurrent usage and usage nature are factors used to determine the connections. If a PPM Server cluster configuration is used, each PPM Server might use 60 database connections.

For single-server configurations, set the parameter value to 80 (the default). For a PPM Server cluster configuration with three nodes, set the parameter value to (3 x 60) + (3 x 20) = 240.

RESOURCE_MANAGER_PLAN

RESOURCE_MANAGER_PLAN specifies the top-level resource plan to use for an instance. The resource manager will load this top-level plan along with all its descendants (subplans, directives, and consumer groups). If you do not specify this parameter, the resource manager is off by default.

You can change the setting of this parameter using the ALTER SYSTEM statement to turn on the resource manager (if it was previously off) or to turn off the resource manager or change the current plan (if it was previously on). If you specify a plan that does not exist in the data dictionary, Oracle returns an error message.

Recommended Setting

Set the RESOURCE_MANAGER_PLAN parameter to none (the default value) to disable the resource manager.

SESSION_CACHED_CURSORS

The SESSION_CACHED_CURSORS parameter specifies the number of session cursors to cache. Repeated parse calls of the same SQL (including recursive SQL) or PL/SQL statement causes the session cursor for that statement to be moved into the session cursor cache.

Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor.

Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.

The SESSION_CACHED_CURSORS parameter is used to reduce the amount of parsing with SQL statements that use host variables and with PL/SQL cursors.

If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement.

If so, Oracle moves the session cursor associated with that statement into the session cursor cache.

Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse cannot be completely avoided; a "softer" soft parse is done that is faster and requires less CPU.)

Recommended Setting

Set the SESSION_CACHED_CURSORS parameter value to 200 or a greater value.

The value of SESSION_CACHED_CURSORS must be less than the value of OPEN_CURSORS.

After SESSION_CACHED_CURSORS is modified, Oracle needs to be restarted.

SGA_TARGET

The SGA_TARGET parameter value determines the maximum size of all System Global Area (SGA) components combined in the instance. If you specify SGA_TARGET, it is not necessary to specify individual values for SGA components such as SHARED_POOL_SIZE,JAVA_POOL_SIZE,LARGE_POOL_SIZE, and DB_CACHE_SIZE.

Recommended Setting

Set the SGA_TARGET parameter value to 1.66 GB. If you also set the SGA_MAX_SIZE parameter, its value must be higher than the value set for SGA_TARGET.

SHARED_POOL_RESERVED_SIZE

The SHARED_POOL_RESERVED_SIZE parameter helps to ensure that a portion of the shared pool (determined by the SHARED_POOL_SIZE parameter) is set aside for large objects. Reserving an area for large objects helps to ensure that requests for a large number of bytes do not fail as a result of shared pool fragmentation.

If you want to place an object in the reserved area, make sure that the object is larger than the SHARED_POOL_RESERVED_MIN_ALLOC value. We recommend that you use the default value for the SHARED_POOL_RESERVED_MIN_ALLOC parameter.

Recommended Setting

Set the SHARED_POOL_RESERVED_SIZE parameter value to 10 percent of the shared pool (as determined by the SHARED_POOL_SIZE parameter).

SHARED_POOL_SIZE

The shared pool contains shared cursors and stored procedures. The SHARED_POOL_SIZE parameter value determines the size (in bytes) of the shared pool. Larger values can improve performance in multiuser systems, but they use more memory. Smaller values use less memory, but they can degrade the performance of multiuser systems.

Recommended Setting

Set the SHARED_POOL_SIZE parameter value to at least 350 MB.

_SORT_ELIMINATION_COST_RATIO

For certain restrictive (with good filters specified) and limited (returns few records) searches, PPM Center uses the FIRST_ROWS_N optimization mode.

If a search such as this also uses SORT on one or more fields returned by the search, Oracle uses the INDEX on the sorted columns under the FIRST_ROW_N optimization, even if other indexes on supplied filters may yield to a better execution plan for a SQL statement. This often leads to a less desirable INDEX FULL SCAN on the index on sorted column.

Recommended Setting

Set the parameter value to 5. This directs Oracle to consider an execution plan with ORDER BY sort elimination, as long as the plan is no more expensive than five times the cost of the best-known plan (that uses sort).

WORKAREA_SIZE_POLICY

The WORKAREA_SIZE_POLICY parameter value determines whether work areas operate in automatic or manual mode. If the value is set to AUTO, work areas used by memory-intense operators are sized automatically based on the PGA memory that the system uses and the target PGA memory set for the PGA_AGGREGATE_TARGET parameter. If the value is set to MANUAL, work areas are set manually and based on the value of the *_AREA_SIZE parameter.

Recommended Setting

Set the parameter value to AUTO.