RelationalDBDesign RelationalDBDesign


Memory Processes   «Prev  Next»
Lesson 3Oracle memory structures
Objective Review of Oracle instance memory structures.

Instance Memory Structures

The Oracle instance contains several memory structures located in the System Global Area, or SGA. Memory structures are contained in the SGA and are shared by user and system processes. The SGA contains both data and control information for an instance, and is referenced by virtually all of the background processes. The benefit of this shared memory model is that information is shared efficiently between the various system processes. The major components of the SGA are shown in the MouseOver below.

store IO buffers
  1. Large Pool: An optional memory area used to store IO buffers for use by Recovery Manager.
  2. Database buffer cache: Holds data blocks that have been read from datafiles.
  3. Redo log buffer: Holds redo log records before they are written to the log files.
  4. Shared pool: Contains parsed versions of SQL statements and other information.

One of the more important changes to the Oracle instance memory structures is the addition of the Large Pool.

The Large Pool

Introduced in Oracle8, the Large Pool helps buffer I/O. It will also store session information if yhou are using Multi-Threaded and when using XA protocol. Both Archive and Recovery Manager benefit greatly from the use of the Large Pool. As with most good features of Oracle, you must tell the instance that you want to have a Large Pool by setting the LARGE_POOL_SIZE parameter in your init.ora file. If you do not use the Large Pool, Oracle will allocate shared memory buffers from the Shared Pool. Below are the INIT.ORA parameters related to the Large Pool.

Oracle data dictionary

A database is a collection of files stored on disk. The primary component of the database is a tablespace which is a logical unit made up of one or more physical files on disk or disks. See the Oracle8 Server Administrator’s Guide for a complete description of managing tablespaces and data files. You can find an online version of this documentation in the Data Servers/Oracle8 category of the Oracle Technology Network website.

There is one tablespace that deserves mention here, the SYSTEM tablespace. This tablespace must be available all the time for the normal operation of your database. This tablespace contains several unique components, one of which is the data dictionary. The data dictionary contains information about the database and is relatively consistent in size, never really growing compared to the potential growth of user data. A DBA will limit access to the SYSTEM tablespace and will check to make sure that it normally contains 50% free space.
Most DBAs have one of the large posters identifying the Data Dictionary for Oracle8 or the V$ views of Oracle8. During this course we will refer to various tables and views for the information specific to our installation. For instance, there is a V$SGA view which will provide the size of the shared pool, log buffer, data buffer cache, and fixed memory sizes. The V$INSTANCE view will return information about your instance such as name, startup time, and host name. Here is an example from a default Oracle 8.0.5 database on Windows NT.
instance view
instance view

The next lesson explores Oracle background processes.

Oracle Memory Structures - Exercise

Click the Exercise link below to complete a brief matching exercise on instance memory structures.
Oracle Memory Structures - Exercise