Identify purpose and major components of System Global Area.
Function of the System Global Area (SGA)
The System Global Area, or SGA, is a shared memory structure that is the focal point of activity for an 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. Move your mouse over the image below to see the major components of the SGA.
System Global Area (SGA)
Designing Database Applications
The primary goal of designing database applications is to return the correct results as quickly as possible. Application developers spend countless hours trying to write efficient SQL code, and Oracle DBAs tune the database memory structures to provide efficient access to the data and index blocks needed to satisfy the queries. Tuning an application normally involves finding the best path to touch the least amount of data and/or index blocks in the Oracle database buffer cache. Accessing data in memory is always much faster than accessing data from disk. Oracle uses algorithms to keep highly used data blocks in memory to reduce the number of physical disk reads. Oracle DBAs can increase the size of the default buffer cache or make use of the KEEP buffer pool to increase the number of blocks resident in memory for highly used tables and indexes. Even if all the data and index blocks are in memory, the query still has to read them to obtain the result set.
SQL Result Cache
Oracle has introduced a feature called the SQL Result Cache with the release of 11g. It can be used to cache a read-consistent version of the query results of commonly executed queries. Static queries that are run time and no longer need to read the underlying data and index blocks but instead can read the results of queries from the new SQL Result Cache memory area within the Shared Pool of SGA.
A SQL statement is run once to read all the necessary index and data blocks that produce a query result, which can optionally be stored in the SQL Result Cache by means of a hint in the statement or by setting a database initialization parameter.
The SQL Result Cache is stored within the SGA so that all users connecting to the database instance have access to the query results stored there. Anyone executing the same SQL statement including the RESULT_CACHE hint will be able to make use of the SQL Result Cache and will not have to perform the physical and logical I/O to generate the output of the query.
Benefits of the SGA
Oracle uses this shared memory structure because it is a much more efficient way to share data between processes than other mechanisms.
The size of the SGA can be significant, easily reaching several hundred megabytes for a large database.
The SGA has a significant impact on database performance, and it's important to understand the various components so that you can size them properly. The next module covers the SGA in detail.
The (SGA) System Global Area is a group of shared memory areas that are dedicated to an Oracle instance, which is an instance is your database programs and RAM. All Oracle processes use the SGA to hold information and the SGA is used to store incoming data.
the data buffers as defined by the db_cache_size parameter), and
internal control information that is needed by the database.
You control the amount of memory to be allocated to the SGA by setting the Oracle initialization parameters.
These may include
db_cache_size,
shared_pool_size and
log_buffer.
In Oracle Database 10g you only need to define two parameters, namely
sga_target and
sga_max_size
to configure your SGA. If these parameters are configured, Oracle will calculate how much memory to allocate to the different areas of the SGA using a feature called (AMM) Automatic Memory Management. As you progress you may want to manually allocate memory to each individual area of the SGA using the initialization parameters.