Memory tuning in Oracle

The following are a few general rules regarding configuration of the Oracle system global area (SGA) as well as memory structures affecting the size of an Oracle user's private global area (PGA). An SGA is a block of shared memory that Oracle allocates and shares with all sessions. For more information about SGA, refer to the Oracle Concepts Guide for your Oracle release.

• SGA must not swap.

  You should not create an SGA that is larger than two-thirds the size of your server's physical random access memory (RAM). Your virtual memory must be able to accommodate both the SGA and the requirements of all active processes on the server.

• Avoid excessive paging.

  Using your operating system tools (vmstat on UNIX systems and Task Manager on Windows), check for excessive paging. A high degree of paging can result from an SGA that is too large.

• Configure enough virtual memory.

  As a rule, Oracle recommends that your swap space be at least three to four times the size of your physical RAM. The required size of the swap file on UNIX or the page file on Windows depends on the number of active ArcSDE sessions. For every ArcSDE service (application server) session, a gsrvr process and a corresponding Oracle process are started. To determine the memory usage of these processes, use the ps –elf command on UNIX systems or the Processes tab of the Windows Task Manager. You must deduct the size of the Oracle SGA from the Oracle user processes. The total size of the ArcSDE gsrvr, the ArcSDE giomgr, Oracle user, Oracle background, operating system, and any other processes running on the server must be able to fit into virtual memory.

  For ArcSDE client applications that connect directly to an Oracle instance, the gsrvr process does not exist. Also, if the ArcSDE service is not used because all client applications connect directly to the Oracle instance, the giomgr process will not be started either. For this reason, direct connections have a smaller memory imprint on the server since the gsrvr process is absent. Parameters that affect memory include LOG_BUFFERSHARED_POOL_SIZE, DB_CACHE_SIZE, PGA_AGGREGATE_TARGET, SGA_TARGET, and WORKAREA_SIZE_POLICY.

  For an explanation of and suggested settings for these parameters, see Oracle initialization parameters.

• Use explicit quotas on tablespaces to avoid using up all available storage space.

  Users with privileges to create Oracle objects, such as the SDE user, the owner of a geodatabase stored in a user schema, and data owners, can access storage space through one of two methods: by possessing the UNLIMITED TABLESPACE system privilege or by receiving an explicit quota on a tablespace.

  The UNLIMITED TABLESPACE privilege allows a user to allocate an unlimited amount of space in any or all tablespaces in the database, including the Oracle-managed SYSTEM and SYSAUX tablespaces. This invites the possibility for an end user, intentionally or accidentally, to exhaust all available storage space and even to crash the Oracle instance. For this reason, it is best if only database administrator (DBA) users possess this powerful system privilege.

  For users who are not DBAs, you should assign a quota on one or more tablespaces to enable them to create Oracle objects in a controlled manner. For example, you might grant the GIS_ADMIN data owner user a quota on the GIS_DATA and GIS_INDEX tablespaces but not on the SYSTEM and SYSAUX tablespaces. This allows you to control where the data owner can create its tables and indexes and, optionally, how much space those objects can consume.

  Usually, the DBA assigns either an unlimited quota or no quota on each tablespace to project instance administrator and data owner users. In this way, the DBA controls where the data is physically stored,such as on a mirrored disk array for increased data protection, and can segregate data into logical containers separate from system data and data for other projects and applications. The unlimited quota allows the data owner to allocate as much space as necessary within the tablespaces to which it has access. This is generally appropriate because users with access to the data owner account typically have additional training or experience and often know more about the storage requirements of their own GIS data than does the DBA.

  In environments where data editors or data viewers are permitted to create their own geodatabase objects, such as output from geoprocessing operations, you may choose to assign a limited quota on the tablespaces to which those users have write access. For instance, on the GIS_DATA tablespace, data viewers might have a 100 MB quota, data editors have a 500 MB quota, and data owners have an unlimited quota. You should customize quota assignments to meet the specific needs of your data and business processes.