DBA

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

• Java Pool
• Large Pool

* 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.