3. Large Pool
Large pool was brought in to optimize shared pool by isolating heavy IO servers operation like Backup/restoration using parameter LARGE_POOL_SIZE.
Large Pool allocates memory required during disk IO server process operations of Backup & recovery.
Also parallel query buffers & Oracle XA memory allocation taken care by large pool
select pool, name, bytes from v$sgastat where pool like '%large_pool%' order by pool, name;
Java pool memory is used for all session-specific Java code and data within the JVM.
The Java Pool Advisor statistics provide information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate. The Java Pool Advisor is internally turned on when statistics_level is set to TYPICAL or higher. These statistics resets when the advisor is turned off.
select pool, name, bytes from v$sgastat where pool like '%java%' order by pool, name;
Redo Log Buffer is circular buffer residing in SGA. It holds redo entries generated due to DB changes. Redo Entries contains information to reconstruct the changes made to database by DML(insert, update,delete) & DDL (alter, create or replace , drop , truncate ) operations. Redo Buffer is flushed to Redo Log files required for Recovery.
log_buffer parameter sets the buffer value in SGA, which is not auto tuned by ASMM(Automatic Shared Memory Management).
log_buffer parameter sets the buffer value in SGA, which is not auto tuned by ASMM(Automatic Shared Memory Management).
The server processes generate redo data into the log buffer as they make changes to the data blocks in the buffer. LGWR subsequently writes entries from the redo log buffer to the online redo log
6. Fixed SGA
The fixed SGA is a un alterable component of the SGA that varies in size based on platform & release.
The fixed SGA contains references to the other components of the SGA and variables that contain the values of various parameters.
Its bootstrap section used by Oracle internally to find the other bits and pieces of the SGA.
select pool, name, bytes from v$sgastat where name like '%fixed_sga%' order by pool, name;
7. Stream Pool
The streams pool is used exclusively by Oracle Streams.
It stores buffered queue messages, and it provides memory for Oracle Streams capture processes and apply processes.
Initial size of the stream pool set by streams_pool_size parameter & default value is zero.
The pool size grows dynamically as needed when Oracle Streams is used.
8. Flash Back Buffer
Oracle Flashback feature supports retrieving the historical stat of the db object using Database flashback logs, undo or recyclebin.
Flashback requires past image of the objects retrieved using various methods e.g flashback query version to fetch data as on historical timestamp, recyclebin to recover dropped table in which tables is renamed to hexadecimal naming format & retained till free space pressure arrives in particular tablespace
To support flashback mechanism flashback buffers stores altered/dirty buffers from database buffer before being moved to database flash back logs/disk by RVWR background process (will discuss in later stage).
9. Program Global Area (PGA)
For every user connecting to DB a PGA area is created from PGA memory from OS RAM
PGA consists of stack space, session/cursor information & Private sql area (in dedicated server mode)
Oracle 10g onwards PGA size is managed by pga_aggregate_target & WORKAREA_SIZE_POLICY (value AUTO) parameter else it can managed by setting sort_area_size and hash_area_size parameters
select * from v$pga_target_advice order by pga_target_for_estimate;
select * from v$pgastat;
select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;
The PGA has great impression performance due to queries involving huge sorts (ORDER by and GROUP BY commands in SQL)
Apart from this PGA supports Hash-join , Bitmap related operations , Write buffers used by bulk load operations
Below Query can be used for optimal re-sizing of PGA & to review PGA stats
select * from v$pga_target_advice order by pga_target_for_estimate;
select * from v$pgastat;
select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;
10. UGA
The User Global Area (UGA) is memory resides in OS RAM holds session information like sorts & hash joins of each user process.
In dedicated server mode UGA is a part of PGA being one to one relationship between User & Server Process
Whereas in shared server mode it has been moved to SGA as user sessions are served by multiple server processes
select name, sum(value/1024) "Value in KB"
from v$statname n,
v$session s,
v$sesstat t
where s.sid=t.sid
and n.statistic# = t.statistic#
and s.type = 'USER'
and s.username is not NULL
and n.name in ('session pga memory', 'session pga memory max',
'session uga memory', 'session uga memory max')
group by name
/
No comments:
Post a Comment