Craig S. Mullins & Associates, Inc.
Database Performance Management
An Introduction to the Architecture of Oracle
By Craig S. Mullins and Christopher T. Foot
Editor's note: As more sites establish multiple DBMS environments, developers and analysts need to understand the strengths and weaknesses of various client/server DBMS offerings. This article is third in a series on "Client/Server relational DBMS Architectures;" here, Christopher T. Foot and Craig S. Mullins examine the architectural "guts" or Oracle.
To effectively administer the Oracle Server environment, you will find it worthwhile to examine the architecture of Oracle in more detail. Seeing "what makes Oracle tick" can best be achieved by investigating the basic "architectural" blueprint of Oracle and examining its five basic components:
Instance vs. Database
An Oracle instance is the combination of all of the memory structures and background processes that are allocated when an Oracle database is started. Oracle users frequently confuse an Oracle instance with an Oracle database.
An Oracle database has both physical structures (data files) and logical structures (table, index). Oracle separates the physical from the logical structure so that the physical storage of data may be manipulated without affecting user access to the logical structures. The physical structure of an Oracle database is determined by the files created at the operating system level by the Oracle instance during database creation (controlfile, logfile) or by the DBA during normal operation (create table space, create control file).
The three physical file structures that comprise an Oracle database are:
Redo log files a record of changes made to data. The redo log files are used in recovery circumstances to ensure that no data is lost should a failure prevent changed data from being written to disk.
Database files a file that contains the database information, including both system and application data.
An Oracle parameter file contains all of the configuration parameters for an Oracle instance. These parameters can be set to different values to tailor the Oracle instance to the system's operating configuration as well as to the needs of the individual application or applications contained in the Oracle database. The parameter file specifications affect both memory and process settings of the associated Oracle instance. All parameters contained in the parameter file are optional and will default if omitted (see Oracle's Oracle 7 Server for UNIX Administrator's Reference, Oracle Corporation part no. A10324-1, for default values.)
Oracle database files
Oracle database files contain the data associated with a particular database. All of the files discussed are not absolutely required for normal database operations, but this configuration is highly recommended for a well-designed, efficient environment. Oracle database files can be grouped according to specific categories:
Oracle utilizes specific memory structures to perform DBMS-related tasks. These memory structures are contained in the main memory (RAM) of the computer running the Oracle instance.
The basic memory structures for an Oracle instance are the SGA (System Global Area), PGA (Program Global Area), and Oracle Sort Area.
The SGA is a group of shared memory structures allocated by the Oracle instance to contain data and control information. The SGA is automatically allocated when the instance is started and deallocated when the instance is shut down. If more than one user is connected to a multiple-process database, the information in the SGA is shared among the different users.
The SGA contains the following components:
Redo log buffer a staging area for redo log entries, similar to the database buffer cache.
Shared pool an area of memory set aside for the parsing and processing of SQL statements (shared SQL area), staging of Data Dictionary reads and writes (Data Dictionary cache), and, if the Oracle configuration is multithreaded, binding information and run-time buffers (private SQL area).
Every time a user process requests a sort to be performed a special memory structure is allocated called an Oracle sort area. The sort area exists in the memory of the user process that is requesting the sort. User processes typically perform sorting when query result sets are ordered or grouped.
Oracle processes are the true workhorses of the Oracle instance. Each process is composed of a series of tasks. Also, each process has its own internal memory allocated to it (the PGA). This memory allocation allows the process to perform the internal processing required to carry out its designated function.
Oracle has two general types of processes: user processes and Oracle processes. A user process is created to execute the program code of an application program. An Oracle process is called by another process to perform certain, specific functions on behalf of the invoking process.
Oracle processes can be further broken down into server processes and background processes:
Two levels of software must be executed by users accessing the Oracle server. The application or software tool on the client must be executed to initiate the connection to the Oracle server and transport the desired SQL statement to be executed. The server process on the Oracle server receives the connection request and its associated SQL statement and begins the process of executing the desired statements.
The Oracle server can be configured as either a single or multiple process instance. The different configurations are based on the operating system on which the instance will run and the processing requirements of the applications accessing Oracle data. Some operating systems (such as MS-DOS) cannot run multiple background processes concurrently. All of the code for the various background tasks required by the instance must be run in a single background process. These types of databases can be accessed by only one user at a time. This requires a single process Oracle instance.
The most popular class of configuration, however, is the multiple process instance. There are three possible multiple process configurations:
Figure 2 offers a complete picture of Oracle "under the covers." It contains all the components of Oracle that operate together to achieve an effective and useful relational database management system.
© 2005 Mullins Consulting, Inc. All rights reserved.