Tuning oracle for infrastructure applications
Sue A. Johnson
Geographic Information Technology, Inc.
Introduction
In this paper, certain theory and methods will be explored for tuning Oracle in GIS
infrastructure applications specifically focusing on GIS applications. With the advent of
GIS applications and technology becoming a corporate core technology, GIS is
increasingly becoming interdependent on databases such as Oracle. There are also the
wide selection of server technologies that provide a high availability and scalability for
the enterprise-wide technologies and applications such as GIS and Oracle databases.
The current versions of Oracle such as Oracle 8 and 8i combined with the available
server technologies provide a highly available and scalable architecture for business and
government application solutions. Combining GIS with Oracle databases on servers
provides increased performance and throughput. Typically Oracle and other scalable
databases will be a key component of GIS technologies utilized by business or
government to meet their current GIS application requirements.
The current dependency of GIS on the incorporation of Oracle databases varies by
vendor and application. Increased usage of Oracle increases the necessity to ensure that
the Oracle database is properly set up and tuned for the particular GIS application
requirements. These types of GIS applications including AM/FM, land information
systems, e-business and other internet applications, and future usages will require
different types of Oracle database environments. Given the variety of GIS vendors and
their respective GIS application offerings, there will be a discussion of the rules of thumb
for tuning the Oracle database environment. The focus of tuning the Oracle database will
specifically include the possible ways to improve the Oracle database environment
including storage, memory and other processes, and the SQL that is used to generate data
from the Oracle database in the GIS infrastructure application.
Oracle has been scalable and reliable since the release of 7.1 of the data repository. Today
Oracle on a variety of servers can process massive amounts of data for a variety of
applications. Over the last decade with Oracle 8 and 8i, Oracle Corp. has surpassed all
database technology by providing business with the capability to deliver data stores in the
Oracle database that can be highly tuned, scalable both in physical and user size, and
highly available. Oracle is scalable on Unix and NT platforms with seamless integration
with other databases and tools via ODBC (open database connectivity).
GIS as an infrastructure application
In the past, GIS applications were not dependent upon Oracle databases, as the data has
historically been retrieved from GIS file systems. GIS applications are becoming a key
component of the enterprise-wide information infrastructure that may link many types of
data resources. These enterprise information sources may include the following data
sources:
- relational
- object
- spatial
- legacy systems
- video
Further, this information may be ported to an Internet application for retrieval by
worldwide users. With this in mind, it is no longer possible to segregate the business of
tuning databases from GIS applications because the Oracle database may be a key
component of the GIS application.
When some or part of the application(s) depend upon the Oracle database for some or all
of its information, there needs to be a significant amount of attention paid to the tuning of
the Oracle database. This will primarily include tuning Oracle's physical and memory
related processes and SQL statements.
Each type of application may have different requirements for tuning. Applications
implementing long transactions require large amounts of memory and disk space in order
to store all stages of the transaction. Short transaction applications are less memory
intensive but may require extensive network traffic. In any case, knowledge of the
database internal views and tables is imperative in order to effectively tune the Oracle
database.
