Oracle Internals: An Introduction

Chapter 6. Memory

Many tuning issues involve making decisions about memory allocation. Those decisions are complicated by the fact that Oracle manages much of its memory dynamically. To tune Oracle effectively, you need to understand both what it uses memory for and how it manages that memory.

The SGA

The System Global Area (SGA), together with the essential background processes, is definitive of an Oracle instance. It is a global area in the sense that it contains global variables and data structures, and it is a system area in the sense that it contains data structures that must be accessible to the entire Oracle instance, rather than just a particular process.

The SGA Areas

The SGA contains four or five main areas:

• The fixed area

• The variable area

• The database block buffers

• The log buffer

• The instance lock database (for parallel server instances)

In terms of memory size, the fixed area and the log buffer should be trivial.

The fixed area

The fixed area of the SGA contains several thousand atomic variables, small data structures such as latches and pointers into other areas of the SGA. These variables are all listed in the fixed table X$KSMFSV along with their data types, sizes, and memory addresses, as shown in Example 6.1. The names of these SGA variables are cryptic, and seldom of use to know. However, senior Oracle staff can obtain advanced diagnostic information by joining X$KSMFSV with X$KSMMEM to monitor the values of these variables or to probe the data structures that they ...