Will walk through the SGA memory components in detail below
1. Database Buffer
Database buffer plays significant role in SGA by keeping blocks/granules read from datafile & allowing user processes to read/write the block concurrently. DB cache value is driven by parameter DB_CACHE_SIZE
Database buffer/Cache manages the blocks by an internal algorithm using write & LRU (Least Recently Used) lists
Write List holds all the dirty blocks, means the blocks which are modified by user processes in memory & should be written to DB file back again to retain consistent image of a row/data.
LRU list contains two ends, MRU (Most Recently Used) & LRU (Least Recently Used).
LRU end holds dirty, pined, free buffer/blocks. Pinned buffer means blocks which are currently in use & free is free J
Dirty blocks held by LRU end of LRU list are being moved to write list.
Whenever user process accesses the buffer it has been moved to MRU end of LRU list, mostly fresh/Recently read blocks from data files found in MRU end.
Cache hit/miss: First time if an oracle process requesting a block is found in database buffer is known as a cache hit, else it must fetch it from data file into buffer know as direct IO & should be considered as cache miss
Database buffer also holds static components keep (db_keep_cache_size) & recycle buffer (db_recycle_cache_size)
Data blocks of the segments allocated to KEEP buffer cache retained in memory
Database blocks of the segments allocated to RECYCLE are wiped out of memory as soon as they are no longer needed , making room for other RECYCLE segment blocks
DEFAULT buffer pool holds segment blocks which are not assigned to any of the above buffer pool
By default segments allocated to DEFAULT buffer pool
Oracle also supports non-default db block sizes in database buffer 2K, 4K, 8K, 16K, 32K by parameters DB_2K_CACHE_SIZE,
DB_4K_CACHE_SIZE,
DB_8K_CACHE_SIZE,
DB_16K_CACHE_SIZE,
DB_32K_CACHE_SIZE
DB_8K_CACHE_SIZE,
DB_16K_CACHE_SIZE,
DB_32K_CACHE_SIZE
DB buffer Flush occurs when...
-Checkpoint occurs/forced by alter system checkpoint;
-Dirty Buffer list is full & no more free block is available for incoming block
-Alter system flush BUFFER_POOL; is executed
Moving default segment pool to KEEP:
select owner,segment_name,buffer_pool from dba_segments where owner='SEBS' and segment_name='CDRV_RIC_PART';
alter table SEBS.CDRV_RIC_PART storage ( buffer_pool keep);
Verifying Buffer Pool stats:
select name,BLOCK_SIZE,CURRENT_SIZE,BUFFERS,RESIZE_STATE from v$buffer_pool;
2. Shared Pool
Shared Pool in SGA is further divided into Library & Dictionary Cache. SHARED_POOL_SIZE parameter reserves space for shared pool
Library Cache:
It holds shared/private sql area, sql’s & plsql code like function procedures & control structures like locks/Enques
Each user process has its private sql area (in shared server mode) & shared sql area stored in shared pool.
Shared sql area holds parse trees, explain plan , compilation program unit information of the sql’s , procedure , function, triggers executed by a user process which is shared among all the other user processes executing similar statement
Each executing session has its own private sql area , in dedicated server mode its located in PGA whereas in shared server mode it’s in shared pool
LRU algorithm is used to remove least used library information & make way to new sql’s stats
Dictionary Cache:
Also known as row cache as it holds data as complete row instead of specific block
Dictionary contains metadata of the objects & views , structure & its users/privileges
Oracle flushes the shared pool when…
-Analyze statement is used to update the objects stats
-Objects is Modified
-Objects is Invalidate/Re-validated, re-compilation
-alter system flush shared_pool; is executed
Use below query to get all components of shared pool:
select pool, name, bytes from v$sgastat where pool like '%shared_pool%' order by pool, name;
No comments:
Post a Comment