Oracle Instance Memory Structure

We discussed about Oracle Instance in the previous lesson and got to know that an Instance is composed of various Background processes and Memory Buffers. In this lesson, we would see what different types of Memory Buffers are allocated when an instance is started.

Oracle Instance Memory Structure

The System Global Area (SGA)

The SGA is a shared memory area that contains data and control information for the instance. Multiple users can share data within this memory area and avoid repeated, time-consuming access from physical disk. For optimal performance, the SGA should be large enough to avoid frequent disk reads and writes.

The SGA has several subcomponents as listed:

Buffer Cache

Before any data stored in the database can be queried or modified, it must be read from disk and stored in memory in the buffer cache. All user processes connected to the database share access to the buffer cache.

Shared Pool

The shared pool caches the following information that can be shared among users:

- SQL statements that can be reused

- Information from the data dictionary such as user account data, table and index descriptions, and privileges

- Stored procedures, which is executable code that is stored in the database

Redo Log Buffer

Any changes made to the data are written to the physical redo log files stored on disk from the Redo Log Buffer. This buffer improves performance by caching redo information which is used for instance recovery until it had been written to the physical redo log files.

Large Pool

This optional area is used to buffer large I/O requests for various server processes.

Java Pool

The Java pool memory is used for all session-specific Java code and data within the Java Virtual Machine (JVM).

Streams Pool

The Streams pool is used by the Oracle Streams product.

Program Global Area (PGA)

A program global area (PGA) is a memory area used by a single Oracle server process. A server process is a process that services a client’s requests. Each server process has its own nonshared PGA when the process is started. The PGA is used to process SQL statements and to hold logon and other session information.

Note:

In addition to background processes, Oracle creates Server processes that handle the connection requests of User or Client processes. A user connection is composed of two distinct pieces:

- A client program acting on behalf of the user, such as Oracle Enterprise Manager, SQL*Plus, or an application.

- A server process that handles the connection to the database on behalf of the client program.

The size of the instance memory structures affects the performance of the Oracle database server and is controlled by initialization parameters. Let's see the value of various Initialization parameters related to memory buffers:

SQL>show parameter sga

SQL>show parameter db_cache_size

SQL>show parameter shared_pool_size

SQL>show parameter log_buffer

SQL>show parameter large_pool_size

SQL>show parameter java_pool_size

SQL>show parameter streams_pool_size

SQL>show parameter pga_aggregate_target

In this lesson, we discussed about different memory buffers of Oracle Instance. In next session, we would see how to modify various Initialization parameters using spfile or init file. We would also discuss the various startup and shutdown options.

 

 

Have a question or comment? Post it below!


Previous Tutorial