Notes about Various Oracle Parameters

January 25, 2010

There are a large number of initialization parameters that control the behavior of Oracle, and thus the performance of the database server.  It would likely take hours (or many, many pages) to explain the ideal value of each of these parameters – the ideal parameter values are different for different databases, otherwise Oracle Database could simply default to the ideal parameters.  Below are a couple of my notes on various parameters.

O7_DICTIONARY_ACCESSIBILITY: when set to true, permits non-sysdba users to query the data dictionary without explicitly granting permission to the users to view the data dictionary (setting this parameter to TRUE also allows the SYS user to connect without specifying AS SYSDBA).  This parameter must be set to TRUE for certain applications’ functions to work correctly, but ideally should be set to FALSE, if possible.  This parameter is set to FALSE by default on 9.0.1 and above.  Changing the parameter requires bouncing the database.

COMPATIBLE: Sets the datafile level binary compatibility, allowing the database binaries to be rolled back to an earlier version and still access the database’s datafiles.  Note that this parameter’s purpose is incorrectly described as controlling the query optimizer in a couple of Oracle related books.

PLSQL_CODE_TYPE: Watch out – there is a bug, at least on the Windows platform of 10.2.0.x where this setting will automatically change from interpreted to compiled when parameter values for other parameters are changed using Enterprise Manager Database Control.

PROCESSES and SESSIONS: Control the maximum number of client connections which may connect at any time.  The SESSIONS parameter is typically 10 to 20 greater than the value for PROCESSES.  The database must be bounced to change these parameters.  See here for a related article.

RECYCLEBIN: Controls whether or not dropped tables and indexes will be saved to an area which will permit the objects to be recovered.  If applications are using non-standard methods of determining objects belonging to a user (for example, directly querying SYS.OBJ$ and SYS.USER$), it is possible for the objects in the recylebin to be listed with the normal tables – attempting to assign permissions to objects in the recyclebin will result in the database returning errors to the client.

TIMED_STATISTICS: Should be set to TRUE to permit most forms of performance tuning, when set to FALSE, time deltas between events are not calculated.  Setting this parameter to TRUE may impose a small performance penalty on database performance on some operating systems, but the penalty is typically small.  This parameter defaults to TRUE when the STATISTICS_LEVEL parameter is set to TYPICAL or ALL.

DB_DOMAIN: Allows a database SID to be suffixed with a DNS style domain name.  If set, it may cause problems when database links are created between databases (only one name is valid for a database link when the DB_DOMAIN parameter is set, a custom name cannot be assigned to the database link).

UNDO_MANAGEMENT: When set to AUTO, rollback segments are no longer used – instead, the system automatically manages undo segments.

UNDO_RETENTION: Specifies the suggested minimum number of seconds that undo information should remain available.  Used to limit the frequency of “snapshot too old” error messages.

DB_RECOVERY_FILE_DEST_SIZE: Specifies a hard upper limit of the number of bytes available to store archived redo log, backups, and other items in the flash recovery area.  Note that if files are removed from the flash recovery area using operating system commands, Oracle may incorrectly calculate the space used in the flash recovery area, potentially creating problems if copies of archived redo logs and/or backups are sent to the flash recovery area.

DB_RECOVERY_FILE_DEST: Specifies the location to be used for the flash recovery area.

DB_WRITER_PROCESSES: Do not increase from the default value of 1 unless the server has more than 8 CPUs.  See here for a related article.

STATISTICS_LEVEL: Should be set to TYPICAL, do not leave the parameter set to ALL at the system-wide level as it will significantly slow down performance as more performance data must be collected for each SQL statement executed.  The performance hit when set to ALL is more significant on Oracle 10g than it is on Oracle 11g.

SGA_MAX_SIZE: Specifies the absolute maximum size of memory allocated to items in the system global area (SGA), defaults to the value of SGA_TARGET if not set.  Requires bouncing the database to change the parameter’s value.

SGA_TARGET: Specifies the suggested maximum amount of memory to be allocated to items in the system global area.  The value may be manually increased to the value of SGA_MAX_SIZE without bouncing the database.

SHARED_POOL_SIZE: When the SGA_TARGET is specifies, sets the minimum amount of memory available for caching items in the shared pool (SQL statements, packages, etc.).

TRACE_ENABLED:  Allows the database to create extended trace and 10053 trace files when session request those trace files to be generated.  Typically default to a value of TRUE.

MEMORY_MAX_TARGET: Part of the new memory management parameters in Oracle 11g, specifies the absolute maximum amount of memory that may be used by Oracle.

