Oracle Database Overview - Memory Areas
One of the most important aspects of understanding the Oracle system architecture is understanding how instance memory is divided. We will look at an overview of the memory areas which have relevance to database usage with an SAP application as SAP does not make use of all the memory areas available.
There are two broad memory areas
This portion of SGA holds copies of the data blocks from the datafiles. The SQL operations on data objects are first performed on these blocks and then transferred to the datafiles by the DB Writer processes.
The Buffer pool is further divided into the following parts
Free Buffer, Pinned Buffer and Dirty Buffer form LRU list of the Buffer pool. Free Buffer is the LRU end of LRU List and Dirty Buffer is the MRU end. Each buffer again has data placed by last used order.
![Data buffer cache [Oracle Instance]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwHCPBo_5OkMDSQijt_vAlKurRiM93N0foyvRbNiAcVD1DdacvMCHOEBEUdTfspwMrsu0q3rjdaGyD_dvvlbWCl-Vp_nIcuC__G5tHr7rhcBze-HLtr8Vc5SNi4_haH_E71PaL_p1jLVpN/s400/Data-buffer-cache-Oracle-Memory.PNG "Data buffer cache [Oracle Instance]")
When a process requires data, it starts looking for it in the data buffer. If it finds that data, it is a cache hit otherwise it is a cache miss. In the event of a cache miss, the Oracle process has to copy the data from datafile into the LRU List.
Before copying, the process will first start looking for free space in the LRU list starting from the LRU end. As the process starts to scans for free space, if it hits a dirty data, it copies that over to the Write List.
It continues until it has found enough free space or if it hits a threshold to search. If it hits the threshold, it asks DBWn process to write some of the data blocks from Write List to datafiles and free up space. If it gets the required free space, it reads the data into the MRU end of LRU List.
Whenever an Oracle process accesses any of the data in the LRU list (cache hit), the data is moved to the MRU end of that buffer. Over the time, the older data (except for full table scans) moves to the LRU end of the buffer.
The size of data buffer is defined by DB_BLOCK_BUFFERS (in blocks) or by DB_CACHE_SIZE (in bytes) if using a dynamic SGA.
The size of redo buffer is defined by LOG_BUFFER (in bytes). SAP recommends to set it to 1 MB or less.
Dictionary cache (or Row Cache) contains meta information about database tables, views and users. As the meta information is stored in the form of tables or views (principally rows), Dictionary cache is also known as Row cache.
Shared SQL Area (or Shared Cursor Cache) is a part of Library Cache. Before any SQL statement can be executed, the statement is first parsed and stored in the Library Cache along with its execution plan. Each SQL statement has parts that are common to two or more users executing similar statement and bind variables that are private to each user. The Shared SQL Area stores the shared part of the statement.
The size of shared pool is set by SHARED_POOL_SIZE (in bytes). SAP recommends to set this value to 600 MB or above.
The SGA comprises of other areas such as Java Pool, Large Pool and Streams Pool which are not utilized by SAP. However, these memory cannot be set to 0 if you are using Oracle utilities (RMAN, Oracle VM etc)
It is recommended to limit SGA to 1/4th of the RAM.
There are two broad memory areas
- Memory shared by all the processes - System Global Area in Oracle
- Memory assigned to exactly one process - Program Global Area in Oracle
System Global Area [SGA]
Database Buffer Cache
Also known as Buffer Pool or Data buffer or "Cache"This portion of SGA holds copies of the data blocks from the datafiles. The SQL operations on data objects are first performed on these blocks and then transferred to the datafiles by the DB Writer processes.
The Buffer pool is further divided into the following parts
- Free Buffer - free space
- Pinned Buffer - holds data that is currently being accessed
- Dirty Buffer - holds data that is modified, but not moved to Write List
- Write List - holds data that is modified and ready to be written to disk
Free Buffer, Pinned Buffer and Dirty Buffer form LRU list of the Buffer pool. Free Buffer is the LRU end of LRU List and Dirty Buffer is the MRU end. Each buffer again has data placed by last used order.
![Data buffer cache [Oracle Instance]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwHCPBo_5OkMDSQijt_vAlKurRiM93N0foyvRbNiAcVD1DdacvMCHOEBEUdTfspwMrsu0q3rjdaGyD_dvvlbWCl-Vp_nIcuC__G5tHr7rhcBze-HLtr8Vc5SNi4_haH_E71PaL_p1jLVpN/s1600/Data-buffer-cache-Oracle-Memory.PNG "Data buffer cache [Oracle Instance]")
When a process requires data, it starts looking for it in the data buffer. If it finds that data, it is a cache hit otherwise it is a cache miss. In the event of a cache miss, the Oracle process has to copy the data from datafile into the LRU List.
Before copying, the process will first start looking for free space in the LRU list starting from the LRU end. As the process starts to scans for free space, if it hits a dirty data, it copies that over to the Write List.
It continues until it has found enough free space or if it hits a threshold to search. If it hits the threshold, it asks DBWn process to write some of the data blocks from Write List to datafiles and free up space. If it gets the required free space, it reads the data into the MRU end of LRU List.
Whenever an Oracle process accesses any of the data in the LRU list (cache hit), the data is moved to the MRU end of that buffer. Over the time, the older data (except for full table scans) moves to the LRU end of the buffer.
The size of data buffer is defined by DB_BLOCK_BUFFERS (in blocks) or by DB_CACHE_SIZE (in bytes) if using a dynamic SGA.
Redo Buffer
Redo Buffer is circular buffer. The contents of this buffer are periodically written to the active online redo log files by LGWR process. Database operations such as INSERT, UPDATE, DELETE, CREATE, ALTER and DROP are logged into the this buffer. These operations help redo the changes to the table and hence are important in restore of database in the event of a crash.
Shared Pool
Shared Pool is made up of various memory areas of which Dictionary Cache and Shared SQL Area are of high importance.Dictionary cache (or Row Cache) contains meta information about database tables, views and users. As the meta information is stored in the form of tables or views (principally rows), Dictionary cache is also known as Row cache.
Shared SQL Area (or Shared Cursor Cache) is a part of Library Cache. Before any SQL statement can be executed, the statement is first parsed and stored in the Library Cache along with its execution plan. Each SQL statement has parts that are common to two or more users executing similar statement and bind variables that are private to each user. The Shared SQL Area stores the shared part of the statement.
The size of shared pool is set by SHARED_POOL_SIZE (in bytes). SAP recommends to set this value to 600 MB or above.
The SGA comprises of other areas such as Java Pool, Large Pool and Streams Pool which are not utilized by SAP. However, these memory cannot be set to 0 if you are using Oracle utilities (RMAN, Oracle VM etc)
It is recommended to limit SGA to 1/4th of the RAM.
Program Global Area [PGA]
PGA memory is dynamically allocated to the server processes. PGA is used to hold session data, query execution state etc and to facilitate execute operations such as sorts, hash joins, bitmapping etc. If PGA is insufficient, temporary tablespace (PSAPTEMP in SAP) is used.
The size of PGA is determined by the parameters SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE or by PGA_AGGREGATE_TARGET if automatic PGA administration is used.
Comments
Post a Comment