Evaluate the current configuration

Before you begin to adjust the configuration of your database server, evaluate the performance of your current configuration. You can view the contents of your configuration file with onstat commands.

To alter certain database server characteristics, you must bring down the database server, which can affect your production system. Some configuration adjustments can unintentionally decrease performance or cause other negative side effects.

If your database applications satisfy user expectations, avoid frequent adjustments, even if those adjustments might theoretically improve performance. If your users are reasonably satisfied, take a measured approach to reconfiguring the database server. When possible, use a test instance of the database server to evaluate configuration changes before you reconfigure your production system.

When performance problems relate to backup operations, you might also examine the number or transfer rates for tape drives. You might need to alter the layout or fragmentation of your tables to reduce the impact of backup operations. For information about disk layout and table fragmentation, see Table performance considerations and Indexes and index performance considerations.

For client/server configurations, consider network performance and availability. Evaluating network performance is beyond the scope of this publication. For information about monitoring network activity and improving network availability, see your network administrator or see the documentation for your networking package.

Determine whether you want to set the configuration parameters that help maintain server performance by automatically adjusting properties of the database server while it is running, for example:
  • AUTO_AIOVPS: Adds AIO virtual processors when I/O workload increases.
  • AUTO_CKPTS: Increases the frequency of checkpoints to avoid transaction blocking.
  • AUTO_LRU_TUNING: Manages cached data flushing as the server load changes.
  • AUTO_READAHEAD: Changes the automatic read-ahead mode or disables automatic read-ahead operations for a query.
  • AUTO_REPREPARE: Reoptimizes SPL routines and reprepares prepared objects after a schema change.
  • AUTO_STAT_MODE: Enables or disables the mode for selectively updating only stale or missing data distributions in UPDATE STATISTICS operations.
  • AUTO_TUNE: Enables or disables all automatic tuning configuration parameters that have values that are not present in your configuration file.
  • DYNAMIC_LOGS: Allocates additional log files when necessary.
  • LOCKS: Allocates additional locks when necessary.
  • RTO_SERVER_RESTART: Provides the best performance possible while meeting the recovery time objective after a problem.