|
|
|
System Architecture
|
Tuning oracle for infrastructure applications
Primary Tuning Areas of the Oracle Database
The primary tuning areas consist of the following:
- Database Storage and Design
- Memory processes and Database Initialization Parameters
- SQL statements
The following illustration depicts the relationships existing in the Oracle database
including application users, other types of users, Oracle memory processes, and Oracle
database physical storage. The Oracle database and its associated instance consist of the
following required structures:
- Storage structures, physical and logical
- Processes, memory and physical
Oracle Database Architecture
Database storage and design
Tuning Oracle databases has not traditionally been a focus for GIS implementations as
the GIS was more than likely stored in proprietary files or data stores. Of course some
vendors have been moving to the idea of relational databases for the storage of GIS.
Data can then be retrieved by Enterprise Applications, thus integrating GIS with other
corporate business systems.
Often, an application that utilizes Oracle does not specify names of tablespaces for the
application tables, so they will have to be created in-house and assigned appropriate
sizing to fit the application needs. Choosing names that correspond either to the
application name or type such as GIS is a good rule of thumb. Note that if the application
utilizes indexing for the application tables, a tablespace will have to be created separately
for the indexes to avoid database contention while using the application. Logically, one
might utilize tablespace names such as GIS_DATA or _DATA for
the application tables and GIS_INDEX or _INDEX for the
application indexes.
Within the database creation scripts, it is equally important to modify the definitions of
tables, indexes and constraints to appropriate storage, growth, and tablespace parameters.
From experience it is assumed that the business or government personnel along with their
GIS experts will provide these parameters.
Note that some GIS applications are integrated with Oracle databases in some of the
following ways
- reference, or lookup, database structure
- spatial data structure
- or a combination
Certain vendors of GIS applications require usage of a vendor type of data store and
hence the primary usage of an Oracle database would be for reference, or lookup, data.
The variety of GIS spatial applications runs the gamut between only lookup data to all
spatial data residing in the Oracle database. Certain vendors have defined all of the
structures for the GIS location and descriptive data within Oracle. This combination will
require creative database management in the future if a variety of database structures are
operational in an environment possibly consisting of strictly GIS application requiring a
datastore only with Oracle lookup data and a potential AM/FM requiring full usage of the
Oracle database. The eventual design of the tablespaces and tables will be determined by
the GIS application requirements.
It is also important to set up users with the appropriate default and temporary tablespaces.
If these are not set at all, the critical SYSTEM tablespace is used, and will eventually
cause degraded performance due to contention with the Oracle instance. The general rule
of thumb is to have a USERS tablespace with enough room for storing any user database
objects such as tables, indexes, packages, procedures, functions, etc. Of course the
TEMPORARY or TEMP tablespace could be considered a standard for any space
required for the user's query sorts and other temporary data.
The amount of space allocated to a user for the default and temporary tablespaces can be
unlimited but will most certainly need to be restricted. Typically this number is limited
by the type of application, the size of sort area required by queries and the type of
database objects the user expects to create and utilize in the GIS application. Tablespace
size may also be affected by GIS applications that require new or modified database
objects.
|
|
|
|
