84 lines
2.0 KiB
SQL
84 lines
2.0 KiB
SQL
set linesize 300
|
|
|
|
column host_name format a30 heading "Host Name"
|
|
column mem format 999G999G999 heading "Physical Mem"
|
|
column group_name format a20 heading "Group"
|
|
column cpu_count format 999 heading "Num OS CPUs"
|
|
column db_instance format a40 heading "DB Instance"
|
|
column sga_total format 9G999G999D99 heading "SGA (MB)"
|
|
column pga_total format 9G999G999D99 heading "PGA (MB)"
|
|
|
|
|
|
break on host_name skip page on group_name skip 1 on mem on cpu_count
|
|
|
|
compute sum of sga_total on host_name
|
|
compute sum of pga_total on host_name
|
|
compute sum of sga_total on group_name
|
|
compute sum of pga_total on group_name
|
|
|
|
|
|
with pga_total
|
|
as
|
|
( select
|
|
target_guid,
|
|
to_number(value) pga_total,
|
|
collection_timestamp
|
|
from
|
|
mgmt$metric_current mcur
|
|
where
|
|
metric_name = 'memory_usage_sga_pga'
|
|
and metric_column = 'pga_total'
|
|
and target_type = 'oracle_database'
|
|
),
|
|
sga_total
|
|
as
|
|
( select
|
|
target_guid,
|
|
to_number(value) sga_total,
|
|
collection_timestamp
|
|
from
|
|
mgmt$metric_current mcur
|
|
where
|
|
metric_name = 'memory_usage_sga_pga'
|
|
and metric_column = 'sga_total'
|
|
and target_type = 'oracle_database'
|
|
)
|
|
select
|
|
h.host_name,
|
|
h.mem,
|
|
h.cpu_count,
|
|
nvl(gdm.composite_target_name, 'Unassigned') group_name,
|
|
t.target_name db_instance,
|
|
st.sga_total,
|
|
pt.pga_total
|
|
from
|
|
mgmt$target t
|
|
join pga_total pt
|
|
on ( t.target_guid = pt.target_guid )
|
|
join sga_total st
|
|
on ( t.target_guid = st.target_guid )
|
|
join mgmt$os_hw_summary h
|
|
on ( h.host_name = t.host_name )
|
|
left outer join mgmt$group_derived_memberships gdm
|
|
on ( t.target_guid = gdm.member_target_guid)
|
|
where
|
|
t.target_type = 'oracle_database'
|
|
and ( gdm.composite_target_guid is null
|
|
or gdm.composite_target_guid not in
|
|
( select
|
|
cmp.composite_target_guid
|
|
from
|
|
mgmt$group_derived_memberships cmp
|
|
where
|
|
cmp.member_target_type = 'composite'
|
|
)
|
|
)
|
|
order by
|
|
h.host_name,
|
|
group_name,
|
|
t.target_name
|
|
;
|
|
|
|
clear breaks
|
|
clear computes
|