Initialization parameters are stored in an initialization parameter file and can be applied to all database instances on a server. Parameters that affect system performance involve cursor sharing, the policy that determines work area size, the number of concurrent processes, and memory area sizes.
Setting | Starting value | Purpose |
---|---|---|
CURSOR_SHARING | SIMILAR or FORCE | Determines which SQL statements can share cursors. With the SIMILAR value or the FORCE value, SQL statements share cursors when differences between the statements do not affect the meaning or optimization of the statement. |
NLS_LENGTH_SEMANTICS | CHAR | Creates char and varchar2 columns that use character length semantics. |
WORKAREA_SIZE_POLICY | AUTO | Automatically sizes the work areas that are used by memory-intensive operators. |
OPTIMIZER_FEATURES_ENABLE | The current release number for the version of Oracle Database that you are using, such as 11.1.0.7 | Enables a series of features that optimize performance based on an Oracle release number |
SGA_TARGET | A numerical value for the memory size that is based on the database size, number of concurrent users, and workload. | In Oracle Database 10g, and Oracle Database 11g, sets the total size of all System Global Area (SGA) memory. When this parameter is set, the buffer cache, Java™ pool, large pool, and shared pool settings are all sized automatically |
SGA_MAX_SIZE | A numerical value for the memory size that is based on the database size, number of concurrent users, and workload. | In Oracle Database 10g, and Oracle Database 11g, specifies the maximum size of all SGA memory. |
MEMORY_TARGET | A numerical value for the memory size that is based on the database size, number of concurrent users, and workload. | In Oracle Database 11g, sets the system global area (SGA) or program global area (PGA) memory sizes. The SGA and PGA are dynamically sized as needed, based on this setting. |
MEMORY_MAX_TARGET | A numerical value for the memory size that is based on the database size, number of concurrent users, and workload. | In Oracle Database 11g, sets the maximum value that can be used in the MEMORY_TARGET parameter. |
PROCESSES | A numerical value that is based on the number of concurrent users that you expect to connect to the database. Add more processes for background processes. For example, if you expect 50 concurrent users, then you might set the PROCESSES parameter to 70. | Sets the maximum number of processes, both user and background processes, that can concurrently connect to the database. |
OPEN_CURSORS | A numerical value that sets the number of open cursors that are available. | Sets the number of open cursors, which handle private SQL areas. |
SESSIONS | A numerical value that is based on the number of concurrent users that you expect to connect to the database. Add more sessions for background processes and a 10% allowance for recursive processes. For example, if you expect 50 concurrent users, set the value to 77, which can accommodate 20 background processes with 10%, or 7 sessions, for recursive processes. | Sets the maximum number of sessions that can be created. |
TRANSACTIONS | A numerical value that specifies the maximum number of concurrent transactions. | Sets the maximum number of concurrent transactions. When this value is set to a higher number, the SGA is larger. |