Tuning CA Harvest Change Manager r7/r7.1 on Oracle

Products

• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

Tuning CA Harvest Change Manager all versions on Oracle

Environment

Harvest-Software Change Manager all versions

Resolution

Here are some good places to start tuning AllFusion Harvest Change Manager (Harvest) on Oracle for optimal performance.

Tune for Optimal Performance

It is impossible for Harvest to provide a specific tuning plan. The optimal tuning of Harvest DB will change with the amount of system resources available and data stored in the database. However, these are some points noted by customers and consultants.

Warning: Never change production settings without first verifying them on an identical test setup. Parameters do not always work as expected. Many changes that help one situation may hinder another. There are always exceptions for a rule. Please note the current value of each parameter and change only one thing at a time.

Please consult your DBA and the Oracle performance tuning documentation for more information.

Use the Latest Oracle Version

The optimizer becomes more intelligent with every Oracle release. Upgrading to the latest available version is the easiest and safest way to improve performance.

Tablespace Management

Consider using locally managed tablespaces with SEGMENT SPACE MANAGEMENT set to AUTO.

Spread HARVESTINDEX, HARVESTBLOB, and HARVESTMETA tablespaces across separately controlled disks.

Consider operating in automatic undo management mode.

Database Parameters

OPTIMIZER_INDEX_COST_ADJ (1-10000)

The default value of 100 is incorrect for most Harvest implementations. Lowering this parameter will make the CBO view indexes as less expensive to use. We recommend starting this value at 10 and then increase it by 10 until optimal performance is reached.

OPTIMIZER_INDEX_CACHING (0-99)

The default value of 0 is incorrect for most Harvest implementations. Increasing this parameter makes nested loop joins look less expensive to the optimizer. We recommend starting this value at 90 and lower by 10 until optimal performance is reached.

OPTIMIZER_DYNAMIC_SAMPLING

Oracle 9i default value does not handle optimization of temp tables properly. We recommend setting this value to 2. The default on 10g for this parameter is 2 so no change is necessary for 10g.

PARALLEL_MAX_SERVERS

The default value for parallel_max_servers is the number of CPUs on your server. Some customers have reported best performance with parallel_max_servers set to 0. alter system set parallel_max_servers = 0 scope=both;

DB_FILE_MULTIBLOCK_READ_COUNT

The maximum effective setting for db_file_multiblock_read_count is OS and disk dependant. It is very important that this parameter be set correctly for the current environment. This parameter indicates the number of blocks that are read by the operating system at one time. The optimizer uses this value to evaluate the cost of a full scan against an index. According to Oracle, this parameter should be set to the maximum I/O chunk size divided by the db_block_size.

For example: on Windows NT, if the chunk size is 128 and the block size is 8k, the parameter should be set to 128/(block size)8K = 16. Remember you can set different block sizes in different tablespaces.

OPTIMIZER_FEATURES_ENABLE

With each Oracle release, the optimizer changes the way it interprets available data and also introduces new features. Oracle offers this flag as a way to enable previous optimizer version behavior. We strongly recommend not setting this parameter to a prior Oracle version.

OPTIMIZER_MODE

When set to the default CHOOSE, Oracle uses the cost-based approach if statistics are available and a rule-based approach if they are not. We recommend leaving this value as CHOOSE and generating complete statistics on the database.

The CBO works based on statistics, if they are old or inaccurate, you will get a poor execution plan. If performance is poor, flipping the optimizer mode to see if performance improves may help identify if it is an optimizer problem. For example, delete statistics if they exist or generate them if they do not.

SGA_MAX_SIZE

This parameter should be carefully set based on available memory. A rough starting point would be to set this parameter to around ? of the total system ram.

SHARED_POOL_SIZE

The shared pool contains shared cursors, stored procedures, control structures, and other structures. Larger values improve performance in multi-user systems.

A starting point for this parameter is 50M for a normal Harvest installation. It must then be monitored using the v$db_shared_pool_advice or Statspack.

SORT_AREA_SIZE

A small but important component of SQL syntax, sorting is a frequently overlooked aspect of Oracle tuning. Sorts that cannot fit into the sort_area_size will be paged out into the TEMP tablespaces for a disk sort. Disk sorts are thousands of times slower than RAM sorts. Increasing this parameter from its default value up to 5MB may improve performance.

Consider enabling automatic sizing of SQL work areas, starting with setting pga_aggregate_target appropriately.

BUFFER_CACHE

A recommended starting value is around 50M. Monitor the hit ratio and then change accordingly.

JAVA_POOL_SIZE

Defines the memory used by java in Oracle, if you don't use Java set

JAVA_POOL_SIZE = 1000000. If you do use Java, a JAVA_POOL_SIZE = 33000000 is a starting recommendation.

LARGE_POOL_SIZE

The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers. Oracle calculates a default LARGE_POOL_SIZE only if PARALLEL_AUTOMATIC_TUNING is set to TRUE and LARGE_POOL_SIZE is unset.

A starting value would be to set LARGE_POOL_SIZE = 10000000 and increase as necessary.

DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE

This parameter determines the number of database block buffers stored in memory.

Please research them and set them appropriately. v$db_cache_advice and other Oracle tools can be used to estimate proper values.

SESSION_CACHED_CURSORS

This parameter lets you specify the number of session cursors to cache.

After the first ?soft parse?, subsequent ?soft parse? calls will find the cursor in the cache and do not need to reopen the cursor. To get placed in the session cache the same statement has to be parsed 3 times within the same cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed. Session cached cursors is a great help in reducing latching that takes place due to excessive soft parsing.

The suggested value to start testing with is SESSION_CACHED_CURSORS = 300

OPEN_CURSORS

This parameter specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.

A suggested value is to start at OPEN_CURSORS=800 and increase if needed.

CURSOR_SPACE_FOR_TIME

This parameter lets you use more space for cursors in order to save time. It affects both the shared SQL area and the client's private SQL area. The default is false. If CURSOR_SPACE_FOR_TIME is set to TRUE, the shared SQL areas remain pinned in the shared pool as long as an open cursor references them. This parameter should be used only if you have a sufficiently large shared pool to simultaneously hold all the processes' cursors.

Maintenance

Rebuild indexes periodically. Drop and recreate them if performance becomes unexpectedly bad. We have found in certain circumstances that recreating indexes returns better performance than index rebuild.

Ensure that statistics are up to date for best performance. Statistics need to be updated:

• On a regular schedule, based on normal changes in a database
• After inserting a significant amount of data
• When distinct values in primary columns change
• After creating indexes and tables

As a general rule, if you have never enabled statistics before, generate them first in a test database where performance can be verified before generating them in the production database.

Use DBMS_STATS, to generate statistics on the Harvest schema. Do not run this on the SYS schema.

DBMS_STATS has many options that must be carefully considered. Here is an example:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME=>'HARVEST', ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY');