DBMS initialization parameter recommendations
Release 9.2 Last modified December 3, 2010

Often, your default database management system (DBMS) initialization parameters will work fine with ArcSDE. However, if you have a large number of users accessing the system, there are some DBMS-specific initialization parameter settings you should make to help improve performance with ArcSDE. These recommended settings are listed in the sections below. You can use the following links to go directly to the section in which you are interested.

• SQL Server parameters
  
• Oracle parameters
  
• DB2 parameters
  
• Informix parameters
  

SQL Server parameters

Recommended settings for the SQL Server DBMS for it to work well with ArcSDE are as follows:

• Leave AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS enabled.
  
Setting these parameters to ON allows SQL Server to generate and update statistics automatically.


• Disable AUTO_SHRINK and AUTO_CLOSE.
  
AUTO_SHRINK shrinks database files automatically when more than 25 percent of the file contains unused space.
AUTO_CLOSE causes the database to automatically close and shut down when the last remaining user disconnects from the database. This process uses resources. Since user connections for ArcSDE are frequently started and ended by the user, having AUTO_CLOSE on would negatively impact performance.

Top of page

Oracle parameters

ArcSDE 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.

This section describes some of the parameters that control allocation of shared memory. For a detailed discussion of the Oracle initialization parameters, refer to Oracle Server Tuning 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.

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.

Parameter that affects Oracle statistics

• 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.

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.

• 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.

ArcSDE 9.2 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.

Alter the UNDO_POOL
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'll 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 multiversioned 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, 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 can fail and poor performance can result. If possible, set an unlimited undo pool for the SDE user's consumer group. Otherwise, you will need to monitor the size of the compress transactions and choose a suitably large undo pool size.

Top of page

DB2 parameters

Parameters that affect locks
Deadlocks may not be an uncommon occurrence, depending on the client application and the database configuration. Note that the problem may be aggravated with deep states lineages. Fortunately, DB2 provides tuning parameters to control the size of the lock list (LOCKLIST), the maximum percentage of locks an application can hold (MAXLOCKS), the amount of time a request will wait for a lock to be acquired (LOCKTIMEOUT), frequency interval for deadlock detection (DLCHKTIME), and deadlock rollback behavior (DB2LOCK_TO_RB).

The default value for LOCKLIST and MAXLOCKS in DB2 9 is AUTOMATIC, which enables these parameters for self tuning. This allows DB2's memory tuner to dynamically size the memory resources between different memory consumers. Automatic tuning only occurs if self tuning memory is enabled for the database (SELF_TUNING_MEM=ON).

To view lock list settings, issue the following command:

db2 get db cfg

Max storage for lock list (4KB)		(LOCKLIST) = 50
Interval for checking deadlock (ms)	(DLCHKTIME) = 10000
Percent. of lock lists per application	(MAXLOCKS) = 22
Lock time out (sec)			(LOCKTIMEOUT) = -1
Max number of active applications	(MAXAPPLS) = AUTOMATIC

For the DB2LOCK_TO_RB registry value, use db2set and look for
DB2LOCK_TO_RB=.

• LOCKLIST and MAXLOCKS
  
To increase the lock list capacity and lock escalation threshold, modify the LOCKLIST and MAXLOCKS parameters, respectively.

To set LOCKLIST for DB2 8:

1. Estimate the maximum number of active applications (MAXAPPLS, if set).
   
2. Estimate the average number of locks per application.
   


3. Estimate the lower and upper lock list size:
   



  (Avg # locks per application * 36 * MAXAPPLS) / 4096
    (Avg # locks per application * 72 * MAXAPPLS) / 4096



where:
72 = # bytes of first lock on object
36 = # bytes of additional locks on object


4. Set an initial LOCKLIST somewhere between the upper and lower bounds.
   

For example:


  db2 update db cfg using LOCKLIST 200



For DB2 9, there are new formulas for estimating LOCKLIST size. There are changes for 32-bit and 64-bit platforms. Refer to the LOCKLIST documentation in the DB2 Infocenter for details.

To set MAXLOCKS

1. Determine the percentage of lock list any single application can consume before lock escalation occurs. This could be a flat percentage or based on common transaction volumes.
   
For example, if applications are allowed twice the average number of locks:


 100 * (Avg  # locks per application * 2 * 72 bytes per lock)
  / (LOCKLIST * 4096 bytes)


Then set MAXLOCKS as shown in the following statement:


 db2 update db cfg using MAXLOCKS 22




2. Additional tuning of lock list parameters involves the use of the snapshot and event monitors. Look for the following information at the database level:
   
• Total lock list memory in use
  
• Number of lock escalations that have occurred
  



3. Use the event monitor for the maximum number of locks held by transaction.
   
For DB2 9, refer to the MAXLOCKS documentation in the DB2 Infocenter for details at http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0000268.htm.


• LOCKTIMEOUT
  
To set the amount of time DB2 will wait when attempting to acquire a lock, modify LOCKTIMEOUT.


• DLCHKTIME
  
To tune the period between deadlock detection checks, adjust DLCHKTIME.


• DB2LOCK_TO_RB
  
DB2LOCK_TO_RB specifies the behavior of the transaction when the amount of time waiting on a lock exceeds LOCKTIMEOUT.

By default, a lock time-out will roll back the request transaction. To change this behavior to only roll back the statement making the lock request, modify DB2LOCK_TO_RB with db2set DB2LOCK_TO_RB=STATEMENT. The default behavior should be fine for ArcSDE, though.

See the DB2 documentation or performance tuning guides for detailed information on properly setting these parameters.

Top of page

Informix parameters

Informix maintains its configuration parameters in the onconfig file located in the %INFORMIXDIR%\etc directory on Windows and the $INFORMIXDIR/etc on Unix. The parameters of this file control the server's memory use, the size and number of log files, temporary space, the location of the error logs, and much more. The onconfig file is read whenever the Informix server is started; therefore, changes to the parameters in the onconfig file require you to restart the server.

Naming the onconfig file

The standard onconfig file, onconfig.std, contains the default settings of the Informix parameters. Do not edit this file; instead, preserve it as a record of the default settings. On Windows, the Informix Dynamic Server (IDS) installation process automatically copies the onconfig.std file to the Onconfig file. On Unix systems, manually copy the onconfig.std file to a new file name such as onconfig.sde.

The onconfig file can be referred to as %INFORMIXDIR%\etc\Onconfig on Windows or $INFORMIXDIR/etc/<your copied file> on Unix.

On Windows, the installation sets the ONCONFIG system variable to Onconfig. The Onconfig file is also defined in the Windows registry as the Informix onconfig file. If you intend to use an onconfig file with a different name, you need to change the ONCONFIG environment in the registry and the %INFORMIXDIR%\setenv.cmd file.

On Unix systems, add the ONCONFIG system variable to the INFORMIX .cshrc or .profile file. For example, if you have named your onconfig file onconfig.sde, you would set the ONCONFIG variable to that.

setenv ONCONFIG onconfig.sde

Onconfig parameters

The following is a list of some of the more important onconfig parameters whose default values you should change to improve the performance of your Informix server when using it with ArcSDE. The first section lists parameters you should set before creating the geodatabase.

System parameters that must be adjusted prior to initialization

• TAPEDEV
  
The TAPEDEV parameter specifies the device used to back up the dbspaces. During the loading phase of your database, it is often a good idea to set this parameter to the NULL device (Windows) or /dev/null device (Unix). After the data is loaded, set the parameter to the proper tape device. The reason for this is the data is already backed up by the data source from which you are loading it; therefore, if a dbspace is lost to a disk failure, the data can be restored from the original data source. Once the database is loaded, you can set it to your tape device.

Windows


TAPEDEV  NULL



Unix


TAPEDEV  /dev/null




• LTAPEDEV
  
The LTAPEDEV parameter specifies the tape device to which the ONTAPE utility backs up the logical log files.

Set this to the NULL device (Windows) or /dev/null device (Unix). Once the server is up, you can set it to your tape device if you intend to archive the log files.

Windows


LTAPEDEV  NULL



Unix


LTAPEDEV  /dev/null




• NETTYPE
  
Network virtual processors are implicitly defined by the NETTYPE parameter. The NETTYPE parameter defines the number of poll threads allocated for each database connection type. The NETTYPE parameter defines the connection protocol, number of poll threads, concurrent connections per poll thread, and type of virtual processor that will run the connection's poll thread. A poll thread can be run by a CPU virtual processor or by a network virtual processor. The network virtual processors include SHM, SOC, STR, and TLI. All the network virtual processors are defined under the general virtual processor class NET. When the server is initialized, it starts one network virtual processor or each poll thread of the protocol defined by the NETTYPE parameter.

Set separate NETTYPE parameters to configure the poll threads for the shared memory and TCP/IP network protocols. The example settings below allow 20 local connections and 200 remote connections. Set the NETTYPE parameters to the expected number of local and remote connections, as in the examples below:

Windows


NETTYPE  olsoctcp,1,,NET



Unix
Solaris 2


NETTYPE  ipcshm,1,20,CPU
NETTYPE  tlitcp,2,100,NET



HP


NETTYPE  ipcstr,1,20,CPU
NETTYPE  soctcp,2,100,NET



IBM


NETTYPE  ipcshm,1,20,CPU
NETTYPE  soctcp,2,100,NET





NOTE: Poll threads can be run inline by a CPU virtual processor. Poll threads tend to run more efficiently this way, particularly on single processor computers. If you have a lot of CPU virtual processors, it is possible to run all the poll threads on them. However, as user activity increases and the CPU virtual processors become congested, it is a good idea to off-load the work of the poll threads to network virtual processors. Poll threads are processed faster when run by network virtual processors, but they must still wait for a CPU virtual processor to run the sqlexec thread that processes the client's request.



When you have a large number of remote clients, use network processors to run the poll threads for network connection protocols (soctcp or tlitcp, depending on the platform). Use the CPU processors to run the few local client poll threads (ipcshm or ipcstr, depending on the platform). Some examples are shown below:

Windows


NETTYPE  soctcp,2,100,CPU



Unix, Solaris 2


NETTYPE    ipcshm,1,20,CPU




• LOGFILES
  
The LOGFILES parameter specifies the number of logical log files created in your database when it is created or restarted. Logical log files comprise the logical log, which keeps a history of transactions since the time of the last storage-space backup. These allow you to keep the database in a consistent state if your server crashes in the middle of a transaction. This is useful during transactions, such as geodatabase synchronization; if you are in the middle of synchronizing a large amount of changes between the child and parent geodatabase when the server crashes, the logical log files will roll back the changes, keeping the databases in a consistent state.

To find out how to estimate how many log files to create, see Estimating the number of logical log-files in the IBM Informix Dynamic Server Administrator's Guide.

Unix-only parameters

• ROOTPATH
  
The ROOTPATH parameter specifies the initial chunk of the root dbspace. The default setting /dev/online_root causes the initialization of the Informix instance to fail unless you have actually created the device beforehand. Change the default setting from /dev/online_root path to the device of rootdbs space you have created. For example, after creating the device with the Unix touch command as the Informix user and setting its permissions to 660 with the Unix chmod command, set the ROOTPATH to the full path name of the root dbspace chunk file. If you are using a raw device, set the ROOTPATH to the full path name of the link to the raw device.


ROOTPATH  /disk1/informix_data/rootdbs




• MSGPATH
  
The MSGPATH parameter specifies the full pathname to the message log file to which the server the database is on will write status and diagnostic messages.

Update MSGPATH to reflect the location of your Informix installation. For example:


MSGPATH  /disk1/informix/online.log




• ALARMPROGRAM
  
The ALARMPROGRAM parameter specifies the full path of the script that will be executed when a log full event is issued. Set the parameter to log_full.sh to have the logical logs backed up automatically and to no_log.sh if you intend to back up the logs manually.

Update ALARMPROGRAM to reflect the location of your Informix installation. For example:


ALARMPROGRAM  /disk1/informix/etc/log_full.sh




• DBSERVERNAME
  
The DBSERVERNAME parameter specifies the unique name of the server. The DBSERVERNAME is assigned a communications protocol in the sqlhosts file. Typically the DBSERVERNAME is set to the server name that is associated with the shared memory communications protocol. The DBSERVERALIASES parameter normally holds the server name associated with the TCP/IP communications protocol.

Set this value to the lowercase name of your shared memory server. For example:


DBSERVERNAME  gis




• DBSERVERALIASES
  
Set this value to the lowercase name of your TCP/IP server. For example:


DBSERVERALIASES  gis_net

System parameters that can be set after initialization

• BUFFERS
  
The BUFFERS parameter file controls the size of the regular buffers—the area of memory in which Informix stores the most recently used pages of data.

Buffers are specified in system pages. The Informix server checks the regular buffers to find the pages it needs. If it doesn't find them, it reads them into the regular buffers before using them. Doing so avoids reading the pages from disk for each user, improving performance. Pages accessed from memory are much faster than from disk. The pages are maintained in the buffer as long as the instance remains up or until more recently accessed pages require the space. Informix recommends that, initially, the buffers occupy 20–25 percent of physical memory. For example, if the page on your system is 2 KB and you have 512 MB of physical memory, set BUFFERS to 65,536 to occupy 25 percent of the physical memory.

Ideally, you want to keep the ratio of disk reads to buffer reads as low as possible. You can monitor the ratio by periodically issuing onstat –p and examining the percent cached reads after the system has been up for a while. If this ratio falls below 90 percent for a decision support system, you should consider increasing the size of the regular buffers.

Increase the number of data buffers to 2,000 or 25 percent of your physical RAM, whichever is greater. If your pages are 2 kilobytes (page size can be determined with the Informix command onstat –b) and your physical RAM is 256 MB, BUFFERS would be calculated as follows:


BUFFERS = <physical RAM converted to kilobytes> * 25% / 
          <page size in kilobytes> 
        = (256 * 1024) * 0.25 / 2    
        = 32768

BUFFERS 32768




• LOGBUFF
  
The Informix server uses three log buffers to temporarily store changes. As one buffer flushes to a log file, a user thread can write to another one. The log buffers generally default to 32 kilobytes or pages. Set the LOGBUFF to an even increment of the page size.

Determine the optimal size of the logical log buffer with the onstat –l command after the system has been running for awhile in update mode. Onstat –l reports the current statistics of the physical log buffer and logical log buffer.

Under the Logical Logging section of the onstat –l output, if the value under pages/io is less than 75 percent of the value under bufsize, the logical log buffer is too large, and shared memory is being wasted. Reduce the onconfig LOGBUFF parameter.

If the value under pages/io is greater than 95 percent of the value under bufsize, the logical log buffer may be too small. Increase the LOGBUFF parameter.


• PHYSBUFF
  
The Informix server uses two physical log buffers to temporarily store the pages that are about to be changed, commonly referred to as the before image. A physical buffer flushes to the physical log file once it becomes full. As one buffer flushes, the other buffer becomes current and the user thread writes to it.

The PHYSBUFF should always be an even increment of the page size.

Determine the optimal size of the physical log buffer with the onstat –l command after the system has been running for awhile in update mode. Onstat –l reports the current statistics of the physical log buffer and logical log buffer.

Under the Physical Logging section of the onstat –l output, if the value under pages/io is less than 75 percent of the value under bufsize, the physical log buffer is too large, and shared memory is being wasted. Reduce the onconfig PHYSBUFF parameter.

If the value under pages/io is greater than 95 percent of the value under bufsize, the physical log buffer may be too small. Increase the PHYSBUFF parameter.


• LOGSIZE
  
The LOGSIZE parameter controls the default size of the logical logs. The size of the logical logs can be specified when they are created with the Informix onparams utility. However, if the size is not specified, LOGSIZE is used.
Set the logical log file size to 100,000 kilobytes. When the logical logs are moved out of the rootdbs, they will be created with this size.


LOGSIZE 100000




• LOG_BACKUP_MODE
  
The LOG_BACKUP_MODE parameter specifies the mode in which logical logs are backed up. This mode can be either continuous or manual. Continuous mode will allow you to automatically do logical log backups when required.


LOG_BACKUP_MODE CONT




• CLEANERS , LRU_MIN_DIRTY , and LRU_MAX_DIRTY
  
Each page read into the regular buffers is added to a least recently used (LRU) queue that tracks the frequency at which each page is accessed. If the regular buffers become full, the user sqlexec process selects an LRU queue at random to find pages that can be overwritten. The sqlexec process examines the free least recently used (FLRU) list of the LRU queue.

Pages are free if they have not been changed. Changed pages are referred to as dirty pages. They move from the FLRU list to the modified least recently used (MLRU) list. If no free pages are listed in FLRU, the sqlexec is forced to do a foreground write to clear pages from the MLRU queue. Foreground writes are performed one at a time and are resource intensive.

Page cleaners normally write dirty pages to disk. They perform inexpensive background writes. However, if the page cleaners are unable to keep up, foreground writes become necessary.

The onstat -F command can determine how often foreground writes have occurred. Avoid foreground writes by making sure enough LRU queues and page cleaners are available. The number of LRU queues is set by the LRU variable in the onconfig file. Informix recommends you set LRU to a minimum of 4. For multiprocessor machines, set LRU to four times the number of CPUs.

The onconfig parameter CLEANERS controls the number of page cleaner threads running. Set the number of page cleaners to 6 or the number of disks that contain frequently accessed data, whichever is higher.


CLEANERS 6



The LRU_MIN_DIRTY and LRU_MAX_DIRTY parameters determine when the page cleaners wake up and go to sleep. Page cleaners wake up whenever the percentage of dirty pages in the MLRU reaches the LRU_MAX_DIRTY threshold, and they clean the dirty pages, starting with the least recently used, until the percentage of dirty pages is less than LRU_MIN_DIRTY.

The default setting of the LRU_MIN_DIRTY is 50, and the default setting of the LRU_MAX_DIRTY is 60. If the onstat –F command shows a significant number of foreground writes and increasing the number of LRU queues and page cleaners does not have any effect, try lowering the LRU_MIN_DIRTY and LRU_MAX_DIRTY thresholds to shorten the queues.


• STACKSIZE
  
STACKSIZE specifies the amount of stack allocated to the Informix instance. For most applications, Informix recommends this parameter be left at its default value of 32 (kilobytes); however, for ArcSDE it is important to increase the size of this parameter to 64 (kilobytes) to support the Informix Spatial DataBlade user-defined data types (UDTs) accessed by ArcSDE.
Learn more about data types

Increase the initial stack size of each thread to 64 kilobytes by setting the STACKSIZE parameter to 64.


STACKSIZE 64




• RA_PAGES
  
This read-ahead parameter sets the number of data and index pages that are cached in the regular buffers whenever a sequential scan of one or more tables occurs.

Set the read-ahead pages to 125.


RA_PAGES  125




• RA_THRESHOLD
  
RA_THRESHOLD, the read-ahead threshold, specifies the number of remaining unread pages that triggers another call to read more pages from disk.

Set the number of unprocessed pages that trigger another read ahead to 85.


RA_THRESHOLD 85




• DUMPDIR
  
The DUMPDIR parameter specifies the location of the dump directory where error log files are written in the event of an assertion failure.

Leave the dump directory set to tmp if you have adequate space there. However, you can create a tmp directory under the Informix installation directory and set DUMPDIR to that. Should an assertion failure occur, the diagnostic files are one directory below the online.log file that references them.
Windows:


DUMPDIR C:\informix\tmp 



Unix:


DUMPDIR /usr/informix/tmp




• RESIDENT
  
The RESIDENT parameter specifies which portion of the Informix instances shared memory can be swapped out of the operating system's shared memory. Allowing as many portions of the instance's shared memory as possible to remain resident eliminates a large amount of input/output (I/O) and context switching of the instance's memory structures.

When the operating system switches between processes, it normally pages portions of process memory to disk. Process memory designated as resident is not swapped to disk, so setting the RESIDENT parameter to 1 keeps as many of the instance's memory structures as possible resident given the amount of physical memory and system resources available.


RESIDENT 1




• MULTIPROCESSOR
  
The MULTIPROCESSOR parameter specifies whether the Informix server machine has one or multiple processors to use.

Set to 0 if the Informix server machine has only one processor, and set to 1 if there are multiple processors.

Top of page

Please visit the Feedback page to comment or give suggestions on ArcGIS Desktop Help.
Copyright © Environmental Systems Research Institute, Inc.