Oracle Database Tips by Donald Burleson

Changes to Memory Structures  

The major memory structures associated with an Oracle instance are System Global Area (SGA) and Program Global Area (PGA). SGA is shared by all server and background processes, while PGA is exclusive to the server and background processes.

System Global Area (SGA)

System Global Area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. When multiple users are connected to the same instance, the data in the SGA is shared by all users. This is why it is called the Shared Global Area.

An Oracle instance is made of the SGA and Oracle processes. Oracle allocates memory for the SGA when the database instance is started and returns the memory when the instance is shut down. The maximum size of the SGA is determined by sga_max_sizeinitialization parameter in the initInstanceName.orafile or server parameter (SPFILE) file.

The SGA contains the following data structures:

Database Buffer Cache- The Database Buffer Cache is the portion of the SGA that holds copies of data blocks read from data files. All concurrent user processes share access to the database buffer cache. The size of the database buffer cache is set by the initialization parameter file db_block_size. Usual values are from 2K to 32K. This standard block size is used by the SYSTEM tablespace.

Redo Log Buffer - Redo Log Buffer is a circular buffer in the SGA that holds information relating to changes made in the database in the form of redo entries. The background process, LGWR , writes the redo log buffer to the active redo log file or group on disk.

Shared Pool - Shared Pool contains the library cache, the dictionary cache, buffers for parallel execution and control structures. It is sized by shared_pool_sizeparameter.

Java Pool  - Java Pool is used in memory for all session-specific java code and data within the JVM (Java Virtual Machine). The Java pool advisor provides information on how the size of the Java pool can affect the parse rate.

Large Pool (Optional) - Large Pool is a large optional memory area used to provide memory allocations for memory requests larger than the size of shared pool.

Data Dictionary Cache- Data Dictionary Cache, also known as row cache is a special location in memory to hold data dictionary data. The Data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users.

Streams pool - Streams pool controls the Streams memory.

Database Buffer Cache - Oracle supports multiple block sizes in a database. The sizes and numbers of non-standard block size buffers are specified by the following parameters.

§   db_2k_cache_size

§   db_4k_cache_size

§   db_8k_cache_size

§   db_16k_cache_size , and

§   db_32k_cache_size.

If the cache is large, a request for data is more likely to find the information, resulting in a cache hit.

Other information - The SGAcontains general information about the database and the instance, which is accessed by the background processes. This part is called fixed SGA.

All SGA components allocate and deallocate memory in units called granules. The granule size is determined by the total SGA size and the operating system. It is 4 MB if the total SGA is less than 1 GB and 16MB if the SGA is over 1 GB. For 32bit Windows, the granule size is 8MB for a SGA larger than 1 GB.

The size of the SGA is determined by several initialization parameters, of which the following are of higher relevance.

§        db_cache_size- size of the cache of standard blocks

§        log_buffer- number of bytes allocated for the redo log buffer

§        shared_pool_size- size in bytes of the area for shared SQL and PL/SQL

§        large_pool_size- size of the large pool (default = 0)

§        java_pool_size- size of the Java Pool.

§        db_nk_cache_size- size of non-default block size cache

In previous database versions, the DBA had to manually specify different SGA component sizes by setting the above parameters. Oracle Database 10g has the Automatic Shared Memory Management(ASMM) feature to simplify this memory management process. ASMM methods will be explored in a later chapter.

Program Global Area (PGA) is a memory area that contains data and control information for a server process. Access to the PGA is exclusive to server processes and software code acting on its behalf.

The contents of PGA memory vary depending on how the instance is running, whether the shared server or dedicated server option is in effect. But generally, the PGA memory can be classified into the following areas: Private SQL Area, Cursors and SQL Areas, and Session Memory

Private SQL Area- A Private SQL Area has data on bind information and runtime memory structures. Every session or user that issues an SQL statement has a private SQL area. Many private SQL areas can be associated with the same shared SQL area. The private SQL area of a cursor is divided into the persistent area, which is freed only when the cursor is closed, and the run-time area, which is freed when the execution is terminated.

The actual location of a private SQL area depends on the session's connection. For a session connected through a dedicated server, private SQL areas are located in the server process's PGA. If a session is connected through a shared server, part of the private SQL area is kept in the SGA.

Cursors - A cursor is a handle or name for a private SQL area, which is used as a named resource throughout the execution of the program. The number of private SQL areas that a user process can allocate is limited by open_cursorsparameter. The default value is 50.

Session memory - Session memory is the memory allocated to hold a session's variables and other information related to the session. For a shared server, the session memory is shared and not private.

This section has reviewed the Oracle memory structures, namely the System Global Area (SGA) and the Program Global Area (PGA) and introduced some of the new features that affect them in Oracle 10g. The next section will introduce some of the new features in Oracle 10g.

This is an excerpt from the book Easy Oracle Automation.  You can get it for more than 30% by buying it directly from the publisher and get instant access to scripts from the code depot: