Oracle Database XE: Storage Structures

In this second part of a five-part series on Oracle Database XE administration, you’ll learn about physical database structures. This article is excerpted from chapter 28 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Physical Storage Structures

From the perspective of building queries, developing applications, and running reports, regular users, managers, and developers don’t need to know much about the underlying physical structure of the database on disk. However, even a part-time DBA does need to understand these database structures. For example, you need to know where the database’s datafiles reside on disk and how to best optimize their placement when performance becomes an issue.

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. Figure 28-2 shows the physical structure and its relationship to the Oracle memory and logical storage structures.

Figure 28-2.  Oracle Database XE physical storage structures


The datafiles in a database contain all of the database data that the users of the database add, delete, update, and retrieve. A single datafile is an operating system file on a server’s disk drive. This disk may be local to the server or a drive on a shared storage array. Each datafile belongs to only one tablespace; a tablespace can have many datafiles associated with it.

There are seven physical datafiles in the database in Figure 28-2. There are two for the SYSTEM tablespace; one for the SYSAUX tablespace; one for the TEMP tablespace; two for the USERS tablespace; and one for the UNDO tablespace.

Redo Log Files

The Oracle mechanism to recover from an instance failure or a media failure uses redo log files. When users or system processes make changes 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. Oracle best practices recommend that you store multiple copies of the redo log files on different disks; Oracle automatically keeps the multiple copies in sync. If the instance fails, any unrecorded changes to database blocks not yet written to the datafiles are retrieved from the redo log files and written to the datafiles when the instance starts again; this process is known as instance recovery. By default, Oracle does not mirror the redo log files on different disks; we’ll show you how to do that in Chapter 40. For development, the default redo log configuration is sufficient.

Control Files

The control file maintains the metadata for 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, Oracle best practices recommend that you keep a copy of the control file on at least three different physical disks. As with the redo log files, Oracle keeps all copies of the control file in sync automatically. We’ll show you how to move the control files to different disks in Chapter 40.

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. We cover data dictionary views and many other types of views in Chapter 34.

{mospagebreak title=Oracle Memory Structures}

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

  1. User reading and writing activity (adding, modifying, deleting)
  2. SQL and PL/SQL commands 
  3. Stored procedures and functions 
  4. Information about database objects 
  5. Transaction information 
  6. Oracle program executables

This information is stored in two major memory areas: the System Global Area (SGA) and the Program Global Area (PGA). These areas are shown in Figure 28-3. The Oracle program executables are stored in the Software Code Area (not shown in the diagram).

The overall memory allocated to Oracle falls into two broad 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 allocated for each user connected to the database.

Figure 28-3.  Oracle database memory structures

System Global Area

The SGA is the memory area that all connected database users share. The SGA itself is broken down into many areas. In the following sections we 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 the database buffer cache, the shared pool, and the redo log buffer, respectively.

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. We cover the ROLLBACK statement and transaction processing in Chapter 32.

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, tuning statistics, and security information. Because Oracle frequently caches objects such as PL/SQL stored functions in the shared pool, another user or process that needs the same stored functions can benefit from the performance improvement because the stored function is already in the shared pool.

Redo Log Buffer

The redo log buffer keeps the most recent information regarding changes to the database resulting from SQL statements. The Oracle background processes write these blocks initially to the online redo log files, which can be used to recover, or redo, all recent changes to the database after a failure, as we mentioned earlier.

Program Global Area

The 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.

Software Code Area

The Software Code Area is a shared area containing the Oracle program code (binary executables). Multiple database instances running against the same or different databases can share this code; as a result, it saves a significant amount of memory on the server. Now that you’ve heard about the Software Code Area, you will probably never hear about it again; it’s a static area of memory that only changes size when you install a new version of Oracle. It’s truly a “set it and forget it” situation. If you meet the overall Oracle memory requirements, the program code uses a relatively insignificant amount of memory compared to the SGA and the PGA.

Please check back next week for the continuation of this article.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye