SGA (System Global Area)

Hey Folks,

This post is the continuation of my previous post " Oracle database architecture".  In This post We will know few facts about System Global Area (SGA).

SGA (SYSTEM GLOBAL AREA):

System Global Area is also known as Shared Global Area.

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Therefore, the SGA is sometimes called the shared global area.

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

The SGA consist of several memory structure :

• Shared pool
• Database buffer cache
• Redo Log buffer
• Other Structures (For example, Lock and latch management , Statistical data) 

There are three additional memory structures that can configured within the SGA.

• Large Pool  (Optional)
• Java Pool
• Stream Pool

The SGA is also called the shared global area. It is used  to store database information that is shared by database processes. it contains data and control information for the oracle server and is allocated in the virtual memory of the computer where oracle resides.

The fallowing statement can be used to view SGA memory allocation. We use below this command to show SGA configuration of the  oracle instances.

SQL> show sga

Total System Global Area 1.0689E+10 bytes

Fixed Size                  2235904 bytes

Variable Size            4664066560 bytes

Database Buffers         6006243328 bytes

Redo Buffers               16928768 bytes

Dynamic SGA :

Beginning with Oracle 9i, A Dynamic SGA  implements an infrastructure  that allows the SGA configuration to change without shutting down the instance. This then allows the size of the Database Buffer Cache, Shared Pool, and Large Pool to be changed without shutting down the instance. 

So the Database Buffer Cache  and Shared Pool could be initially under configured and would grow and shrink depending upon their respective work loads, up to a maximum if  SGA_MAX_SIZE.

The SGA_MAX_SIZE Initialization Parameter:

The SGA includes a number of memory components, which are pools of memory used to ensure and fulfill the memory allocation requests. Examples of memory components include the shared pool (used to allocate memory for SQL and PL/SQL execution), the java pool (used for java objects and other java execution memory), and the buffer cache (used for caching disk blocks). All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB, and granule size is 16MB for larger SGAs. Some platform dependencies arise. For example, on 32-bit Windows, the granule size is 8 M for SGAs larger than 1 GB.

Oracle Database can set limits on how much virtual memory the database uses for the SGA. It can start instances with minimal memory and allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter. If the value for SGA_MAX_SIZE in the initialization parameter file or server parameter file (SPFILE) is less than the sum the memory allocated for all components, either explicitly in the parameter file or by default, at the time the instance is initialized, then the database ignores the setting for SGA_MAX_SIZE.

For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system. The amount of memory dedicated to all shared areas in the SGA also has performance impact.

The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:

Sizing the SGA:

The Size of the SGA is determined by several initialization parameters. The Parameters that most affect SGA size are :

• DB_CACHE_SIZE :  The size of the cache of standard blocks. The default is 48 MB on UNIX and 52 MB on NT (Windows).

• LOG_BUFFER :  The number of bytes allocated for the Redo Log Buffers that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file. 

       The log buffer size depends on the number of redo strands in the system. One redo strand is allocated for every 16 CPUs and has a default size of 2 MB. Oracle allocates a minimum of 2 redo strands per instance. When the log buffer size is not specified, any remaining memory in  the redo granules is given to the log buffer.

• SHARED_POOL_SIZE :  SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool The shared pool contains shared cursors, stored procedures, control structures, and other structures. 

If you set PARALLEL_AUTOMATIC_TUNING to false, then Oracle also allocates parallel execution  message buffers from the shared pool. Larger values improve performance in multi user systems. Smaller values use less memory. 

