Filter
Top Products
Parameter Description
optimizer_mode Controls the mode that the optimizer uses to select the proper
execution path for fulfilling query results, among other functions.
Setting this to an incorrect mode can dramatically affect the overall
performance of the server.
Recommended setting for Sterling B2B Integrator: all_rows
SGA pool sizing:
As an alternative to manually sizing the main SGA pools in an Oracle database,
IBM recommends that you use Automatic Shared Memory Management (ASMM).
This feature controls pool size dynamically at run time, readjusting the sizes of the
main pools (db_cache_size, shared_pool_size, large_pool_size, java_pool_size)
based on existing workloads.
Set the following values to enable Oracle ASMM:
v Use an spfile for the init.ora values
v Set sga_target to a non-zero value
v Set statistics_level to TYPICAL (the default) or ALL
v Set shared_pool_size to a non-zero value
Rolling Back or Undoing Changes in Oracle Database
Oracle database supports AUTO UNDO management. Its use is recommended to
avoid manual monitoring of UNDO segments. Set the
UNDO_MANAGEMENT=AUTO parameter in init<SID>.ora. Your database
administrator needs to determine the UNDO_RETENTION setting.
Ensure that the file system where UNDOTBS1 tablespace is located has enough
space to use the AUTOGROW setting. As a starting point, size the undo tablespace
at 20% of the total database size.
Redo Logs
Redo logs are critical for database and instance recovery. Correct redo log
configuration is also critical for performance. We have seen best performance while
implementing redo logs on ASM. The following is recommended:
v Consider implementing redo logs on dedicated disk devices, preferably RAID 10.
v Consider implementing redo log group log files on alternating disks.
Redo File Size
Your choice of redo file size depends on your trade-off between performance and
availability, specifically the time required to recover the Oracle instance in the
event of a failure. For performance, some installations opt to create large redo logs
in order to reduce the frequency of log switches. However, this means potentially
more transactions in the redo logs that must be replayed during recovery.
The general rule for sizing redo log files is to consider the time it takes to switch
log files. Generally, do not switch logs more than once every 20 to 30 minutes.
Issue the following query to see how often the redo log files are changing:
Performance Management 25