MEMORY_TARGET: Part of the new memory management parameters in Oracle 11g, by default 80% of this memory will be allocated to the SGA and 20% to the PGA.

DB_CACHE_SIZE: Specifies the minimum amount of memory in bytes for the DEFAULT block buffer cache (KEEP and RECYCLE buffer cache sizes do not subtract from this value).

LOG_BUFFER: Specifies the amount of memory to be allocated for buffering redo information before it is written to the redo logs.  512KB to 1MB is typically sufficient on older Oracle releases, 10g and above may automatically set this parameter’s value to a size close to the memory granule size, which may be 16MB.

WORKAREA_SIZE_POLICY: When set to AUTO, allows the automatic allocation of memory for work areas from the memory specified for the PGA_AGGREGATE_TARGET.

SORT_AREA_SIZE: Has no effect when WORKAREA_SIZE_POLICY is set to AUTO (assuming dedicated sessions), specifies the amount of memory that may be used during a sorting or hashing operation when executing a SQL statement.  HASH_AREA_SIZE defaults to twice this value.

SORT_AREA_RETAINED_SIZE: Has no effect when WORKAREA_SIZE_POLICY is set to AUTO, specifies the amount of memory that may be used after a sorting operation when the client is retrieving the results of the SQL statement.  If the server has sufficient memory, set this value to the same as the value of SORT_AREA_SIZE to avoid unnecessarily spilling the results to the temp tablespace after the sort, but before the client starts retrieving the results.

OPEN_CURSORS: Specifies the maximum number of cursors that may be simultaneously open for each client’s session.  Depending on the application connecting to the database, a value between 300 and 1000 might be a safe target if there is sufficient memory on the server.

SESSION_CACHED_CURSORS: On older Oracle releases, this parameter defaults to 0, and on more recent releases the parameter defaults to either 20 or 50 (this parameter controls the number of cached cursors per session).  If the value of this parameter is set to a non-zero value and the same SQL statement is submitted at least 3 times, the SQL statement is added to the session cached cursors and remains open even when the client explicitly closes the cursor.  The helps reduce the performance hit caused by soft parses when the client repeatedly submits the same SQL statement to be executed – on the next parse request Oracle does not need to search the library cache as would be needed during a soft parse.  A value of 50 to 100 probably would be a good target, and if server memory permits, consider setting this parameter to a higher value, possibly 200.

CURSOR_SHARING: Starting with Oracle 8i, it is possible for the database server to automatically convert constants (literals) submitted in SQL statements to bind variables in order to reduce the number of hard parses.  There are problems in patched Oracle 10.2.0.2 and 10.2.0.3 when this parameter is set to anything except EXACT (the October 2006 CPU for Oracle 10.2.0.2, for example, introduces problems when the CURSOR_SHARING parameter is set to FORCE – the problem may appear a couple of hours after the database is used in production).

CURSOR_SPACE_FOR_TIME: This parameter will be removed from future releases of Oracle as it is often misused (removed from 11.2.0.1?).  When set to TRUE, this parameter causes Oracle to assume that required SQL statements will not be prematurely aged out of the library cache.

OPTIMIZER_INDEX_CACHING: Tells Oracle the approximate percentage of index blocks that remain in the buffer cache – primarily has an effect during nested loop joins, affects costing of nested loop joins and in-lists.

OPTIMIZER_INDEX_COST_ADJ: Artificially lowers the calculated cost of an index access to the percentage of the original specified by this parameter.  Due to rounding problems, may cause the wrong index to be used if this parameter is set to too low of a value.  If index access costs are calculated too high compared to full table scans (and fast full index scans), use CPU (system) statistics, if available, to increase the cost of full table scans, rather than using this parameter to decrease the cost of index accesses.

OPTIMIZER_FEATURES_ENABLE: When adjusted, automatically changes the value of many hidden initialization parameters to permit the query optimizer to behave similar to the optimizer in an earlier release of Oracle.

OPTIMIZER_SECURE_VIEW_MERGING: Defaults to TRUE on 10g, and may cause performance problems when set to TRUE when a user accesses a view created by another user, while the performance problem is not present for the view owner.

DB_FILE_MULTIBLOCK_READ_COUNT: Controls the maximum number of blocks that may be fetched in a single read operation during a full table scan or fast full index scan.  Oracle 10.2.0.x and above is able to auto-set the DB_FILE_MULTIBLOCK_READ_COUNT, which will likely set the parameter to permit multi-block reads of 1MB.