Sharing Memory

An important concept to understand as a DBA is the control of how memory regions are set up for sharing. Most memory areas (such as the one allocated to your shell when you log on to UNIX or the one for Microsoft Windows) are not sharable. You have sole access to that memory and that is the way it should be. Shared memory is the exception to the rule. It usually takes some special permissions from the system administrator to share a memory area. The exact method for setting this up varies from operating system to operating system. The RDBMS software provided by Oracle is normally set up so that when it creates a new database instance, it has the permissions (typically through the user ID that owns it) necessary to set up the memory areas as sharable without you having to take any overt actions.

---

Note:  
What if you get a message indicating the SGA is not available? You may have lost your operating system permission to create shared memory areas if the following are true:

•  You see the Oracle instance running properly.

•  Only the user who started the Oracle instance (oracle) can access Oracle successfully.

Check with the system administrator regarding shared memory permissions.

---

You may be curious about how you can control all these wonderful memory areas to set them up properly for your particular application environment. The answer lies in the combination of initialization files (discussed in detail in Chapter 12, “Oracle Database Objects”) and the default parameters that Oracle has established for its products on your operating system. Let’s start with the default settings. Oracle takes into consideration the average configuration of a particular type of computer and comes up with a set of default values for those parameters that determine the size of the SGA, PGA, and sort areas. Obviously, the configuration for the Microsoft Windows version of Oracle is set up to be much smaller than that of a large VAX computer. The settings of these parameters can be overridden by values entered in the initialization files. Chapter 12 presents how to make entries in the initialization file to change the values of these parameters. Chapter 25, “Oracle8 Application Tuning,” shows you how to analyze your instances to determine how to adjust (or tune) these parameters. However, the basic concept is simple. If you use the initial settings for database block buffers (200) and you get a lot of contention for these items (as determined by your tuning run), you would edit the following line to double the value of the buffer:

db_block_buffers =  400

System Global Area (SGA)

The SGA can be thought of as the heart of Oracle itself (see Figure 10.1). It holds changes that you make to your database until a process is ready to write the data to disk. It stores things that can help speed up your access to data. Without the SGA, you do not have an Oracle database. Anything that is this important deserves some attention from the DBA. In this section, you explore what the SGA does. Although the SGA usually works just as well whether you understand it or not, this knowledge can come in handy some day when a performance problem arises and the cause is not obvious.

Figure 10.1.  The System Global Area (SGA).

As with most of the topics in this book, it is easiest to break the SGA into its components and discuss each of them individually. The only time you have to be concerned with how the pieces fit together is when you calculate the total amount of memory space required for the SGA. Sizing is discussed later in this section. For now, the pieces of the SGA are the following:

•  Database (block) buffer cache

•  Redo log buffer

•  Shared pool

•  Interprocess communications area

•  Queues for the multi-threaded server (when used)

Database Buffer Cache

The database buffer cache stores records from various tables within the Oracle database (actually, because reads and writes are in blocks, it stores the entire database block that contains the records you are working with). These blocks contain either rows that have been read from the data files or records that need to be written to the data files. In effect, the database buffer cache is a way-station for data that sits between users and the data files. As with most hotels, there is a limited capacity for accommodating these guests. Therefore, it is important for the DBA to understand how Oracle determines how long an individual block gets to stay.

The easiest decision involved in determining how long a block of records needs to stay in cache involves new or modified records that are waiting to be written to disk. They stay in the database buffer cache until they get written by the database writer process. It would not work if Oracle got to throw out data blocks when it felt too full. For the remaining buffers, which have been read in from disk and not modified, Oracle has to decide which ones to keep and which ones to get rid of. Many systems use a “first-in, first-out” method. This may seem fair, but it is not efficient for most database circumstances. Many records are accessed frequently (a common look-up table, for example), so it makes more sense to keep those in memory and drop those that are not used. The algorithm that implements this concept is called least recently used (LRU for those of you who are fond of acronyms).

When a record is read from database by an Oracle query, the block containing its data is stored in the database buffer cache and is placed at the most recently used end of the list of records. Every time a record in the database buffer cache is used, it gets promoted to the most recently used end of the list. When a query needs space to store data that has to be pulled off the disk drives and all of the buffers in the database cache are full, it overwrites buffers that are at the least recently used end (top) of the list that are not dirty (have been changed since being recalled from disk). If records that are overwritten are needed again, they are called in from disk.