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 documentation 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 connections.
- 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 administrators possess this powerful system privilege.
For all other users, 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 database administrator assigns either an unlimited quota or no quota on each tablespace to user-schema geodatabase owners and data owners in the sde master geodatabase. In this way, the database administrator 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 or geodatabase owner account typically have additional training or experience and often know more about the storage requirements of their own GIS data.
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.