Here are some good places to start tuning AllFusion Harvest Change Manager (Harvest) on Oracle for optimal performance.
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 = 300OPEN_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.
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');