Tutorial corner

Informatica,ETL,oracle,sql/plsql

System Global Area in Oracle Database

Spread the love

System Global Area in Oracle Database: In previous tutorial , we talked about the oracle database architecture in details. We also checked the importance of SGA (System Global Area in Oracle database) in the memory management part. In below tutorial , we will  go through the detailed explanation of System Global Area in Oracle Database.

System Global Area in Oracle Database

What is System Global Area (SGA) ?

The SGA is the primary memory component of the instance ,which contains the necessary memory structure for data manipulation , SQL statement parsing, and redo caching etc.
Oracle database automatically allocates SGA memory for an instance , once instance is started. Once instance is shut down , it is reclaimed by operating system. A special SGA is allocated for each instance running.

Check here for oracle database architecture in detail

Properties of SGA

  • The SGA can be allocated in memory and virtual memory depending upon the system.
  • Size of  SGA  can be set by DBA  by setting SGA_TARGET in the parameter file
  • Maximum size of SGA can be limit  by SGA_MAX_SIZE  in the parameter file

Component of SGA in Oracle

System Global Area in Oracle
System Global Area in Oracle

1 : Shared Pool

Shared pool is memory structure shared by all system processes. It cache the various type of program data like parsed SQL code , PL-SQL code , system parameters, and data dictionary information.

It can be classified as :

  • Library Cache
  • Data Dictionary Cache

1.1 : Library cache

  • Stores the text, parsed format, and execution plan of SQL statements that have been submitted to the RDBMS
  • It also store the headers of PL/SQL packages and procedures that have been executed
  • For each coming SQL statement to the server , it first checks the library cache to see if an identical statement has already been submitted and cached and then use it if  exists. Hence it enhance the performance of SQL.

The library cache can be further classified as below.

1.1.1  : Shared  Cache : This area contains the parse tree and execution path for SQL statement

1.1.2  : Private Cache : This area contains session specific information, such as bind variable, environment and session parameters, run time stacks and buffers, etc  A private SQL area is created for each transaction initiated, and it is de-allocated after the cursor corresponding to that private area is closed.

Many private SQL areas in separate PGAs can be associated with the same shared SQL area

Using these two structures, the server can reuse the information common across all execution of an SQL statement, while session specific information to the execution can be retrieved from the private SQL area.

 1.2 : Data Dictionary Cache

The Data Dictionary Cache is a memory structure that caches data dictionary information that has been recently used.

  • This cache is necessary because the data dictionary is accessed so often.
  • Information accessed includes user account information, data file names, table descriptions, user privileges, and other information.

The database server manages the size of the Data Dictionary Cache internally and the size depends on the size of the Shared Pool in which the Data Dictionary Cache resides.  If the size is too small, then the data dictionary tables that reside on disk must be queried often for information and this will slow down performance.

Check here for Process Global Area in Oracle

 1.3 : Server Result Cache:

The Server Result Cache holds the result sets and not data blocks. The server result cache contains the SQL query result cache and PL/SQL function result cache, which share the same infrastructure

1.3.1 : SQL Query Result Cache

This cache stores the results of queries and query fragments.

  • Using the cache results for future queries tends to improve performance.
  • For example, suppose an application runs the same SELECT statement repeatedly. If the results are cached, then the database returns them immediately.
  • In this way, the database avoids the expensive operation of rereading blocks and recomputing results.

1.3.2  : PL/SQL Function Result Cache

The PL/SQL Function Result Cache stores function result sets.

  • Without caching, 1000 calls of a function at 1 second per call would take 1000 seconds.
  • With caching, 1000 function calls with the same inputs could take 1 second total.
  • Good candidates for result caching are frequently invoked functions that depend on relatively static data.
  • PL/SQL function code can specify that results be cached.

2.  Buffer Cache:

  • The buffer cache is composed of memory blocks. All data updates are performed in the buffer blocks
  • The data movement (swapping and loading) between buffer and disk or other parts of RAM is by least recently Used (LRU) algorithm.
  • The LRU list keeps track of what data blocks are accessed and how often.
  • Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora  initialization parameter file

3. Redo Log Buffer:

  • Each DML statement (select, insert, update, and delete) executed by users will generates entry in the redo logs .
  • The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the data files, then the changes can be obtained from the redo log, so work is never lost.
  • To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file.

4. Large Pool:

  • Large pool is an optional area of memory in the SGA.
  • It is used to relieves the burden place on the shared pool. It is also used for I/O processes.
  • The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.

5.  Java Pool :

  • As its name, Java pool is used to services parsing of the Java commands.
  • Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

How to check SGA of an oracle instance

You will need to connected as SYSDBA to know about the SGA of oracle instance

SQL> connect / as sysdba
Connected.
SQL> show sga
Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 1006633528 bytes
Database Buffers 587202560 bytes
Redo Buffers 14692352 bytes

Parameters related to System Global Area in Oracle

Parameter Description
 SGA_TARGET  It is the total memory allocated for SGA . It cannot be greater than SGA_MAX_SIZE.
SGA_MAX_SIZE  Maximum size of memory which can be set for SGA for an instance. It’s value is set in init.ora file
DB_CACHE_SIZE It is used to set amount memory allocated to the Database Buffer Cache in standard database blocks.Block sizes may vary depending upon the operating systems
 LOG_BUFFER   Log_buffer is an optional parameter specifies the number of bytes allocated for the Redo Log Buffer.
SHARED_POOL_SIZE  It is optional parameter specifies the number of bytes of memory allocated to shared SQL and PL/SQL.
LARGE_POOL_SIZE  It define the number of block assign to Large pool
JAVA_POOL_SIZE This is another optional memory object.  The default is 24 MB of memory.

Sources :

https://docs.oracle.com/en/
www.orafaq.com

Hope you enjoyed the above tutorial , in which we have gone through the basics of System Global Area in oracle database architecture , basic component and parameter of SGA .

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme