CHAPTER 3
Tuning The Oracle Architecture

A holistic approach to the tuning of the overall Oracle architecture is paramount to the development of effective client/server systems. Regardless of how slick and well-tuned the client performs, poor response time at the server level will cause the entire development effort to fail. Whether your server is private to your application or shared between many client applications, the optimal usage of Oracle resources can ensure that the system performs at an acceptable level. Topics in this chapter include:

•  The Oracle architecture

•  Oracle’s internal structures

•  Oracle metadata—the V$ tables

•  Tuning Oracle memory

•  Simulating the pinning of database rows

•  Interoperability features

•  Creating a batch-oriented Oracle instance

•  I/O-based tuning

The Oracle Architecture

Oracle is the world’s leading relational database, continuing to dominate the marketplace for midrange computer platforms. Since Oracle’s inception in 1971, it has evolved to the point where Oracle7 bears little resemblance to the original offering. While Oracle has many extensions, the base product consists of the following components:

•  The Oracle SGA—This is the region in RAM memory that is created when Oracle is started.

•  SQL*Plus—This is the online interface to the Oracle database. Like SPUFI on DB2 and IDD for CA-IDMS, SQL*Plus is used to allow ad hoc queries and updates to be issued against the database.

•  SQL*DBA—This is the interface to Oracle for the database administrator. It allows the DBA to create databases, tablespaces, tables, clusters, indexes, and sundry other Oracle constructs.

•  Server Manager and Enterprise Manager—This is the visual reporting component to Oracle. Server Manager provides a graphical interface to online reports that give fast, visual access to the database. Server Manager can be run with Windows using Oracle SQL*Net for communications to the database, or it can be run on the individual server using Motif.

•  SQL*Net—This is the communications protocol that allows remote database servers to communicate with each other. SQL*Net is described extensively in Chapter 8.

•  PL/SQL—This is Oracle’s proprietary implementation of the ANSI SQL standard. PL/SQL can be used within Oracle applications tools called SQL*Forms or embedded into remote tools, such as C programs or PC GUIs. A fully functional programming language by itself, PL/SQL adds extensions to the SQL to allow sophisticated processing.

•  SQL*Menu—This is the menu builder for Oracle that allows individual SQL*Forms screens to be linked together.

The following list represents related Oracle products that also may be of interest, including some utilities:

•  Oracle Express—Formerly called Express by IRI Software, Oracle MultiDimension is a multidimensional database for supporting data warehouse and online analytical processing (OLAP). This product has yet to be integrated with the standard Oracle engine, and communications with the relational database are usually accomplished by extracting data from Oracle7 and loading it into Oracle MultiDimension.

•  Oracle Book—A text database, Oracle Book is used primarily for creating hypertext documents.

•  Import/Export—These are the utilities allowing the developer to dump the contents of tables into a flat file, and allowing the flat file to be restored to the database.

•  SQL*Loader—A utility that allows delimited flat files to be loaded into Oracle tables. For example, an extract from a DB2 database could be created with commas delimiting each table column. SQL*Loader would be used to import this flat file into an Oracle table.

•  Designer/2000—This utility is part of the Oracle family of CASE tools. Designer/2000 allows the developer to maintain logical table definitions and create a physical model from the logical structure.

Oracle’s Internal Structures

When Oracle is started, a region of memory is configured according to initial parameters that are defined in two files, the init.ora and the config.ora. These files tell the database software how to configure the system global area (SGA), which is the term used to describe a running Oracle.

Since the SGA resides within an operating system, it is dependent upon the operating environment (see Figure 3.1). In Unix, Oracle must share space with many other memory regions, competing for the limited memory and processing resources (as shown in Figure 3.2).

Figure 3.1  A sample Oracle instance in Unix.

Figure 3.2  The relationships between Oracle and applications.

The SGA consists of several main components, each of which is configured at database startup time:

•  Buffer cache

•  Log buffer

•  Shared pool

•  Private SQL areas

At the internal level, each Oracle instance has predefined interfaces to the hardware to allow it to communicate with the physical devices. Being a database software product, Oracle must be able to communicate with disk devices and CD-ROMs, retrieving and storing information from these devices. When the DBA defines a tablespace, a physical data file is associated with the tablespace, and Oracle will manage the addressability to this file. In other words, Oracle manages all of the mapping of the logical tablespaces to the physical data files.

The configuration of the SGA is critical to designing high performance client/server applications. The sizes allocated to the program pool and the buffer pools have a direct impact on the speed at with Oracle retrieves information. Remember, most business applications are I/O bound—the single greatest delay being the time required to access data from disk. As such, tuning for I/O becomes a critical consideration.