In this Article i am going to explain you about the Memory structure of oracle database.
In Oracle
database memory structure mainly consist of SGA and PGA which are core for the
instance to start in the database.
1) System Global Area (SGA)
2) Program Global Area (PGA)
System
Global Area:
In simple
words SGA also called as Shared Global Area which is allocated at the instance
startup and shared by all the processes/Thread, hold information of the
instance and its behavior.
SGA consist
of 3 components below are they
1) Database Buffer
Cache
2) Redo Log Buffer
Cache
3) Shared Pool
a) Library Cache
b) Data Dictionary
Cache
4) Large Pool
5) Java Pool
6) Stream Pool
1) Database
Buffer Cache:
Database Buffer cache hold copies of data and stored the data after
reading it from the disk and improve performance by storing the data in the
memory which decrease the I/O to disk.
All users connected to the database share access to the Buffer cache.so
DB Buffer Cache should be large enough to avoid Disk I/O.
Blocks are managing in the
buffer cache in single place with two lists which are
a) Dirty blocks
list:
Dirty blocks are the blocks which is need
to be written to the disk using a process called Database writer (DBWR)
b) Non Dirty Blocks
lists: it is the least Recently used(LRU) which holds the free buffer, pinned
buffer and dirty buffer that not have been moved to the write list.
When a user
process a query the server process finds the data in the buffer cache and give
the output it is known as cache hit. If it cannot find the data, then it should
obtain from the datafile which is present in the disk.
Parameter use to manage the database buffer
cache is DBA_CACHE_SIZE
The size of
the buffer is based on the parameter DB_BLOCK_SIZE
With the
above initialization parameter, we also have below parameter for DB Buffer
cache
db_2k_cache_size
db_4k_cache_size
db_8k_cache_size
db_16k_cache_size
db_32k_cache_size
db_keep_cache_size
db_recycle_cache_siz
2) Redo Log Buffer cache:
Redo log buffer will have redo log entries of
change data which is done on daily bases using the DML operations (Insert,
update, delete, adding rows).
In the
redo log buffer, the data which is to be written to the online redo log will be
cached temporary. Process which writes the data from log buffer to online redo
log is Log writer (LGWR). using the online redo logs DBA can recover the
database to the point of failure.
Parameter use to manage the Redo Log buffer cache is LOG_BUFFER
by setting this parameter we allocate the bytes to the redo Log buffer.
3)
Shared Pool:
Shared pool stores the most recently used SQL executed
and data requested by the process so that the data should not be read from the
disk if it ask again. The size of the shared pool should not be too less or too
large it will affect the performance of query.
Shared Pool consist of more components
a)
Library Cache
b)
Data Dictionary Cache
c) Result Cache
a)
Library Cache:
it’s a piece of memory within the SGA where it stores all the SQL statements and text of the statement goes in to this library cache. It also has shared SQL area and Private SQL Area where the execution plan is saved.
b) Data Dictionary cache:
It is the highly accessed memory structure that provide
information on oracle object data dictionary such as tables, column definitions
username password and privileges roles etc. and validate them.
It is also called Dictionary Cache or Row cache.
c)
Result Cache:
Result
Cache is a new feature in Oracle 11g, it caches the results of
queries and puts it into the shared pool. The DBMS_RESULT_CACHE package
provides administration subprograms, which flush all cached result and turn
result caching on or off. If you have a query that is executed repeatedly and
reads data that rarely changes, this feature can increase performance significantly.
The parameter used to set the
shared pool size is SHARED_POOL_SIZE
4)
Large Pool:
Large pool is an optional components of SGA
use for the large memory allocation such as for using the query with Parallel
query, taking backup using RMAN, shared server, Backup and restore
Parameter use to set the value
for large pool is LARGE_POOL_SIZE
5)
Java Pool:
Java pool is also an optional memory in
SGA used to stores the java codes such as run state, methods, Classes, Session
code and data in JVM
Parameter use to set the Java Pool Is
JAVA_POOL_SIZE
6)
Streams Pool:
This is also an optional memory allocated
in SGA for the stream processing
Parameter use to set
the stream pool is STREAMS_POOL_SIZE
The size of the SGA cannot exceed
the value of parameter SGA_MAX_SIZE.
Memory is allocated to the SGA as
contiguous virtual memory in units called granules. Granule size is depending
on the estimated total size of the SGA which depends on SGA_MAX_SIZE parameter.
Granule are sized as following:
·
If the SGA is less than
128MB in total, each granule is 4MB
·
If the SGA is greater than
128MB in total, each granule is 16MB.
Granules
are assigned to the database buffer cache and shared pool and these two memory
components can dynamically grow and shrink.it allocated when the oracle
database server starts the database instance.
Query
to look the size of your SGA is:
Select
name, value from v$parameter where name in
(‘shared_pool_size’,’java_pool_size’,’stream_pool_size’,’log_buffer’,’db_cache_size’,’db_2k_cache_size’,’large_pool_size’);
Program Global Area(PGA)
The
program Global Area is also known as Process Global Area and a part of memory
allocated that is outside of SGA.it stores the data and control information for
a single process or a single BP.it is also allocated when the process is
created.
This is not a shared part of memory –one PGA
to each Process only
·
Memory reserved for each
user connecting to an oracle process
·
Allocated when a process is
created
·
Deallocated when the process
is terminated
·
Used by only one process
The Content of
the PGA varies but generally contain the following:
a)
Private SQL Area:
Data for binding variables and runtime memory allocations. A user session issuing
SQL statements has a private SQL Area that may be associated with a shared SQL
Area.
b)
Session memory: Memory that holds session variables and other
session information
c)
SQL Work Area:
Memory allocated for sort, hash-join, bitmap merge.
Private
SQL Area is located according to the connection to the database server like in
·
Dedicated Server
Environment: Private SQL Area is located in the PGA
·
Shared Server Environment:
Private SQL Area is located in the SGA.
So
in this blog you got to know about in depth of memory structure in Oracle
database thanks for reading.
Posts
0 comments:
Post a Comment