The user applications interact with memory areas. These memory areas store data retrieved from disk and data to be written to disk, which is the permanent storage medium. Background processes provide data retrieval and storage functions, linking the memory areas to the disks. There are background processes to perform other system monitoring and maintenance functions. However, if you understand the basic concepts in this paragraph, you’re where you should be for now.

Oracle Processes

Now it is time to delve a little more deeply into the major components. The Oracle processes can be divided into four categories:

•  Servicing user requests

•  Writing data to the data files

•  Recording transactions in log files

•  Monitoring the functioning of the database

User Processes

The first group of processes are those involved with servicing user requests. Think of them as your liaisons to Oracle. When you need for information, they go after it for you. When you have updates or inserts for the database, they store the transaction in the shared memory areas for later transfer to the data files. There are different types of user processes, but for now it is enough to understand that they are there and that their purpose is serving as your interface to the Oracle database.

Data Writing Processes

The next group of processes write data to the data files. Recall that when you write to the Oracle RDBMS, the data is stored first in a shared memory area. Oracle has one or more database writer processes that take the data from this shared memory area and write it to the data files efficiently. This creates free space in the memory area that can be used for other transactions and queries. There is also an optional checkpoint process that updates all data with a record of the latest transaction that has been applied to the database (using the internal Oracle transaction number). If the checkpoint process is not started, the database writer process takes over this function.

Logging Processes

The third group of processes records transactions in log files. One of the major differences in a commercial-grade, multiuser database is that the capability of recovering from a failure such as the loss of a disk drive is very important. Oracle accomplishes this, in part, by recording each transaction (that is, inserting a record in a particular table) in a separate file from the data files. In the event of the loss of one of the data files, these log files can be applied to a copy of the data file retrieved from a backup to bring the data file up to the point where the failure occurred. More on this topic later, but it is important to understand the concept of these log files and that there are one or more processes devoted to taking transactions stored in the shared memory areas and moving them to these log files.

Monitoring Processes

The final group of processes under this simple classification scheme is those that monitor the functioning of the database. You may already be worried about the complexity of the database as it has been described so far. Perhaps you looked at the next couple of chapters where the topics introduced in this section are discussed in more detail. Fortunately, you do not have to keep track of what is happening and manage the details of the database by yourself. Oracle has implemented a number of processes whose job it is to keep an eye on the database and correct problems when they occur.

Memory and Speed

As mentioned earlier, shared memory is the key to speed in the Oracle RDBMS. It is also one of the most elusive areas to keep track of and therefore it is important to understand. It is elusive because you can run operating system utilities to see which processes are running and you can look at the data files to see when they were updated, what their size is, and so forth. With memory, there is very little in the way of utilities that enable you to look at all of the shared memory components to see whether they are working well. Memory areas are discussed in more detail in Chapter 10, however I wanted to present an introduction here to show how the memory areas fit into the overall Oracle architecture. The following are the various components or areas of memory used by Oracle (see Figure 2.2):

•  Software code areas

•  System Global Area (SGA)

•  Program Global Area (PGA)

•  Sort areas

Figure 2.2.  A simplified view of the Oracle memory areas.

Software Code Areas

The first memory area of interest is the one that stores software code for the Oracle database itself. On some operating systems, this software area can be shared between instances. Note that this is the software that runs the database itself, not the applications that you have written and are running.

System Global Area (SGA)

The System Global Area or SGA is the next memory area of interest. Actually, I like to think of this as the heart of an Oracle database. Your processes send transactions to this memory area and try to get data that has been cached here for speed. There are four components stored in the SGA:

•  The database buffer cache contains database records that are waiting to be written to disk or are available for reading.

•  The redo log buffer stores a copy of transactions that have been applied to the database and are waiting to be written to the redo logs.

•  Shared SQL areas store queries that have been parsed and are ready for execution. In many databases, users execute the same query many times and performance is enhanced when these queries are waiting to be executed in memory. Some of this information is actually stored in the PGA (described in the next section) in Oracle version 6.

•  The data dictionary cache stores data about the database itself, its objects (tables, views, and so forth), and users.

Program Global Area (PGA)

In contrast to the SGA, which contains information shared by everyone in the instance, the Program Global Area or PGA stores information that is used by a single user process. When someone creates an Oracle session (Oracle’s way of saying that the user connects to a database instance), Oracle creates the PGA. The PGA contains stack space, and in many cases, information about the user session.