Supporting ArcSDE on Oracle
By Eric Pimpler, SBC Communications, Inc.

Editor's note: The author explains the requirements for running ArcSDE that are specific to Oracle and offers suggestions for optimizing performance.

ArcSDE serves as a gateway for managing geographic data in relational database management systems (DBMS) and makes data available to clients such as ArcMap, ArcCatalog, ArcIMS, and other applications serving data across the Internet. Implementing ArcSDE with a DBMS allows an organization to move from a traditional file-based collection of vector, raster, and CAD data to an integrated environment in which all spatial and business data is managed as a continuous database within the confines of traditional information technology infrastructure. It also makes this data available to the organization as a whole.

A flexible product that can be configured in numerous environments, ArcSDE supports a wide range of applications. However, the performance of client applications accessing spatial data through ArcSDE is dependent upon a number of factors including hardware, database configuration, ArcSDE configuration, client processing, and network bandwidth. This article examines some basic guidelines for tuning ArcSDE with an Oracle database--a common configuration.

An Introduction to Oracle and ArcSDE

An Oracle database is comprised of an instance and a collection of files stored on disk. The Oracle instance is made up of processes and memory structures. Server processes interact with Oracle clients, such as ArcSDE. They are responsible for processing SQL statements and reading and writing user data to and from the system global area (SGA). Background processes interact with the physical files that make up the database on behalf of the server processes. Data files make up the bulk of the database, storing objects such as tables and indexes.

ArcSDE uses a giomgr service to communicate with the database through Oracle's server processes. Each ArcSDE service has one giomgr process that listens for user application connection requests, cleans up disconnected user processes, and maintains the ArcSDE metadata tables. Each client application that connects to ArcSDE is assigned a gsrvr process spawned by the giomgr. The gsrvr submits all query and edit requests to the database through an Oracle server process.

Logical Database Design

Properly configuring Oracle for use with ArcSDE begins with an analysis of how the system will be used. In general, databases fit into one of two categories--online transaction process (OLTP) and decision support systems (DSS)--with some overlap inherent in most systems. OLTP databases are characterized by high data-change activity, such as inserts or updates, that is typically performed by a large user base. Examples of this type of system include order-entry systems, ticketing systems, and time-sheet entry systems. DSS databases offer somewhat different challenges. DSS databases are used to generate report information from large volumes of data. Whereas the user population for an OLTP system is typically large, the user population for a DSS application is often much smaller.

The key feature of a DSS system is fast access to large amounts of data. Most GIS applications fall into the DSS category since they typically access vast amounts of geographic and attribute data for use in analysis and data display applications. However, GIS systems will also exhibit OLTP patterns as data in the system is updated. Since the vast majority of GIS implementations are DSS databases, logical and physical designs should be planned accordingly.

The goal of any logical design is to configure the database so that its objects are separated by object and activity type. For instance, tables should be separated from their indexes, and high-activity tables separated from low-activity tables. This configuration will have dramatic effects on performance and ease of administration.

Although tables and indexes are typically thought of as objects, there are other types of objects such as views, sequences, and synonyms. Oracle logically stores objects in tablespaces supported by one or more data files at the operating system level. A separately named tablespace should be created for each type of Oracle data. For example, a typical ArcSDE implementation may provide a minimum of five tablespaces for storing geographic data--FEATURE, ATTRIBUTE, SPATIAL_INDEX, ORACLE_INDEX, and SDE. Each tablespace will store a different table or index that is used with ArcSDE and its associated client applications. The FEATURE tablespace stores the "F[n]" or feature tables created by ArcSDE while the ATTRIBUTE tablespace stores the business tables containing attribute information. SPATIAL_INDEX tables store "S[n]" or spatial indexes tables, and ORACLE_INDEX stores the Oracle indexes created on the feature, attribute, and spatial index tables. The SDE tablespace stores ArcSDE system tables. In addition to these ArcSDE specific tablespaces, the Oracle database should, at a minimum, also contain SYSTEM, ROLLBACK, TEMP, and USERS tablespaces to store various objects used by Oracle's internal processes.

Physical Database Design

All too often, the physical layout of the database is not planned. It is only considered when the database is experiencing performance problems. Just as the logical layout of the database should be planned, the physical layout of database files must be designed and implemented to meet the goals for the database. Disk input/output (I/O) contention is the most challenging performance bottleneck in any Oracle database. The physical layout of data files should begin with an identification of the relative I/O of the data files. To reduce contention, separate groups of objects across different disk drives and tablespaces.

Typically, tablespaces holding tables and indexes used by client applications will experience the most I/O. In an ArcSDE configuration, this will include the FEATURE, ATTRIBUTE, SPATIAL_INDEX, and ORACLE_INDEX tablespaces. If possible, the data files for each of these tablespaces should be placed on separate disk drives. A typical GIS client application such as ArcMap will access tables and indexes from each of these tablespaces during a normal session. For example, when a user zooms or pans in ArcMap, a query is submitted to ArcSDE to draw all the features within the application window. The query searches for features whose envelopes are within a range of x and y values first. To do this, the S[n]_IX1 index on the S[n] table will be accessed to return all feature identifiers within the application window. The feature geometry from the F[n] table will then be accessed using an index on the FID field. Business tables will also be accessed if an attribute query is involved. Client applications such as ArcMap perform better if each distinct tablespace is physically separated at the system level.

The SYSTEM tablespace holds the Oracle data dictionary and should be placed on a disk with moderate activity. Each time Oracle parses a SQL statement, it checks tables stored in the data dictionary. Only the data dictionary should reside in the SYSTEM tablespace.

A ROLLBACK tablespace is created to maintain data concurrency within the database. For each database transaction, Oracle creates a "before" image of the data in a rollback segment. This provides read consistency for queries started before a transaction is committed along with a mechanism for rolling back transactions. Esri recommends 50 MB be allocated for the ROLLBACK tablespace. In addition, the ROLLBACK tablespace should be separated onto separate disk drives.

A TEMP tablespace is created to store dynamically created data during large sort operations such as SELECT DISTINCT, UNION, and CREATE INDEX. When creating the temporary tablespace, keep in mind that creating an index requires twice as much temporary space as is required to store the index. In an ArcSDE database, the S_IX1 index on the spatial index table will normally be the largest index. Consequently, define the TEMP tablespace size to be at least twice the size of the spatial index. Creating TEMP tablespace on a disk drive separate from all other tablespaces is recommended.

The SDE tablespace, similar to the SYSTEM tablespace created to hold the Oracle data dictionary, holds the metadata used to manage an ArcSDE instance. The SDE tablespace can be created on the same disk drive as the SYSTEM tablespace.

Continued on page 2