Oracle Components Overview

An Oracle server consists of both a database and an instance. In Oracle terminology, database refers to only the physical files on disk. These are the files that store the data itself, the database state information in the control file, and the changes made to the data in the redo log files. The term instance refers to the Oracle processes and memory structures that reside in the server’s memory and access an Oracle database on disk. One of the reasons for separating the concepts of a database and an instance is that a database may be shared by two or more different Oracle instances as part of an Oracle configuration that enhances the scalability, performance, and reliability of the Oracle server.

database

The collection of all physical files on disk that are associated with a single Oracle instance.

instance

The collection of memory structures and Oracle background processes that operates against an Oracle database.

It’s also important to differentiate between the logical and physical structures of the database. The logical structures represent components such as tables—what you normally see from a user’s point of view. The physical structures are the underlying storage methods on disk—the physical files that compose the database.

Logical Storage Structures

The Oracle database is divided into increasingly smaller logical units to manage, store, and retrieve data efficiently and quickly. The illustration below shows the relationships between the logical structures of the database: tablespaces, segments, extents, and blocks.

logical structures

Structures in an Oracle database that a database user would see, such as a table, as opposed to the underlying physical structures at the datafile level.

The logical storage management of the database’s data is independent of the physical storage of the database’s physical files on disk. This makes it possible for changes to the physical structures to be transparent to the database user at the logical level.

Tablespaces

A tablespace is the highest level of logical objects in the database. A database consists of one or more tablespaces. A tablespace will frequently group together similar objects, such as tables, for a specific business area or a specific function. A particular tablespace can be reorganized, backed up, and so forth with minimal impact to other users whose data may be in other tablespaces.

tablespace

A logical grouping of database objects, usually to facilitate security, performance, or the availability of database objects such as tables and indexes. A tablespace is composed of one or more datafiles on disk.

All Oracle databases must have at least one tablespace: the SYSTEM tablespace. Having more than one tablespace is highly recommended when creating a database. In the illustration of logical structures, you can see the SYSTEM tablespace and two others. Oracle’s Database Configuration Assistant, discussed later in this chapter, creates a total of 11 tablespaces for a default installation of Oracle9i.

Segments

A tablespace is further broken down into segments. A database segment is a type of object that a user typically sees, such as a table. Tablespace 1 in the logical structure illustration consists of three segments, which could be tables, indexes, and so forth. It’s important to note that this is the logical representation of these objects; the physical representation of these objects in the operating system files will most likely not resemble the logical representation.

segment

A set of extents allocated for a single type of object, such as a table.

Extents

The next lowest logical grouping in a database is the extent. A segment groups one or more extents allocated for a specific type of object in the database. Segment 2 in the logical structure illustration consists of two extents. Note that an extent cannot be shared between two segments. Also, a segment, and subsequently an extent, cannot cross a tablespace boundary.

extent

A contiguous group of blocks allocated for use as part of a table, index, and so forth.

Database Blocks

At the other end of the spectrum of logical objects is the database block (also known as an Oracle block), the smallest unit of storage in an Oracle database. Every database block in a tablespace has the same number of bytes. As of Oracle9i, different tablespaces within a database can have database blocks with different sizes. Typically, one or more rows of a table will reside in a database block, although very long rows may span several database blocks.

database block

The smallest unit of allocation in an Oracle database. One or more database blocks compose a database extent.

Oracle block

See database block.

Extents group together logically contiguous database blocks in a tablespace. All database blocks within a single extent will store the same kind of information.

A database block can have a size of 2KB, 4KB, 8KB, 16KB, or 32KB. Once any tablespace, including the SYSTEM tablespace, is created with a given block size, it cannot be changed. If you want the tablespace to have a larger or smaller block size, you need to create a new tablespace with the new block size, move the objects from the old tablespace to the new tablespace, and then drop the old tablespace.

Schema

A schema is another logical structure that can classify or group database objects. A schema has a one-to-one correspondence with a user account in the Oracle database, although some schemas may be designed to hold only objects that may be referenced by other database users. For instance, in the logical structure illustration, Segments 1 and 3 may be owned by the HR schema, while Segment 2 may be owned by the SCOTT schema.

schema

A named group of objects associated with a particular user account, such as tables, indexes, functions, and so forth.

A schema is not directly related to a tablespace or any other logical storage structure; the objects that belong to a schema may be in many different tablespaces. Conversely, a tablespace may hold objects for many different schemas. A schema is a good way to group objects in the database for purposes of security and access control.

Physical Storage Structures

From the perspective of building queries and running reports, regular users don’t need to know much about the underlying physical structure of the database on disk. However, DBAs do need to understand these database components.

The physical structure of the Oracle database consists of datafiles, redo log files, and control files. On a day-to-day basis, the DBA will deal most often with the datafiles, since this is where all of the user and system objects, such as tables and indexes, are stored. The illustration below shows the physical structure and its relationship to the Oracle memory structures and logical storage structures.

physical structures

Structures of an Oracle database, such as datafiles on disk, that are not directly manipulated by users of the database. Physical structures exist at the operating system level.

Datafiles

The datafiles in a database contain all of the database data that the users of the database save and retrieve. A single datafile is an operating system file on the server’s disk. Each datafile belongs to only one tablespace; a tablespace can have many datafiles associated with it.

datafiles

Files that contain all of the database data that the users of the database save and retrieve using SELECT and other DML statements. A tablespace comprises one or more datafiles.

There are four physical datafiles in the database in the physical structure illustration: one is used for the SYSTEM tablespace, two datafiles are assigned to Tablespace 1, and the fourth datafile is assigned to Tablespace 2.

Redo Log Files

The redo log files facilitate the Oracle mechanism to recover from an instance failure or a media failure. When any changes are made to the database, such as updates to data or creating or dropping database objects, the changes are recorded to the redo log files first. A database has at least two redo log files, and it is recommended that multiple copies of the redo log files be stored on different disks. (Oracle automatically keeps the multiple copies in synch.) If the instance fails, any changed database blocks that were not yet written to the datafiles are retrieved from the redo log files and written to the datafiles when the instance is started again.

redo log files

Files that contain a record of all changes made to both the data in tables and indexes, as well as changes to the database structures themselves. These files are used to recover changed data that was in memory at the time of a crash.

Control Files

The control file maintains information about the physical structure of the entire database. It stores the name of the database, the names and locations of the tablespaces in the database, the locations of the redo log files, information about the last backup of each tablespace in the database, and much more. Because of the importance of this file, it is recommended that a copy of the control file reside on at least three different physical disks. As with the redo log files, Oracle keeps all copies of the control file in synch automatically.

control file

A file that records the physical structure of a database, the database name, and the names and locations of datafiles and redo log files.

The control file and redo log file contents do not map directly to any database objects, but their contents and status are available to the DBA by accessing virtual tables called data dictionary views, which are owned by the SYS schema.

Oracle Memory Structures

The memory allocated to Oracle includes the following types of data:

• Data from user reading and writing activity

• Information about database objects

• SQL commands

• Stored procedures and functions

• Transaction information

• Oracle program executables

This information is stored in three major areas: the System Global Area (SGA), the Program Global Area (PGA), and the Software Code Area.

The overall memory allocated to Oracle can be divided into two categories: shared memory and nonshared memory. The SGA and the Software Code Area are shared among all database users. The PGA is considered nonshared. There is one dedicated PGA area allocated for each user connected to the database.

System Global Area

The System Global Area (SGA) is the memory area that is shared by all connected users of the database. The SGA is broken down into many areas. We will discuss the areas that hold cached data blocks from database tables, recently executed SQL statements, and information on recent structural and data changes in the database. These areas are known as the database buffer cache, the shared pool, and the redo log buffer, respectively.

System Global Area (SGA)

A group of shared memory structures for a single Oracle instance.

Database Buffer Cache

The database buffer cache holds copies of database blocks that have been recently read from or written to the database datafiles. The data cached here primarily includes table and index data, along with data that supports ROLLBACK statements.

database buffer cache

The memory structure in the SGA that holds the most recently used or written blocks of data.

Any database block can be in one of three states: dirty, free, or pinned.

Dirty buffers A dirty buffer contains data from a database block that has been changed or added due to an INSERT, an UPDATE, or a DELETE statement, but has not yet been written to disk. This buffer cannot be reused until it has been successfully written to disk.

Free buffers These buffers either never contained any data or have data that matches their corresponding database block on disk. Free buffers are available to be overwritten by another read operation from disk at any time. Oracle employs an LRU (least recently used) algorithm in the buffer cache; the longer a buffer has not been used, the more likely it is that it will be reused by a new database block read from disk.

LRU (least recently used) algorithm

An algorithm used to determine when to reuse buffers in the database buffer cache that are not dirty or pinned. The less frequently a block is used, the more likely it is to be replaced with a new database block read from disk.

Pinned buffers These buffers are currently in use by DML statements or are explicitly saved for future use, and therefore they cannot be reused.

Shared Pool

The shared pool contains recently used SQL and PL/SQL statements (stored procedures and functions). It also contains data from system tables (the data dictionary tables), such as character set information and security information. Because objects such as PL/SQL stored functions can be cached in the shared pool, another user or process that needs the same stored functions can benefit from the performance improvement due to the stored function already being in memory.

shared pool

An area in the SGA that contains cached SQL and PL/SQL statements and cached tables owned by SYS.

Redo Log Buffer

The redo log buffer keeps the most recent information regarding changes to the database due to SQL statements. The blocks in this buffer are eventually written to the online redo log files, which are used to recover, or redo, all recent changes to the database after a failure.

redo log buffer

A buffer in the SGA that contains information pertaining to changes in the database.

Program Global Area

The Program Global Area (PGA) belongs to one user process or connection to the database and is therefore considered nonsharable. It contains information specific to the session, and it can include sort space and information on the state of any SQL or PL/SQL statements that are currently active by the connection.

Program Global Area (PGA)

A nonshared area of memory used for storing all connection information, including SQL statement information, in a dedicated server configuration for a user who is connected to the database. In a shared server configuration, a large portion of the memory for each connection is stored in the SGA instead of the PGA.

Software Code Area

The Software Code Area is a shared area containing the Oracle program code or executables against the database. It can be shared by multiple database instances running against the same or different databases, and as a result, it saves a significant amount of memory on the server.

Software Code Area

A location in memory where the Oracle application software resides. The Software Code Area can be shared among several Oracle instances.

Background Processes

A process on a server is a section of a computer program in memory that performs a specific task. When the Oracle server starts, multiple processes are started on the server to perform various functions as part of the Oracle instance. While a detailed discussion of all Oracle background processes is beyond the scope of this book, we will discuss a few of the key processes: Database Writer (DBWn), Log Writer (LGWR), and System Monitor (SMON). These processes communicate with various areas of the SGA, such as the database buffer cache and the redo log buffer, as indicated in the earlier illustration.

process

An executing computer program in memory that performs a specific task.

Database Writer (DBWn)

There may be anywhere from one to ten copies (DBW0 through DBW9) of the Database Writer process running in an Oracle instance. As noted earlier in the section on the SGA, new and modified data is stored in buffers in the database buffer cache, which are marked as dirty buffers. At some point (for example, when the number of free buffers is low), these buffers need to be written out to disk, which is what the DBWn process does, allowing subsequent SELECT statements and other DML statements access to those buffers in the buffer cache.

If there is enough memory and the demand on the system is high, more than one copy of this process may dramatically improve the performance and reduce the response time when a query or DML statement is run.

Log Writer (LGWR)

The Log Writer process writes the buffers in the SGA’s redo log buffer out to disk to the redo log files. The Log Writer process must be able to write redo log buffers fast enough to make sure that there is room in the redo log buffer for entries from new transactions. By writing all changes to the database to the redo log files, the changes made to the database can be recovered by reissuing the commands in the logs if an instance failure occurs.

Log Writer writes under a variety of conditions: when a user issues a COMMIT, when the redo log buffer is one-third full, when DBWn writes dirty buffers, or every three seconds.

System Monitor (SMON)

SMON performs a number of different functions in the database. If there is a system crash, the SMON process will apply the changes in the redo log files (saved to disk previously by the LGWR process) to the datafiles the next time the instance is started. This ensures that no committed transactions are lost due to the system crash. (SMON also performs a number of other tasks that are beyond the scope of this book.)