Oracle initialization parameters

Oracle initialization parameters
Release 9.3

• OPEN_CURSORS
The Oracle initialization parameter OPEN_CURSORS specifies the number of cursors a session can have open at any one time. The default value is 300. If the session attempts to open a new cursor but already has the maximum number of cursors open, the Oracle error -1000 will be returned.
ArcSDE holds open frequently executed cursors to improve performance. If your Oracle OPEN_CURSORS parameter is not set high enough, you will encounter the error mentioned above. Oracle's documentation indicates that setting the parameter to a large value has no adverse effects. Therefore, you can set the value extremely large, for example, to 2,000; this effectively removes any limit on the number of open cursors from a practical standpoint but still provides a measure of protection against a rogue process attempting to consume an excessive amount of cursor resources.
If instead you want to calculate the potential number of cursors a session has open, the following formula, based on your organization's data model, can be used as a guideline:
• Various ArcSDE data management cursors (20) +
• Various anonymous PL/SQL blocks (20) +
• Spatial queries—potential 6 per layer +
• Log file queries (11) +
• Miscellaneous queries used when editing multiversioned tables—12 per multiversioned table or layer
Therefore, an ArcMap application with 10 layers being edited in the document can potentially have 231 cursors open (20 + 20 + 60 + 11 + 120 = 231).
If you find you frequently run out of cursors, you can increase the value of the OPEN_CURSORS parameter by increments of 50 or 100.

NOTE: Oracle 10g is preconfigured to generate a server alert when the number of open cursors for the instance exceeds 1,200. Since 1,200 open cursors is not an uncommon condition for the geodatabase, you may want to increase the threshold for this alert to eliminate extraneous warnings in the alert queue.

One circumstance in which you might need to lower the parameter's value is when memory resources on the server running the Oracle instance do not have enough memory available for each Oracle dedicated process.
Obtaining the size of dedicated process memory requirements requires prototyping the application. Several Oracle parameters and the application's behavior, such as a SQL statement, can affect process memory requirements.
• SESSION_CACHED_CURSORS
Oracle monitors the SQL statements that are submitted for each session. If it detects the same statement has been submitted multiple times, it moves the statement to the cursor cache and keeps the cursor open for subsequent reuse. The SESSION_CACHED_CURSORS parameter controls the number of cursors allowed in the cursor cache.
The default value for SESSION_CACHED_CURSORS varies by Oracle release. If your instance is not configured to cache at least 50 cursors, increase the value of this parameter to 50.
• UNDO_MANAGEMENT and UNDO_TABLESPACE
Oracle stores multiple copies of data that is currently being modified by a user. While the transaction that modifies data is in progress, a copy of the original data is used to provide a read-consistent view of the database for other sessions. In addition, the modifying users may choose to undo work by issuing a ROLLBACK statement, or their process could crash in the middle of the transaction, requiring Oracle to undo their work in progress to restore the database to a consistent state.
To support each of these scenarios, Oracle stores the preedited data in a special data structure, an undo or rollback segment. You can set the UNDO_MANAGEMENT and UNDO_TABLESPACE parameters so Oracle will automatically create and manage undo segments.
To enable automatic undo management, first set UNDO_MANAGEMENT to auto. Next, set the UNDO_TABLESPACE to the name of the tablespace that will store the system-managed undo segments.
Be aware that you cannot use any arbitrary tablespace for storing system-managed undo segments. You must designate the tablespace as an undo tablespace at creation time. For more information on monitoring and managing automatic undo behavior, read the Oracle Database Administrator's Guide.
• SESSIONS
ArcSDE is configured to allow either 48 or 64 simultaneous connections by default, depending on your operating system. If you configure the SDE.SERVER_CONFIG data dictionary table to allow a larger number of connections, you may need to alter the Oracle SESSIONS parameter to accommodate the new setting.
The SESSIONS parameter directly limits the total number of concurrent sessions that Oracle will allow. If the default is insufficient to support the number of ArcSDE connections you expect, increase this parameter to the number of anticipated current connections plus a minimum of 10 percent more to support internal Oracle functions.

NOTE: If you use the Oracle shared server configuration, the SHARED_SERVER_SESSIONS parameter behaves like the SESSIONS parameter discussed above except that it only applies to shared server connections. All sessions—shared server and dedicated server—are limited by the more general SESSIONS parameter.

• PROCESSES
You can also limit the maximum number of processes that Oracle can create with the PROCESSES parameter. When using the dedicated server configuration, this process roughly corresponds to the number of concurrent sessions that the database will support. If you increase the number of connections that ArcSDE will allow, ensure that the PROCESSES parameter is at least as large as the number of ArcSDE connections plus 25 for a typical set of Oracle background processes.

• OPTIMIZER_MODE
Keep the default value for the Oracle parameter OPTIMIZER_MODE. For Oracle9i, the default value is choose; for Oracle 10g, it is all_rows. This setting works best for most geodatabases and improves the overall scalability of your geodatabase.

• LOG_BUFFER
The log buffer is a component of the Oracle System Global Area (SGA) that holds uncommitted changes to the database in memory until Oracle background processes have an opportunity to write those changes to permanent storage on disk. Because these writes occur so frequently—at least every three seconds—the log buffer does not require much space and can be configured at less than a megabyte in size.
The size of the redo log buffer is controlled by the LOG_BUFFER parameter. The default log buffer size is adequate for most geodatabases. However, for databases with a high degree of write activity, performance may be affected by multiple users attempting to access the log buffer simultaneously. Diagnosing and mitigating this condition requires advanced skills, such as monitoring latches and wait events. For detailed information, refer to the Oracle Database Performance Tuning Guide and to the MetaLink Knowledge Base.

NOTE: Setting the LOG_BUFFER to a large value to process huge loading transactions may, in fact, result in a performance reduction. Latch contention between transactions may occur if the log buffer is set too large.

• SHARED_POOL_SIZE
The shared pool is another component of the Oracle SGA that holds both the data dictionary cache and the library cache. The data dictionary cache holds information about data objects, free space, and privileges. The library cache holds the most recently parsed SQL statements.
Generally, if the shared pool is large enough to satisfy the resource requirements of the library cache, it is already large enough to hold the data dictionary cache.
Beginning with the ArcGIS Server Enterprise 9.2 release, ArcSDE geodatabases can benefit from a larger shared pool than some other Oracle applications, including earlier releases of ArcSDE. ArcSDE maintains a cache of SQL statements in memory submitted by client applications. A large shared pool enables more cursors to remain open, thus reducing cursor management operations and improving performance.
The size of the shared pool is controlled by the SHARED_POOL_SIZE parameter. ESRI recommends that you set the SHARED_POOL_SIZE parameter to a multiple of 16 MB to accommodate any system ESRI supports and that you set this parameter to at least 128 MB:

shared_pool_size = 128,000,000



Highly active geodatabases supporting volatile utility or parcel editing systems may require the SHARED_POOL_SIZE to be set as high as 250 MB.
Of the three SGA buffers, the shared pool is the most important. If the SGA is already as large as it can be given the size of your physical memory, reduce the size of the buffer cache to accommodate a larger shared pool.
• DB_CACHE_SIZE
The buffer cache is another component of the Oracle SGA that stores the most recently used data blocks. Data blocks are the Oracle atomic unit of data transfer. Oracle reads and writes data blocks to and from the database whenever the user edits or queries it. The size of the buffer cache is controlled by the DB_CACHE_SIZE parameter.
Unlike the shared pool and log buffer, there is no recommended minimum size for the buffer cache. Because your goal in sizing the buffer cache is to keep as much of the database in memory as possible, plan to allocate all remaining memory to the buffer cache after accounting for the needs of the rest of the system.
To estimate this, follow these steps:
1. Determine how much physical random access memory (RAM) your server has.
2. Multiply this number by 0.66 to determine the target size of the SGA.
3. Deduct the SHARED_POOL_SIZE and LOG_BUFFER to return the amount of memory available to the buffer cache.
4. Reduce this number by 10 percent to account for Oracle's internal memory usage.
5. Divide by the database block size to determine the DB_BLOCK_BUFFERS setting.
For example:

memory available to SGA = physical RAM * 2/3

memory available to buffer cache
= (memory available to SGA - (shared_pool_size + log_buffer)) * 0.9

db_block_buffers 

= memory available to buffer cache / db_block_size



• PGA_AGGREGATE_TARGET
Allocate space for the private global area (PGA) of the Oracle server processes. This space is typically used as a temporary buffer for sorting and merging data during a table join. Set the WORKAREA_SIZE_POLICY to AUTO, then initially set the PGA_AGGREGATE_TARGET to the total physical RAM multiplied by 0.16. Once the application has been in use for some time, tune the PGA_AGGREGATE_TARGET according to the procedure outlined in the Oracle Performance Tuning Guide and Reference.

workarea_size_policy = auto
pga_aggregate_target = <total physical RAM * 0.16)




NOTE: Oracle uses the PGA_AGGREGATE_TARGET to allocate memory for sorting only if the WORKSPACE_POLICY is set to AUTO. If it is not, Oracle will use the older manual method of managing sort area, which includes setting the SORT_AREA_SIZE and HASH_AREA_SIZE parameters.

• Use automatic work area management
Oracle can manage private memory for the server processes servicing user sessions automatically, much in the same way that it manages shared memory with automatic shared memory management (ASMM). SQL work areas for sorting, hashing, and bitmap index processing can be dynamically allocated by Oracle from a large pool of memory available for all PGAs rather than being configured at a fixed size by the DBA. This feature, automatic work area sizing, is available with Oracle9i and Oracle 10g.
To enable automatic work area management, set the parameter WORKAREA_SIZE_POLICY to AUTO. Then, configure the total amount of memory available to all PGAs by specifying the size as the value for the PGA_AGGREGATE_TARGET parameter. By default, Oracle will configure the PGA aggregate size at 20 percent of the size of the SGA. This is a good starting point, though the PGA should be sized based on the type of work performed by the server processes, not strictly relative to the SGA size. Once your database is running under normal load, you can monitor and fine-tune the size of the PGA target based on the actual workload. To learn more about this process, refer to the Oracle Database Performance Tuning Guide.
• Use automatic shared memory management
If you are using Oracle 10g, you have the option to configure a total size for the SGA and allow Oracle to automatically manage the distribution of memory among its constituent pools. This is ASMM, and it is invoked with the SGA_TARGET parameter.
In addition to simplifying the job of the DBA relative to configuring pools individually, using ASMM enables Oracle to continuously monitor the demands on each pool and dynamically adjust their sizes while the instance is running. It would be impractical for a DBA to perform this same level of supervision on an ongoing basis.
You can provide guidance for ASMM by configuring both the SGA_TARGET parameter to specify the total size of the SGA as well as one or more parameters for individual pools. When both SGA_TARGET and a pool size are specified, Oracle interprets the pool size as a minimum that ASMM will maintain for that cache.
Because of the importance of the shared pool to geodatabase performance, when using ASMM, set the minimum shared pool size (the SHARED_POOL_SIZE initialization parameter) to at least 128 MB as recommended above, in addition to setting SGA_TARGET.

NOTE: To use automatic work area and automatic shared memory management, the STATISTICS_LEVEL parameter must be set to TYPICAL (the default and recommended value) or ALL.