SGA（System Global Area）
全システム・グローバル領域 18714712バイト → 以下の総計
Fixed Size 49240バイト → 固定(OS、OracleのVersionにより異なる)
Variable Size 10203136バイト → shared_pool_size + 他要素
Database Buffers 8388608バイト → db_block_size * db_block_buffers
Redo Buffers 73728バイト → log_buffer(マニュアル上)
SQL> show sga; # sys権限
Total System Global Area 457148384 bytes
Fixed Size 731104 bytes
Variable Size 352321536 bytes
Database Buffers 100663296 bytes
Redo Buffers 3432448 bytes
#-------------------------------------------------------------------------------------------------------------
SET SERVEROUTPUT ON;
DECLARE
max_sga NUMBER;
log_buf NUMBER;
shared_pool NUMBER;
java_pool NUMBER;
large_pool NUMBER;
db_cache NUMBER;
zan NUMBER;
result varchar2(20);
BEGIN
select value into max_sga from v$parameter where name in ('sga_max_size');
select value into log_buf from v$parameter where name in ('log_buffer');
select value into shared_pool from v$parameter where name in ('shared_pool_size');
select value into large_pool from v$parameter where name in ('large_pool_size');
select value into java_pool from v$parameter where name in ('java_pool_size');
select value into db_cache from v$parameter where name in ('db_cache_size');
zan := max_sga - log_buf - shared_pool - large_pool - java_pool - db_cache;
result := lpad(to_char(zan, '999,999,999,990'), 16, ' ');
dbms_output.put_line ('sga_max = '||max_sga);
dbms_output.put_line ('log_buffer = '||log_buf);
dbms_output.put_line ('shared_pool = '||shared_pool);
dbms_output.put_line ('large_pool = '||large_pool);
dbms_output.put_line ('db_cache = '||db_cache);
dbms_output.put_line ('result= '||result);
END;
SET SERVEROUTPUT OFF;
#-------------------------------------------------------------------------------------------------------------
SQL> select * from v$sgastat;
fixed_sga 734240
buffer_cache 335544320
log_buffer 3408896
shared pool branch 358104
shared pool errors 528
shared pool enqueue 1610712
shared pool DML lock 854160
shared pool KGK heap 33368
shared pool KQR L PO 615640
shared pool KQR M PO 268264
shared pool KQR S SO 6656
shared pool KQR X PO 301704
shared pool db_files 289104
shared pool sessions 2704000
shared pool sql area 223124960
shared pool 1M buffer 2098176
shared pool KGLS heap 355368
shared pool processes 386400
shared pool parameters 15432
shared pool constraints 249832
shared pool free memory 33704160
shared pool transaction 1807256
shared pool PL/SQL DIANA 1049712
shared pool FileOpenBlock 11813536
shared pool PL/SQL MPCODE 2516592
shared pool library cache 126222736
shared pool miscellaneous 17238792
shared pool MTTR advisory 102240
shared pool PLS non-lib hp 2088
shared pool joxs heap init 4240
shared pool kgl simulator 22971624
shared pool sim memory hea 332568
shared pool table definiti 448
shared pool trigger defini 544
shared pool trigger inform 1096
shared pool trigger source 168
shared pool Checkpoint queue 10490880
shared pool VIRTUAL CIRCUITS 2044880
shared pool dictionary cache 3229952
shared pool qmps connections 424000
shared pool enqueue resources 709912
shared pool sim trace entries 196608
shared pool ktlbk state objects 589600
shared pool KSXR receive buffers 1034000
shared pool LISTEN ADDRESS TABLE 261120
shared pool character set object 180864
shared pool db_block_hash_buckets 1573680
shared pool FileIdentificatonBlock 1791824
shared pool message pool freequeue 940944
shared pool KSXR pending messages que 853952
shared pool event statistics per sess 11176000
shared pool fixed allocation callback 880
large pool free memory 205325992
large pool session heap 46332248
java pool free memory 33554432
容量
set lines 300
set heading OFF
col pf for a25
col vl for a20
pro+--------------------------+-------------+----------+;
show sga ;
pro+--------------------------+-------------+----------+;
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('db_block_buffers');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('db_block_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('buffer_pool_keep');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('buffer_pool_recycle');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('db_cache_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('db_keep_cache_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('db_recycle_cache_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('large_pool_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('log_buffer');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('java_pool_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('shared_pool_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('library');
select name as pf, value as vl from v$parameter where name in ('dispatchers');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('max_dispatchers');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('shared_servers');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('max_shared_servers');
select 'Free_Buffer_Waits', total_waits from v$system_event where event = 'free buffer waits';
--select name ,value ,100*(value/ decode((select sum(value) from v$sysstat where name like 'workarea exec%'),0,null,(select sum(value) fromv$sysstat where name like 'workarea exec%'))) pctfrom v$sysstat where name like 'workarea exec%';