Supporting ArcSDE on Oracle
Continued...

Other Database Files

Oracle uses a control file to hold information about the database such as database identifier, creation date, tablespace names, data file names, and other important details necessary for the database to start and operate successfully. This file is updated continuously by Oracle during database use so it must be available for writing when the database is open. If, for some reason, the control file is not accessible, the database will not function. For this reason, a minimum of three mirrored control files should be created.

Online redo log files are used to record all transactions (e.g., inserts, updates, deletes) in the database. Oracle uses these files to minimize data loss in the event a database instance crashes. If an instance crashes, Oracle reads these files to recover committed data that has not been written to the data files. Oracle requires a minimum of two redo log files. Once one log fills up, Oracle begins writing to the next log file. This process, called a log switch, triggers a resource intensive checkpoint. Since most ArcSDE databases are relatively static, over time they should receive few changes. To minimize the number of log switches, Esri recommends the creation of two 50 MB online redo log files. Redo log files can be positioned with other data files since they aren't used much.

Oracle Instance Configuration Parameters

Database performance is determined to a large extent by how the Oracle instance is configured. Every time a database is started, a system global area (SGA) is allocated and Oracle background processes are started. SGA is an area of memory used for database information shared by the database users. The combination of Oracle background processes and the SGA is called an Oracle instance. On startup, Oracle uses the initsid.ora parameter file to configure the instance.

When an ArcSDE database is running on Oracle, a number of parameters are critical for high performance. The DB_BLOCK_SIZE and DB_BLOCK_BUFFERS parameters control the size of the buffer cache in the SGA. It is not uncommon to allocate one-third or more of the system's available memory to the buffer cache. The buffer cache stores previously accessed table and index data blocks in memory, thus reducing I/O demands on the system. A setting of 16,384 bytes for DB_BLOCK_SIZE is very good. A setting less than 8,192 may degrade performance. The DB_BLOCK_BUFFERS parameter works in conjunction with DB_BLOCK_SIZE to set the total amount of memory allocated for the buffer cache. For instance, using a setting of 100,000 for DB_BLOCK_BUFFERS along with a 16K DB_BLOCK_SIZE actually reserves 1.6 GB of memory for the buffer cache.

Another important parameter for an ArcSDE implementation, and DSS databases in general, is the DB_FILE_MULTIBLOCK_READ_COUNT parameter. This parameter determines the amount of data read from disk during a single read. As disk I/O can significantly degrade system performance, the fewer disk reads, the better. Although this parameter is often set to a higher value in DSS databases, ArcSDE seems to perform best when this parameter is set at 8. Higher values seem to adversely affect Oracle's execution plans.

The shared pool is another component of the SGA that holds both the data dictionary cache and the library cache. The library cache contains the most recently parsed SQL statements. The size of the shared pool is controlled by the SHARED_POOL_SIZE parameter. While 55 MB is the recommended setting, the parameters can be set as high as 75 MB.

The final component of the SGA is the redo log buffer that holds uncommitted changes to the database. The size of the redo log buffer is controlled by the LOG_BUFFER parameter. If memory is available, set this parameter to 10 MB.

Conclusion

These general guidelines for configuring Oracle to support an ArcSDE implementation are just a few of the many steps necessary for optimally configuring your site to serve spatial data to client applications. However, planning the layout of an Oracle database is the first step in obtaining adequate database performance.

Before reconfiguring Oracle, review the ArcSDE for Oracle Configuration and Tuning Guide, a PDF file that comes with ArcSDE. Esri offers a five-day instructor-led course, ArcSDE Administration for Oracle, that specifically addresses installation, configuration, data loading, and performance optimization issues associated with running ArcSDE on Oracle. Information about available Oracle database management training can be obtained at the Oracle Web site (www.oracle.com).

For more information, contact

Eric Pimpler
SBC Communications, Inc.
E-mail: ep4265@txmail.sbc.com

About the Author

Eric Pimpler has a master's degree in geography from Southwest Texas State University, a bachelor of science degree in geography from Texas A&M University, and is an Oracle Certified Database Administrator. He is currently the associate director of GIS for SBC Communications in San Antonio, Texas.