Oracle Concepts - RDBMS Architecture Oracle Tips by Burleson Consulting

Oracle RDBMS Architecture

Oracle version 8i is an object-relational database management system (ORDBMS). Oracle8i (actually 8.1.5 production release) expands upon the new features offered in Oracle8 and makes  over 150 changes or additions to the Oracle tools. A traditional RDBMS stores data in tables called relations. These relations are two-dimensional representations of data where the rows, called tuples in relational jargon, represent records, and the columns, called attributes, are the pieces of information contained in the record. Oracle8i provides new features in the object-oriented extensions provided in Oracle8 as well as to the Oracle RDBMS. In an object-relational database, columns can represent either a single value (as in standard relational databases), a varray (a fixed number of additional records), or a REF to a second table where a variable amount of data can be stored. This takes the two-dimensional relational view and adds a third dimension. In addition, in an object-relational database, procedures known as methods can be tied to the tables. Methods are above and beyond the old concept of triggers, as we shall see later. In latter releases of Oracle8i, JAVA a new object oriented language, can be used to create stored objects in an Oracle8i database.

Oracle consists of background processes, files, and executables. Lets look at these items.

Background Processes

Oracle is more than just a collection of programs that allow ease of data access. Oracle can be compared to an operating system that overlays the operating system of the computer on which it resides. Oracle has its own file structures, buffer structures, global areas, and tunability above and beyond those provided within the operating system. Oracle controls its own processes, controls its own records and consistencies, and cleans up after itself.

Oracle as it exists on your system (with the exception of DOS or OS/2) consists of executables, five to nine (or more) detached processes, a global memory area, data files, and maintenance files. It can be as small as a couple of megabytes, or as large as a massive globe-spanning construction of gigabytes. A diagram showing a typical Oracle8 and Oracle8i environment is shown in Figures 1 and 2; you may want to refer to these diagrams as you read the next sections.

Figure 1: Oracle 8 Structures

Figure 2: Oracle8i Structures

On VMS, NT (threads), or UNIX there may be a minimum of eight detached processes for Oracle7; for Oracle8i, this jumps to nearly a dozen. Four of these are the base Oracle processes and these processes are started every time Oracle is started up on a system; the additional processes may be started if the database is using archiving, uses TCPIP, or is being run in parallel and/or distributed mode. The Oracle job queues, snapshot processes, advanced queuing options, and callout processes all add to the process count. These processes are listed below.

* DBWR—Database Writer --This process handles data transfer from the buffers in the SGA to the database files.

* LGWR—Log Writer -- This process transfers data from the redo log buffers to the redo log database files.

* SMON—System Monitor   -- This process performs instance recovery on instance startup and is responsible for cleaning up temporary segments. In a parallel environment, this process recovers failed nodes.

* PMON—Process Monitor  -- This process recovers user processes that have failed and cleans up the cache. This process recovers the resources from a failed process.

* ARCH—Archiver Process -- This process is active only if archive logging is in effect. It writes the redo log data files that are filled into the archive log data files.

* RECO—Distributed Transaction -- This is an Oracle7 process that resolves failed distributed processes.

* LCKn—Lock Process -- This process is used for inter-instance locking in an Oracle7 parallel server environment.

* Dnnn—Dispatcher -- This process allows multiple processes to share a finite number of Oracle7 servers. It queues and routes process requests to the next available server.

* Snnn—Servers -- This Oracle7 process makes all the required calls to the database to resolve a user’s requests. It returns results to the Dnnn process that calls it.

* LISTENERTCPIP server -- If you are running TCPIP, this process, known as the listener process, will be running as well (only one per node).

* CKPxx -- This is the checkpoint process that can be started to optimize the checkpoint operation for Oracle logging.

* Snpxx -- These are snapshot process and job queues. Thee can be up to 32 configured in 8i.

* EXTPROC -- These are the callout queues; there will be one for each session performing callouts. It is hoped that Oracle will multithread these processes or it could result in the callout feature being unusable in a large multi-user environment.

* QMNn -- These are the Oracle queue monitor processes (new for 8i). They monitor the queues used in the Oracle advanced queuing (Oracle AQ). There can be up to 10 QMN processes. These take the place of the single AQ_TXX process in previous releases.

On multiuser-capable systems, each user process may spawn several sub-processes depending on the type of activities being done by that process. Depending on how Oracle is configured, a single parallel query may start dozens of query slave processes for a single user!

Datafiles

In Oracle datafiles are the physical implementation of the logical concept of tablespaces. Each tablespace contains segments which can be one of a specific set of segment types:

* Data (both normal and partitioned)

* Index (both normal and partitioned)

* Rollback

* Temporary

Each segment is made up of extents and each extent is made from the finest level of granularity in the database, blocks.

Each datafile is allowed to sized either to 2, 4 or more gigabytes in size depending on operating system and patch level of Oracle.

Datafile placement has grown fuzzy in the last several years with the addition of RAID to the system administrators toolkit. It used to be before the wide spread use of RAID that a single disk platter could be assigned to handle a specific type of segment (and hence their datafiles). Now with striping, plaiding and other esoteric disk array arrangements it becomes harder and harder to determine what segment type is located on which disk.

As a DBA it will be your responsibility to:

1. Ensure datafiles are properly sized

2. Ensure, to the best of your ability, segment types are separated to ensure contention is realized

3. Ensure tablespaces are maintained to prevent fragmentation and poor space utilization

4. Ensure datafiles are properly backed up to allow for proper recovery in case of failure.

This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It’s only $19.95 when you buy it directly from the publisher here.