• LARGE_POOL_SIZE : The large pool size allocation is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers.                      

          Parallel execution allocates buffers out of the large pool only when SGA_TARGET is set.   

   The Default value is Zero (Unless the the init.ora parameter     PARALLEL_AUTOMATIC_TUNING is set to TRUE, then       the default is Automatically  Calculated.

• JAVA_POOL_SIZE : JAVA_POOL_SIZE specifies (in bytes) the size of the Java pool, from which the Java memory manager allocates most Java state during run-time execution.

• STREAM_POOL_SIZE :  Oracle's Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value.

If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value, and the SGA_TARGET parameter is set to 0 (zero), then the Streams pool size is the value specified by the  STREAMS_POOL_SIZE parameter.

If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to  10% of the shared pool from the buffer cache to the Streams pool.

Therefore, the size of the SGA can not be exceed SGA_MAX_SIZE minus the value for DATABASE_BUFFER_CACHE,LOG_BUFFER,SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE and STREAM_POOL_SIZE

Hey Folks, If this post is informative then please fallow, like and Subscribe.

Thank You !!.  

Oracle database Architecture

Hey Folks,

Here we will learn about oracle database architecture and its Function.

Oracle Server: An Oracle server is a Database management system that provides an OPEN, comprehensive, integrated approach to information management.

In General, an oracle server must reliably manage a large amount of data in a multi-user environment so that many users can concurrently access the same Data.

There are several files, Processes, and memory structure in an Oracle Server, However, not all of them are used when processing a SQL statement. Some are used to improve the performance of the database, to ensure that the database can be recovered in the event of a software or hardware error, or to perform other tasks necessary to maintain the Database.

An Oracle server must also prevent unauthorized access and provide efficient solutions for failure recovery.

Oracle Server Consists of an  Oracle Instance and Oracle Database. 

Oracle Instance:  An Oracle Instance is the combination of the background Process and the Memory structure.  The Instance must be started to access the data in the database.

Every Time an Instance is started, a System Global Area (SGA) is allocated and Oracle Background Process is started. Background Process performs functions on behalf of the invoking process. The Background Process Perform input/output (I/O) and monitor other Oracle Processes to provide increased parallelism for better performance and Reliability.

Oracle Database: An Oracle Database consist of Operating System Files, Also Known As Database Files, that provide the actual physical storage for database information.  The database files are used to ensure that the data is kept consistent and can be recovered in the event of a Failure of the instance.

An Oracle Database has a Logical and a Physical Structure. The physical structure of the database for the database is the set of Operation System files in the database.

An Oracle Database :

• Is a collection of data that is treated as a Unit.
• Consist if Three File Types. 

1. Data Files: Data Files Containing the actual data in the database.
2. Online Redo Log files: Online Redo Log Files containing a record of changes made to the database to enable recovery of data in case failures.
3. Control Files: Control Files containing Information necessary to Maintain and Verify database  

Other Key File Structure :

The Oracle server also uses other files that are not part of the Database :

• The Parameter File: The Parameter File defines the characteristics of an Oracle Instance. For Example, It contains parameters that size, Some of the memory structure in the SGA.
• The Password File: The password file authenticates users privileged to startup and shut down an Oracle Instance.
• Archive redo log files: Archive redo log files are offline copies of the online redo log files that may be recover from Media Failure.

        ORACLE DATABASE ARCHITECTURE DIAGRAM 

Now here we see standard oracle database architecture via Diagram. Further, we will see the one by one all the Oracle architecture components in Details in the coming few subsequent posts.

Here we see the only overview:

Virtual Structure OR memory Structure: Virtual Structure is volatile in nature as when your database instance is up, memory structure will exist and when your database instance is down, memory structure does not exist.

Oracle's memory structure consists of two memory is known as,

• System Global Area (SGA): Allocated at instance startup, and it is the fundamental component of an oracle instance.
• Program Global Area (PGA): Allocated when the server process is started.

SGA consists of many components OR divided in-memory structures components and as we can see in the above Architecture diagram. Now Let see here the little description of all memory components. I will share descriptive details for all each and every component in a few next posts.

Shared Pool: The Shared pool is an area that is Exists on RAM heap of the system when the Oracle Instance starts Up. This is one of the most important storage structure in an Oracle Database system. The Oracle shared pool contains  Oracle library cache, Data dictionary cache and In 11g Onward It contains the Result Cache also. 

Database Buffer cache:  The oracle database buffer cache is also known as buffer cache. The database buffer cache is typically the largest area if SGA holds the copy of data blocks read from the data files. The data block copies are stored in the buffer and these buffers are equals in the data block size. The database buffer cache is shared among all the sessions that are connected to the instance. 

The goal is to keep frequently used data blocks in the buffer cache in order to improve I/O by having fewer physical reads or writes. When a dirty buffer is not used anymore, it is written to the disk by the background process database writer (DBWR).

This area of the SGA contains only the buffers themselves and not their control structures. For each buffer, there is a corresponding buffer header in the variable area of the SGA.

Redo Log Buffer: The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or Redo, the changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations.

Redo entries are primarily used for database recovery as necessary.

Large Pool: The large pool is an optional memory area in the SGA. It relieves the burden placed on the shared pool by allocating session memory from the large pool for a shared server or parallel query buffers. Oracle can use the shared pool primarily for caching shared SQL and avoid the performance caused by shrinking the shared SQL cache in Shared Pool. 

Large pool provides large memory allocation for :

• Session memory for the shared server and It is also used where transaction interact with more than one database
• I/O server process
• Oracle Backup and restore operations
• Parallel execution message buffer if the initialization parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE. (Otherwise, these buffers are allocated to the shared pool)

In addition, the memory for Oracle backup and restore operations, for I/O server processes, and for parallel buffers are allocated in buffers of a few hundred kilobytes. The large pool is better able to satisfy such large memory requests than the shared pool.

JAVA Pool: The java pool is a Memory region within the Oracle SGA and the java pool Memory is used to provide:

• Parsing if the JAVA code and Scripts
• Installation Tasks related to JAVA applications with Oracle

The JAVA pool is an optional Setting but it is required if you are installing and using JAVA. its size is set in bytes, using the JAVA_POOL_SIZE parameter. Default Size of the JAVA Pool  is  24 MB (Oracle 9i)

Streams Pool: The Streams pool stores buffered queue messages and provides memory for Oracle Streams capture processes and apply processes. The Streams pool is used exclusively by Oracle Streams.

Unless you specifically configure it, the size of the Streams pool starts at zero. The pool size grows dynamically as required by Oracle Streams.

Oracle Background Processes:  Oracle Mandatory background processes are started when oracle instance is started. These Processes are maintaining and enforces the relationships between Physical and Memory Structures.

We will see here Only ORACLE Mandatory background process.

Mandatory background Processes is:

• DBWn (Database Writer Process)
• LGWR (Log Writer Process)
• PMON (Process monitor process)
• SMON (System Monitor process)
• CKPT  ( Checkpoint Process) 
  

Physical Structure: Physical Structure is consists of oracle physical files.

• Data file: Data files Containing the actual data in the database.

• Online redo log files: Online Redo Log Files containing a record of changes made to the database to enable recovery of data in case failures.

• Control Files containing Information necessary to Maintain and Verify database  

• Parameter file

• Password File

• Archive redo log files.

Thank you !!.

See you in the next post with some more Architecture details.