Optimizing performance in Oracle Database

Initialization parameters set values that can affect system performance, such as the optimizer features. Initialization parameters are stored in an initialization parameter file. You can change initialization parameters with ALTER SYSTEM commands.

Before you begin

To set parameters, you must log in as a user that has SYSDBA privileges.

Procedure

  1. Set the CURSOR_SHARING parameter to SIMILAR or FORCE so that literal values are converted to bind variables.
  2. If the database character set is a double-byte or Unicode character, set the NLS_LENGTH_SEMANTICS parameter to CHAR.
  3. Set the WORKAREA_SIZE_POLICY parameter to AUTO to automatically size the work areas.
  4. Ensure that the OPTIMIZER_FEATURES_ENABLE parameter is set to your current version of Oracle Database.
  5. Set the PROCESSES parameter to the maximum number of users and background processes that can access the database concurrently. For example, if you expect 50 concurrent users, set the value to 70 to include background processes.
  6. Set the OPEN_CURSORS parameter to the maximum number of open cursors that a session can have at one time. Open cursors handle private SQL areas. The number of open cursors that you require depends on your deployment. Set the value high enough to prevent Maximo® Asset Management from running out of cursors.
  7. Set the SESSIONS parameter to specify the maximum number of sessions that can be created. Set the SESSIONS parameter to a value that is based on the maximum number of users and background processes plus an allowance of 10% for recursive processes. For example, if you expect 50 concurrent users, set the value to 77, which can accommodate 20 background processes with seven sessions for recursive processes.
  8. Set the System Global Area (SGA) and Program Global Area (PGA) management parameters to a memory size that is based on the database size, number of concurrent users, and workload.
    Oracle Database version Parameters to set
    For Oracle Database 10g
    • SGA_TARGET
    • SGA_MAX_SIZE
    For Oracle Database 11g
    • SGA_TARGET
    • SGA_MAX_SIZE
    • MEMORY_TARGET
    • MEMORY_MAX_TARGET
  9. Set the TRANSACTIONS parameter to specify the maximum number of concurrent transactions. A larger value for this parameter means that the size of the SGA is also larger.
  10. To apply your changes, stop, and restart Oracle Database.


Feedback