Share this page

# 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).

Note: We recommend that you set a value for this parameter, even if `SGA_TARGET` is set.

## 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.

Note: Consult your Oracle support when you want to use this hidden parameter.

## _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.

Note: Consult your Oracle support when you want to use this hidden parameter.

## 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.

Note: If multiple OpenText PPM test instances use the same database instance, you must set `GLOBAL_NAMES` to `false`.

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.

Note: Default selectivity varies between releases. For Oracle releases earlier than 9.2.x, the default selectivity is 25 percent, whereas in 9.2.x and later releases, default selectivity is five percent.

As of Oracle 9i, the `LIKE_WITH_BIND_AS_EQUALITY` parameter also enables equality costing for expressions in the following format.

`function(column) LIKE function(:bind)`

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.

https://admhelp.microfocus.com PPM 8942
Tips to improve your search results... OpenText™ Project and Portfolio Management Help Center PPM Help 26.2-26.3 All View Local Help Center View Online Help Center Version: