Chapter 10
Oracle Memory Areas

In This Chapter

•  Uses of Memory Within Oracle8

•  System Global Area (SGA)

•  Program Global Area (PGA)

•  User Work Spaces

For Oracle, and most other multi-user database management systems, physical memory means speed. Data stored in physical memory can be accessed much faster than that stored on even the fastest magnetic or optical disk drives. This chapter presents a discussion on memory at the level of detail needed by Oracle database. While most developers do not need this level of detail, it can come in handy when developers are trying to get that extra ounce of performance out of their applications. This chapter focuses on the topics that have practical uses, such as the following:

•  In what memory areas does Oracle store various types of information?

•  What is the impact of inadequate space for the various memory areas?

•  How are the values of the storage parameters for the various memory areas set?

Uses of Memory Within Oracle8

A good starting place is the overview picture of the Oracle memory areas that was presented in Chapter 2, “Oracle Database Architecture.” Refer to Figure 2.3 in Chapter 2.

Before getting into the details of what each of the memory areas contain, it is useful to step back and consider what you need to put in memory to make a database management system work efficiently. This depends somewhat on the DBMS designer’s preferences and style, but generally speaking, a multi-user DBMS that wants to use memory to achieve speed would want to put the following into memory:

•  Rows of tables that have been added or changed. These are written to memory first so the user process can go on without waiting for the disk to receive the data.

•  Rows that are “close” to the row with which the user is currently working. In most applications, it is likely that the next row the user will want will be close to the current row. Because most operating systems read entire blocks (512 bytes or more) from disk drives, if you save these extra retrieved rows in memory, you can get lucky and have the next row needed waiting in higher-speed memory.

•  Space for the application programs that are running. Computers execute instructions stored in memory. If you want your general ledger application to work, you need to have memory space available for it. This same concept applies to storing code for the various Oracle background processes.

•  Administrative space to keep track of who is logged in, what needs to be done, and so forth.

•  Information being passed between Oracle database processes and end-user processes. This information enables the many operating system processes involved with applications using the Oracle database to coordinate their activities.

Virtual Memory

One of the considerations you will run across is the use of real and virtual memory. For those who are not used to these terms, real memory consists of the random access memory (RAM) chips of the computer. This is the memory that gives all the speed benefits I have been discussing in this chapter. Most multi-user computer systems, and even Microsoft Windows and the Macintosh operating system, provide what is known as virtual memory. Virtual memory is a section of a disk drive that is allocated to storing the overflow of the real memory area. This enables you to have more processes running than would be possible if you were limited to storing these processes in real memory. It works especially well when you have a number of processes that run infrequently, and therefore there is not much of a performance impact when they need to be swapped out to disk. The swapping out process transfers the program and data as they reside in memory to a special area on one or more of the disk drives, thereby freeing up real memory to be used by other processes. In Oracle’s case however, you really want all of Oracle’s memory areas to be stored in real memory, because speed is the whole purpose of this architecture.

System Global Area

Oracle divides its memory areas into several sections. The System Global Area (SGA) holds all the common database storage areas (transactions being buffered, data dictionary information, and so on). The Program Global Area (PGA) stores data related to your individual process. User spaces are related to the software being run by the Oracle background processes and user processes. Finally, sort areas are used for sorting data associated with a user’s query. This is a nice, clean division of function and, if you can understand these basic concepts, you are well on the way to understanding enough of how Oracle works to be an effective DBA.