Oracle Instance  «Prev  Next»
Lesson 2 Instance components
Objective Describe the main components of the Oracle SGA.

Describe Main Components of Oracle SGA

The System Global Area, or SGA, is a shared memory structure that is the focal point of activity for an Oracle instance. In fact, the term SGA is often used to describe a running Oracle instance.
The SGA contains both data and control information for an instance, and is referenced in one way or another by virtually all of the background processes.

Configuring the SGA

When Oracle is started, a region of memory is configured according to initial parameters that are defined in two files: two files .
  1. init.ora and
  2. config.ora

These parameter files define the memory region and start-up characteristics for the Oracle instance. These files tell the database software how to configure SGA. Because the SGA resides within an operating system, it is dependent upon the operating environment. In UNIX, Oracle must share memory space with many other memory regions, competing for the limited memory and processing resources. The Oracle instance occupies an area of memory (the SGA), while external Oracle applications, also in a memory space, communicate with Oracle to service their data needs.

More about Initial Parameters

The init.ora file may be made to contain all of the necessary information to start an Oracle instance, but the config.ora file is sometimes used to segragate the configuration information into a separate file.
The init.ora file (and spfile) determines many Oracle operating system environment attributes, such as
  1. memory allocated for data,
  2. memory allocated for statements,
  3. resources allocated for I/O, and
  4. other crucial performance-related parameters.
Each version of Oracle continues to add to the total number of initialization parameters. In Oracle 10g Release 2 there are now 1381 (257 documented and 1124 hidden) different initialization parameters (these numbers vary slightly on different versions of Oracle and platforms). As you might expect, an entire book could be written on how to set and tune each parameter; this book focuses on the key parameters that affect database performance. The key to an optimized Oracle database is often the architecture of the system and the parameters that set the environment for the database

Parallel Statistics Gathering

The statistics-gathering operations can run either serially or in parallel. You can specify the degree of parallelism with the DEGREE argument to the DBMS_STATS gathering procedures. The database can use parallel statistics gathering in conjunction with sampling. Oracle recommends setting the DEGREE parameter to DBMS_STATS.AUTO_DEGREE. This setting allows Oracle Database to choose an appropriate degree of parallelism based on the size of the object and the settings for the parallel-related init.ora parameters. Note that certain types of index statistics are not gathered in parallel, including cluster indexes, domain indexes, and bitmap join indexes.

Statistics on Partitioned Objects

For partitioned tables and indexes, DBMS_STATS can gather separate statistics for each partition and global statistics for the entire table or index. Similarly, for composite partitioning, DBMS_STATS can gather separate statistics for subpartitions, partitions, and the entire table or index.

The following diagram reviews the SGA components:
  1. The database buffer cache
  2. The shared pool (comprising session memory, the data dictionary cache, and the library cache)
  3. The redo log buffer


Components of the SGA

SGA shared Pool consisting of 1) Session Memory 2) Library Cache 3) Dictionary Cache
SGA shared Pool consisting of 1) Session Memory 2) Library Cache 3) Dictionary Cache

  1. This is the memory allocated by db_block_buffer to hold the incoming Oracle data blocks.
  2. This is the area of memory reserved for the redo logs before they move into the online redo log files.
  3. This area of memory resides within the shared pool and holds session information such as cursors and host variables.
  4. This area of memory resides within the shared pool and holds shared SQL, PL/SQL procedures, locks and latches.
  5. This memory area resides within the shared pool and holds data dictionary objects.

SGA_TARGET Initialization Parameter

In Oracle's memory architecture, the SGA_TARGET initialization parameter plays a crucial role in managing the System Global Area (SGA), which is the central storage area for data and control information shared by all Oracle server processes. The SGA_TARGET parameter represents the total size (in bytes) of all components in the SGA, and it is a dynamic parameter that you can alter without having to restart the database. This parameter offers the following significant benefits:
  1. Simplifies SGA Memory Management: By setting the SGA_TARGET parameter, you enable Oracle's Automatic Shared Memory Management (ASMM). ASMM simplifies the configuration and resource allocation process by automatically distributing memory among various SGA components (Database Buffer Cache, Shared Pool, Large Pool, Java Pool, etc.) based on workload characteristics.
  2. Provides Flexibility: It allows for dynamic resizing of memory components. As workload changes, Oracle reallocates memory to accommodate the varying needs of different components without manual intervention.
  3. Improves Efficiency: Through ASMM, Oracle can manage memory more efficiently than manual tuning. This leads to improved database performance as Oracle can adjust SGA memory allocations in response to changes in application workload.
  4. Minimizes Overhead: The SGA_TARGET parameter removes the need for DBAs to manually balance memory between different SGA components, reducing the administration overhead.
  5. Optimizes Resource Usage: By letting Oracle manage SGA memory, you can ensure that your system resources are used optimally. Oracle's ASMM will adjust the sizes of the SGA components to ensure that none are starved of memory or have too much memory allocated to them.

To use ASMM, you should set the SGA_TARGET to a value that represents the amount of memory that you estimate will be adequate for all the SGA components. While Oracle recommends setting this parameter, you also have the option of manually setting the sizes of individual SGA components if necessary.

SGA_TARGET

Specifies the total size of all SGA components. If SGA_TARGET is specified, then the buffer cache (DB_CACHE_SIZE), Java pool (JAVA_POOL_SIZE), large pool (LARGE_POOL_SIZE), and shared pool (SHARED_POOL_SIZE) memory pools are automatically sized.

Putting SGA tuning in Context

While tuning the Oracle SGA is quite important, there are many other factors that have an ever bigger impact on Oracle performance such as proper database design, SQL tuning, and disk I/O tuning, and tuning the operating environment.

Database design issues No amount of instance tuning can fix a poor table design or alleviate poor SQL response time.
Disk I/O tuning If Oracle cannot quickly retrieve blocks from the disk, instance tuning cannot help.
OS issues If your processor is short on memory or CPU, no amount of instance tuning will be effective.

SGA Performance Information

When you suspect an instance tuning problem, the best source of information is always the V$ views.
This is because virtually all instance performance information is held in these views.
In the next lesson, you will start by learning some basic tuning considerations of the shared pool.