Values set for SGA and PGA
col DISPLAY_VALUE for a30
select name,display_value
from v$parameter
where name like 'mem%target'
or name like 'pga%'
or name like 'sga%'
;
Current allocation :
select round(sum(bytes)/1024/1024/1024,3) SGA_G
from v$sgastat;
select round(value/1024/1024/1024,3) PGA_G
from v$pgastat
where name = 'total PGA allocated';
Historical use :
select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,trunc(SN.END_INTERVAL_TIME,'mi') time
from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo
from DBA_HIST_SGASTAT
group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo
from DBA_HIST_PGASTAT where name = 'total PGA allocated'
group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn
where sn.snap_id=sga.snap_id
and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
and sn.snap_id=pga.snap_id
and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;
Calculate value for PGA and SGA
set pages 999;
column pga_size format 999,999,999
accept hwm number prompt 'Enter high-water mark of connected users:'
select &hwm*(2048576+a.value+b.value) pga_size -- Reserve 2MB RAM for windows and 1MB in unix for each dedicated connection
from v$parameter a, v$parameter b
where a.name = 'sort_area_size'
and b.name = 'hash_area_size';
SGA = Total server RAM - PGA for N connections - 10%(Total server RAM in Unix)
If there's increase in connected sessions, it is possible that it exceed the amount of RAM on the server, causing active programs to go out to the swap disk.
You should not create an SGA that is larger than two-thirds the size of your server's physical random access memory (RAM).A high degree of paging can result from an SGA that is too large.
Intimate Shared Memory (ISM) and Dynamic Intimate Shared Memory (DISM) feature
DISM should always be turned off on x86-based systems running Oracle Solaris 10.
DISM is turned on by default for Oracle Database 11.2.0.1 on Oracle Solaris,
ISM is used if SGA_MAX_SIZE parameter is equal to or smaller than the size required for all SGA components combined. ISM segments cannot be re-sized.Since ISM memory is locked, no swap space is needed to back it.
DISM is used if SGA_MAX_SIZE parameter is larger than the size required for all SGA components combined.
In DISM , oracle database is responsioble for locking the memory.It locks an amount of memory determined
by the total of individaul elements of SGA.From Oracle Database 10g, the size of the SGA can also be controlled by the SGA_TARGET
parameter rather tuning the buffer cache and shared pool individually.
As DISM memory is not automatically locked, swap space must be allocated.
DISM is not recommended for use with Oracle Solaris on x86-based systems.
From the 11.2.0.1 , it's used if MEMORY_TARGET or MEMORY_MAX_TARGET is set by the user
By default , 40% of physical memory is aggaigned to MEMORY_TARGET
Out of MEMORY_TARGET value , 60% to SGA and 40% to PGA i.e 60% of memory used by Oracle Database will be locked i.e 24% of physical memory.
And project.max-shm-memory parameter or zone.max-shm-memory is 25% of RAM.
If memory is dynamically diverted from the PGA to the SGA, the 25% threshold is likely to be
exceeded. It is very important to set the project.max-shm-memory parameter (or zone.maxshm-memory) high enough to ensure that all SGA memory can be locked under all circumstances.
Shutdown normal and immediate
1.Database is closed (Writes data in SGA to datfiles,recovery data in SGA to redo log files.checkpoint performed on DF header and CF.May get skipped in abort).
2.closes all online datafiles and redo log files
3.Oracle unmounts the database to disassociate it from the instance
4.Oracle closes the control files
5.shutdown down the instance (SGA removed , background processes terminated.May get skipped in abort)
col DISPLAY_VALUE for a30
select name,display_value
from v$parameter
where name like 'mem%target'
or name like 'pga%'
or name like 'sga%'
;
Current allocation :
select round(sum(bytes)/1024/1024/1024,3) SGA_G
from v$sgastat;
select round(value/1024/1024/1024,3) PGA_G
from v$pgastat
where name = 'total PGA allocated';
Historical use :
select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,trunc(SN.END_INTERVAL_TIME,'mi') time
from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo
from DBA_HIST_SGASTAT
group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo
from DBA_HIST_PGASTAT where name = 'total PGA allocated'
group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn
where sn.snap_id=sga.snap_id
and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
and sn.snap_id=pga.snap_id
and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;
Calculate value for PGA and SGA
set pages 999;
column pga_size format 999,999,999
accept hwm number prompt 'Enter high-water mark of connected users:'
select &hwm*(2048576+a.value+b.value) pga_size -- Reserve 2MB RAM for windows and 1MB in unix for each dedicated connection
from v$parameter a, v$parameter b
where a.name = 'sort_area_size'
and b.name = 'hash_area_size';
SGA = Total server RAM - PGA for N connections - 10%(Total server RAM in Unix)
If there's increase in connected sessions, it is possible that it exceed the amount of RAM on the server, causing active programs to go out to the swap disk.
You should not create an SGA that is larger than two-thirds the size of your server's physical random access memory (RAM).A high degree of paging can result from an SGA that is too large.
Intimate Shared Memory (ISM) and Dynamic Intimate Shared Memory (DISM) feature
DISM should always be turned off on x86-based systems running Oracle Solaris 10.
DISM is turned on by default for Oracle Database 11.2.0.1 on Oracle Solaris,
ISM is used if SGA_MAX_SIZE parameter is equal to or smaller than the size required for all SGA components combined. ISM segments cannot be re-sized.Since ISM memory is locked, no swap space is needed to back it.
DISM is used if SGA_MAX_SIZE parameter is larger than the size required for all SGA components combined.
In DISM , oracle database is responsioble for locking the memory.It locks an amount of memory determined
by the total of individaul elements of SGA.From Oracle Database 10g, the size of the SGA can also be controlled by the SGA_TARGET
parameter rather tuning the buffer cache and shared pool individually.
As DISM memory is not automatically locked, swap space must be allocated.
DISM is not recommended for use with Oracle Solaris on x86-based systems.
From the 11.2.0.1 , it's used if MEMORY_TARGET or MEMORY_MAX_TARGET is set by the user
By default , 40% of physical memory is aggaigned to MEMORY_TARGET
Out of MEMORY_TARGET value , 60% to SGA and 40% to PGA i.e 60% of memory used by Oracle Database will be locked i.e 24% of physical memory.
And project.max-shm-memory parameter or zone.max-shm-memory is 25% of RAM.
If memory is dynamically diverted from the PGA to the SGA, the 25% threshold is likely to be
exceeded. It is very important to set the project.max-shm-memory parameter (or zone.maxshm-memory) high enough to ensure that all SGA memory can be locked under all circumstances.
Shutdown normal and immediate
1.Database is closed (Writes data in SGA to datfiles,recovery data in SGA to redo log files.checkpoint performed on DF header and CF.May get skipped in abort).
2.closes all online datafiles and redo log files
3.Oracle unmounts the database to disassociate it from the instance
4.Oracle closes the control files
5.shutdown down the instance (SGA removed , background processes terminated.May get skipped in abort)
