DBA SGA+PGA Memory Configuration

select
*
from
(
select
x.inst_id,
x.name,
case when x.name not like '%cpu%' then x.value/1024/1024/1024 else x.value end value
from
(
select go.inst_id, 'cpu physical' name, go.value, 10 order_by from gv$osstat go where go.stat_name='NUM_CPUS' union all
select go.inst_id, 'memory physical' name, go.value, 1 order_by from gv$osstat go where go.stat_name='PHYSICAL_MEMORY_BYTES' union all
select go.inst_id, 'memory unused' name, go.value-(select sum(gp.value) from gv$parameter gp where gp.name in ('sga_target','memory_target','pga_aggregate_target')) value, 2 order_by from gv$osstat go where go.stat_name='PHYSICAL_MEMORY_BYTES' union all
select gp.inst_id, gp.name, to_number(gp.value) value,
decode(gp.name,
'cpu_count',11,
'sga_max_size',3,
'sga_target',4,
'pga_aggregate_target',5,
'pga_aggregate_limit',6,
'memory_max_target',7,
'memory_target',8
) order_by
from gv$parameter gp where gp.name in ('cpu_count','pga_aggregate_limit','pga_aggregate_target','sga_max_size','sga_target','memory_max_target','memory_target')
) x
order by
x.inst_id,
x.order_by
)
union all
select
gi.inst_id,
'PGA record date: '||least(gi.startup_time,(select min(dhs.begin_interval_time) from dba_hist_snapshot dhs where vd.dbid=dhs.dbid and gi.instance_number=dhs.instance_number)) name,
null value
from
gv$instance gi,
v$database vd
union all
select
gi.inst_id,
'average PGA' name,
(select avg(dhp.value)/1024/1024/1024 from dba_hist_pgastat dhp where vd.dbid=dhp.dbid and gi.instance_number=dhp.instance_number and dhp.name='total PGA allocated') value
from
gv$instance gi,
v$database vd
union all
select
gi.inst_id,
'maximum PGA' name,
(select max(dhp.value)/1024/1024/1024 from dba_hist_pgastat dhp where vd.dbid=dhp.dbid and gi.instance_number=dhp.instance_number and dhp.name='maximum PGA allocated') value
from
gv$instance gi,
v$database vd
union all
select
to_number(null) inst_id,
case when ass.pname='DSTART' then 'System Stats record date: '||ass.pval2 else ass.pname end name,
to_number(decode(ass.pname,'DSTART',null,ass.pval1)) value
from sys.aux_stats$ ass
where
ass.pname in ('DSTART','CPUSPEEDNW','IOSEEKTIM','IOTFRSPEED','MBRC')

select * from ( select x.inst_id, x.name, case when x.name not like '%cpu%' then x.value/1024/1024/1024 else x.value end value from ( select go.inst_id, 'cpu physical' name, go.value, 10 order_by from gv$osstat go where go.stat_name='NUM_CPUS' union all select go.inst_id, 'memory physical' name, go.value, 1 order_by from gv$osstat go where go.stat_name='PHYSICAL_MEMORY_BYTES' union all select go.inst_id, 'memory unused' name, go.value-(select sum(gp.value) from gv$parameter gp where gp.name in ('sga_target','memory_target','pga_aggregate_target')) value, 2 order_by from gv$osstat go where go.stat_name='PHYSICAL_MEMORY_BYTES' union all select gp.inst_id, gp.name, to_number(gp.value) value, decode(gp.name, 'cpu_count',11, 'sga_max_size',3, 'sga_target',4, 'pga_aggregate_target',5, 'pga_aggregate_limit',6, 'memory_max_target',7, 'memory_target',8 ) order_by from gv$parameter gp where gp.name in ('cpu_count','pga_aggregate_limit','pga_aggregate_target','sga_max_size','sga_target','memory_max_target','memory_target') ) x order by x.inst_id, x.order_by ) union all select gi.inst_id, 'PGA record date: '||least(gi.startup_time,(select min(dhs.begin_interval_time) from dba_hist_snapshot dhs where vd.dbid=dhs.dbid and gi.instance_number=dhs.instance_number)) name, null value from gv$instance gi, v$database vd union all select gi.inst_id, 'average PGA' name, (select avg(dhp.value)/1024/1024/1024 from dba_hist_pgastat dhp where vd.dbid=dhp.dbid and gi.instance_number=dhp.instance_number and dhp.name='total PGA allocated') value from gv$instance gi, v$database vd union all select gi.inst_id, 'maximum PGA' name, (select max(dhp.value)/1024/1024/1024 from dba_hist_pgastat dhp where vd.dbid=dhp.dbid and gi.instance_number=dhp.instance_number and dhp.name='maximum PGA allocated') value from gv$instance gi, v$database vd union all select to_number(null) inst_id, case when ass.pname='DSTART' then 'System Stats record date: '||ass.pval2 else ass.pname end name, to_number(decode(ass.pname,'DSTART',null,ass.pval1)) value from sys.aux_stats$ ass where ass.pname in ('DSTART','CPUSPEEDNW','IOSEEKTIM','IOTFRSPEED','MBRC')