Oracle database 11g System Global Area
We will now learn about Oracle database 11g System Global Area. In this post, we will discuss Database buffer cache, Redo buffer cache, shared pool and few more memory components that reside in SGA. Oracle database 11g System Global Area
Oracle database 11g System Global Area (SGA)
The main components of SGA are
- Database Buffer cache
- Redo Buffer cache
- Shared pool
The shared pool is further divided into two sections such as Data Dictionary Cache and Library Cache.
Database buffer cache
The database buffer cache is the portion of the SGA that holds copies of data blocks read from data files. All users concurrently connected to the instance share access to the database buffer cache.
whenever a query requests a Server Process to look for data.The server process first looks it in the Database Buffer Cache to determine if the requested information is already there in database buffer cache – thus the information would not need to be retrieved from disk and this would speed up performance. If the information is not in the Database Buffer Cache, the Server Process retrieves the information from disk and stores it to the cache.
Database blocks are kept in the Database Buffer Cache according to a Least Recently Used (LRU) algorithm and are aged out of memory if a buffer cache block is not used in order to provide space for the insertion of newly needed database blocks.
There are three states of a buffer in buffer cache:
Unused – a buffer is available for use – it has never been used or is currently unused.
Clean – a buffer that was used earlier – the data has been written to disk.
Dirty – a buffer that has modified data that has not been written to disk.
Free buffers vs pinned buffers
Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed.
Write list vs LRU list
The buffers in the cache are organized in two lists, the write list and the least recently used (LRU) list. The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list.
When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list – this causes dirty buffers to age toward the LRU end of the LRU list.
cache hit vs cache miss
If the requested data is available in database buffer cache, it is called cache hit.
If the requested data is not available in database buffer cache and needs to be fetched from disk, it is called cache miss.
Redo log buffer
Redo log buffer holds information about changes made to the database, called redo. Redo is generated by each transaction in the database as it makes a change (e.g. insert, update, delete, or any DDL operations). These redo entries contain the information needed to reconstruct any changes that have been made to the database. Redo is used to recover the database in the event of a failure.
As changes occur in the database, the redo generated by those changes is stored in the redo log buffer. When certain conditions occur, the redo log buffer will be flushed by Oracle to the online redo logs that exist on physical disk by the Oracle background process called the Log Writer (LWGR).
We will discuss the conditions when the LGWR writes the data to redo log file here background process
Shared pool
The shared pool is further divided into library cache and data dictionary cache.
Library cache:
Parsing takes place is library cache. Parsing is nothing but checking for syntactic check and semantic check.
There are two types of parsing
- Hard parsing
- Soft parsing
For the first time, every statement has to undergo hard parsing, soft parsing is nothing but there is no reparsing.
Library cache is further divided into
- Shared SQL area
- Shared PL/SQL area.
Shared SQL area contains the parse trees and execution plans, shared PL/SQL area contains compiled PL/SQL program units like procedures, functions, packages etc. The second time when an identical SQL statement is executed, Oracle takes the advantage of existing parse trees and execution plans.
During the parse phase, server process looks into data dictionary cache to resolve names of the objects and access privileges.
If the information is available in data dictionary cache server process uses it, if the information is not available, the server process goes to data dictionary(system.dbf) and gets the information and stores it in data dictionary cache.
Data Dictionary Cache
Data dictionary cache is also called a row cache.
The Data Dictionary Cache is a memory structure that caches data dictionary information that has been recently used.
The data dictionary is a collection of read-only tables and views. These read-only tables are also called as base tables, at the time of creating the database oracle internally calls sql.dsq script to create the base tables. Information in the base tables are in cryptic format. We create views to access these base tables by executing catalog.sql
There are two types of views for data dictionary, they are
- Static views, e.g, views starting with DBA_, e.g., DBA_TABLES
- Dynamic performance views, e.g., views starting with v$, e.g., V$INSTANCE.
Oracle uses LRU(Least recently used) to flush out the contents from various sub-components.
The other SGA contents include
Large Pool
Several features require large memory allocations in the shared pool, such as a shared server, parallel query, or Recovery Manager. Oracle recommends using a separate memory area—the large pool—to segregate the System Global Area (SGA) memory used by these features.
Java Pool
The Java Pool is an optional memory object but is required if the database has Oracle Java installed and in use for Oracle JVM (Java Virtual Machine).
The size is set with the JAVA_POOL_SIZE parameter that defaults to 24MB
The Java Pool is used for memory allocation to parse Java commands and to store data associated with Java commands.
Storing Java code and data in the Java Pool is analogous to SQL and PL/SQL code cached in the Shared Pool.
Streams Pool
This pool stores data and control structures to support the Oracle Streams feature of Oracle Enterprise Edition.
Oracle Steams manages to share data and events in a distributed environment.
In the next post, we will discuss Oracle process architecture
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.