ArcGIS does not require that you change your Oracle instance from its default configuration to run. However, for larger systems, you may want to make some changes to the Oracle instance configuration.
Whenever you start an Oracle instance, Oracle reads its initialization parameters from either the init.ora file or from the server parameter file, spfile.ora. Both files define the characteristics of the instance, but they are managed differently.
The init.ora file is located under the ORACLE_BASE/admin/<ORACLE_SID>/pfile directory or folder. Commonly, init.ora is a name given to the initialization file of an Oracle database instance, but for any given instance, the file is actually init<oracle SID>.ora. For example, if the Oracle system ID (SID) is GIS, the init.ora file for this instance would be initGIS.ora.
Changing parameters using the ALTER SYSTEM command will automatically be reflected in the server parameter file if the instance was started by that method. If the instance was started using an init.ora file, you will have to manually edit the file with a text editor if you want changes to system parameters to affect more than the current instance of the database.
The following are suggestions for setting parameters when implementing a large, highly used geodatabase in Oracle:
Parameters that affect shared memory
This section describes some of the parameters that control allocation of shared memory. For a detailed discussion of the Oracle initialization parameters, refer to the Oracle documentation for your Oracle release.
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. ArcGIS 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. Keep in mind that ArcGIS will open 80% of the specified number of open cursors, allowing Oracle processes to use the other 20%.
- Various ArcGIS data management cursors (20) +
- Various anonymous PL/SQL blocks (20) +
- Spatial queries—potential 6 per feature class +
- Log file queries (11) +
- Miscellaneous queries used when editing versioned tables—12 per versioned 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). In this case, the default setting of 300 OPEN_CURSORS is sufficient, as ArcGIS will hold open 240 cursors with this setting. However, if you frequently run out of cursors, you can increase the value of the OPEN_CURSORS parameter in increments of 50 or 100.
SESSION_CACHED_CURSORS
Oracle monitors the SQL statements that are submitted for each session. If it detects that 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.
SESSIONS
Beginning with ArcGIS 10.3, the geodatabase is configured to allow unlimited connections to the geodatabase. If you expect there will be a large number concurrent connections to the geodatabase, you may need to alter the Oracle SESSIONS parameter to accommodate this.
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 geodatabase 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.
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. Ensure that the PROCESSES parameter is at least as large as the number of geodatabase concurrent connections you anticipate plus 25 for a typical set of Oracle background processes.
Parameter that affects Oracle statistics
OPTIMIZER_MODE
Keep the default value (all_rows) for the Oracle parameter OPTIMIZER_MODE. This setting works best for most geodatabases and improves the overall scalability of your geodatabase.
Parameters that affect memory
Care must be taken when setting the initialization parameters that affect memory. Setting these parameters beyond the limits imposed by the physical memory resource of the host machine significantly degrades performance. In general, you should not allocate more than 70 percent of the server's physical memory to the databases on the server.
SHARED_POOL_SIZE
The shared pool is a component of the Oracle System Global Area (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.
Geodatabases can benefit from a larger shared pool than some other Oracle applications. ArcGIS 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.
Use automatic work area and shared memory management
You should take advantage of the mechanisms Oracle provides to automatically manage work area and memory allocation. See Oracle's Database Administrator's Guide for the version of Oracle you are using for information on how to configure work area and memory management.
Other changes
Though not an initialization parameter, the UNDO_POOL database resource manager plan directive can be set to allow an sde user consumer group a large amount of undo space for compress operations.
To use this, you will need to set up a consumer group for the sde user and alter the UNDO_POOL plan directive to allow for an unlimited undo pool for this consumer group.
UNDO_POOL identifies the total amount of undo space that the members of one resource group, collectively, may allocate at one time.
When using a versioned geodatabase for editing, you must periodically perform a compress operation to purge old information and simplify the contents of the geodatabase. If a large number of edits have occurred since the last compress operation, the new compress procedure can create large transactions that require a large amount of undo space. If UNDO_POOL is set too low, the compress operation can fail and poor performance can result. If possible, set an unlimited undo pool for the sde user's consumer group. Otherwise, you must monitor the size of the compress transactions and choose a suitably large undo pool size.