Memory Struture:-
* SGA (system global area) --- Allocated When instance started.
* PGA (program global Area) --- Allocated when server process Started.
Sytem Global Area ( Shared Global Area):-
- Shared Pool ( Library Cache and Data Dictionary Cache)
- Database buffer Cache
- Redo log buffer cache
two optionally memory struture
* Sga is used to store database information that is shared by database process.It Contains data and control information for the database.
* Its allocated for the virtual memory of the computer where oracle resides.
sql> show sga; (this is view of sga).
Dynamic Sga :-
* It allows sga configuration changes without Shutdown the instance . It Can be shared pool ,database buffer cache ,redo log buffer cache.
buffer ( temporarly storage one,example browsing any page when click on any link its seeking that page very slowly ,when you enterd backside button,its coming very fastly).
* A granule is a unit of contiguous(in contact or sharing an edge) virtual memory allocation.
* one granule size 4MB means if estimated sga size is <128mb.
*One granule size is 16MB Otherwise. >128MB.
*buffer cache,large pool,shared pool are allowed to shrink and grow based on granules boundries.
*Allocation of granules via ALTER SYSTEM
*Target size in granules tracked and displayed by the V$BUFFER_POOL view.
Shared Pool :-
* Its used to store the most recently executed sal statments and data definitions.
- Library cache
- Data dictionary cache
sql> alter system set shared_pool_size=64MB;
* It Contains both fixed and variable size.
* Variable Struture ---- It grow and shrink depends on user programme requirments.
** Total sga size cannot exceed sga_max_size.
Library Cache :-
* the query or sql statment against the database.
* It stores information about most recently executed used shared sql and pl/sql statments.
* It managed by least recently used (LRU) algorithm.
* size determined by shared pool size.
* If the size of the shared pool too small,statments are reloaded into the library cache ,which affects performance.
* As the cache fills, Less recently used execution paths and parse trees removed from the library cache to make room for the new entries. They eventually aged out.
Data Dictionary Cache :-
* Its a collection of most recently used definitions in the database.
* It includes information about
- Database files
- Indexes
- tables
- columns
- users
- priviliges and other database objects.
* During the parse phase ,the server process looks at the data dictionary information to resolve object names and validate access.
* Caching the data dictionary information into memory improves response time on queries.
* Size determined by shared pool sizing.
Database Buffer Cache :-
* It stores copies of data block that retrived from the datafiles.
* It enables great performance gains when you obtain and update data.
* It is managed by LRU algorithm
* db_block_size determined by the primary block size.
- query process --->server process looks ----> database buffer cache for any need blocks.
- If not found, ----> server process reads the block -----> datafiles.and places copies in db cache.
* Consistent of independent of sub-caches
- db_cache_size --> default buffer cache size only and cannot set to be zero.
- db_keep_cache_size ---> To retain blocks in memory that are likely to be reused.
- db_recycle_cache_size ---> eliminate block from the memory that have little reused.
* Db cache dynamically resized
- sql>alter system set db_cache_size = 96M;
* Size of each buffer cache is equal to the size of an Oracle Block.
* Non-standard block size any power of two value between 2kb and 32kb.
Redolog Buffer Cache :-
* It records all changes made to the database data blocks.
* Its primary purpose recovery.
* Changes recorded within are called redo entries.
* Redo entries contain information to the reconstruct or redo changes.
* size is defined by Log_buffer.
