Sga in oracle
Alter system set shared_pool_size=15m scope=spfile; to set the size of all compenents like this way
Shared Pool:(shared_pool_size=15m)
----------------------------------------
the shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information.
The shared pool is involved in almost every operation that occurs in the database.
parts of sp"
--------------
Library Cache
Data Dictionary Cache
Server Result Cache
Reserved Pool
Library cache:
the library cache that stores executable SQL and PL/SQL code.
maintain hard parse and hard parse information
-Shared SQL area
The database uses the shared SQL area is first
it contains the statement parse tree and execution plan.
-Private SQL area
Each session issuing a SQL statement has a private SQL area in its PGA
Data Dictionary Cache: dict
This cache holds information about database objects.
The cache is also known as the row cache because it holds data as rows instead of buffers.(before 10g version.
Server Result Cache
Unlike the buffer pools, the server result cache holds result sets and not data blocks.
result_cache_max_result=1gset size
result_cache_mode= manual/auto
result_cache_max_size=20g(based your ram)
exec DBMS_RESULT_CACHE.BYPASS ( bypass_mode IN false/true);
v$db_object_cache
v$librarycache
v$result_cache_objects,
Reserverd pool:
Oracle Database can use to allocate large contiguous chunks of memory.
over a 5kb of chunks will be stored.
v$shared_pool_reserved
--------------------------------------------------------------------------------
DBBC(Database buffer Cache)
Modification of an object held in dbbc.
In the memory area that stores copies of data blocks read from data files.
Keep frequently accessed blocks in the cache and write infrequently accessed blocks to disk.
The DBBC, also called the buffer cache, is the memory area that stores copies of data blocks read from data files.
-Storage for data blocks that have been retrieved from data files
-Provides optimization boost for DML operations (UPDATES)
-Managed via the LRU algorithm
Type oF Buffer Pools
Default pool
This pool is the location where blocks are normally cached.
Unless you manually configure separate pools, the default pool is the only buffer pool.
Keep pool
This pool is intended for blocks that were accessed frequently,
but which aged out of the default pool because of lack of space.
The goal of the keep buffer pool is to retain objects in memory, thus avoiding I/O operations.
Recycle pool
This pool is intended for blocks that are used infrequently.
A recycle pool prevent objects from consuming unnecessary space in the cache.
Buffer States
Unused like Free
This type of buffer is the easiest for the database to use.
Clean
This buffer was used earlier and now contains a read-consistent version of a block as of a point in time.
The block contains data but is "clean" so it does not need to be checkpointed. The database can pin the block and reuse it.
Dirty(fully modified blocks)
The buffer contain modified data that has not yet been written to disk.
Buffer Block Status:-
-A cold buffer is one that has not been recently used.
-A hot buffer is frequently accessed and has been recently used.
Buffer Modes:
current -current mode your seesion without commit;
consistent -consistent mode is unod buffer if you commit the data,the information shown another user's.
If the process does not find Data in the buffer in memory (a cache miss),
Copies the block from a data file into memory (a physical read)
Performs a logical read of the buffer that was read into memory
db_cache_size is the default buffer cache for Oracle 11g
db_keep_cache_size is for the KEEP buffer cache
db_recycle_ cache_size is for the RECYCLE buffer cache
db_nk_ cache_size
desc v$db_cache_advice
Redo Log Buffer(log_buffer):-
It is maninly used for recovery purpose,
The redo log buffer is a circular buffer whatever changes made to the database store in this buffer.
Redo is used fro reconstruct, or redo, changes made to the database by DML or DDL operations.
lgwr sequentialy write from redo buffer to disk without user wait.
large pool:-(large_pool_size=3m)
it reduce the burden of shared pool.
it maintance user information and oracle XA interface
Message buffers used in the parallel execution of statements
Buffers for Recovery Manager (RMAN) I/O slaves
parallel_automatic_tunning=true
Java Pool(java_pool_size=4m)
The Java pool is an area of memory that stores all session-specific Java code and data within the Java Virtual Machine (JVM).
Streams Pool( stream_poool_size=4m)
The Streams pool stores buffered queue messages and provides memory for Oracle Streams capture processes and apply processes.
The Streams pool is used exclusively by Oracle Streams.
The Streams pool is used exclusively by Oracle Streams.
Shared Pool:(shared_pool_size=15m)
----------------------------------------
the shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information.
The shared pool is involved in almost every operation that occurs in the database.
parts of sp"
--------------
Library Cache
Data Dictionary Cache
Server Result Cache
Reserved Pool
Library cache:
the library cache that stores executable SQL and PL/SQL code.
maintain hard parse and hard parse information
-Shared SQL area
The database uses the shared SQL area is first
it contains the statement parse tree and execution plan.
-Private SQL area
Each session issuing a SQL statement has a private SQL area in its PGA
Data Dictionary Cache: dict
This cache holds information about database objects.
The cache is also known as the row cache because it holds data as rows instead of buffers.(before 10g version.
Server Result Cache
Unlike the buffer pools, the server result cache holds result sets and not data blocks.
result_cache_max_result=1gset size
result_cache_mode= manual/auto
result_cache_max_size=20g(based your ram)
exec DBMS_RESULT_CACHE.BYPASS ( bypass_mode IN false/true);
v$db_object_cache
v$librarycache
v$result_cache_objects,
Reserverd pool:
Oracle Database can use to allocate large contiguous chunks of memory.
over a 5kb of chunks will be stored.
v$shared_pool_reserved
--------------------------------------------------------------------------------
DBBC(Database buffer Cache)
Modification of an object held in dbbc.
In the memory area that stores copies of data blocks read from data files.
Keep frequently accessed blocks in the cache and write infrequently accessed blocks to disk.
The DBBC, also called the buffer cache, is the memory area that stores copies of data blocks read from data files.
-Storage for data blocks that have been retrieved from data files
-Provides optimization boost for DML operations (UPDATES)
-Managed via the LRU algorithm
Type oF Buffer Pools
Default pool
This pool is the location where blocks are normally cached.
Unless you manually configure separate pools, the default pool is the only buffer pool.
Keep pool
This pool is intended for blocks that were accessed frequently,
but which aged out of the default pool because of lack of space.
The goal of the keep buffer pool is to retain objects in memory, thus avoiding I/O operations.
Recycle pool
This pool is intended for blocks that are used infrequently.
A recycle pool prevent objects from consuming unnecessary space in the cache.
Buffer States
Unused like Free
This type of buffer is the easiest for the database to use.
Clean
This buffer was used earlier and now contains a read-consistent version of a block as of a point in time.
The block contains data but is "clean" so it does not need to be checkpointed. The database can pin the block and reuse it.
Dirty(fully modified blocks)
The buffer contain modified data that has not yet been written to disk.
Buffer Block Status:-
-A cold buffer is one that has not been recently used.
-A hot buffer is frequently accessed and has been recently used.
Buffer Modes:
current -current mode your seesion without commit;
consistent -consistent mode is unod buffer if you commit the data,the information shown another user's.
If the process does not find Data in the buffer in memory (a cache miss),
Copies the block from a data file into memory (a physical read)
Performs a logical read of the buffer that was read into memory
db_cache_size is the default buffer cache for Oracle 11g
db_keep_cache_size is for the KEEP buffer cache
db_recycle_ cache_size is for the RECYCLE buffer cache
db_nk_ cache_size
desc v$db_cache_advice
Redo Log Buffer(log_buffer):-
It is maninly used for recovery purpose,
The redo log buffer is a circular buffer whatever changes made to the database store in this buffer.
Redo is used fro reconstruct, or redo, changes made to the database by DML or DDL operations.
lgwr sequentialy write from redo buffer to disk without user wait.
large pool:-(large_pool_size=3m)
it reduce the burden of shared pool.
it maintance user information and oracle XA interface
Message buffers used in the parallel execution of statements
Buffers for Recovery Manager (RMAN) I/O slaves
parallel_automatic_tunning=true
Java Pool(java_pool_size=4m)
The Java pool is an area of memory that stores all session-specific Java code and data within the Java Virtual Machine (JVM).
Streams Pool( stream_poool_size=4m)
The Streams pool stores buffered queue messages and provides memory for Oracle Streams capture processes and apply processes.
The Streams pool is used exclusively by Oracle Streams.
The Streams pool is used exclusively by Oracle Streams.
Comments
Post a Comment