WITH

target_list AS
(SELECT target_name oem_target_name,
decode(instr(property_value,'.'),0,property_value,substr(property_value,1,instr(pro
perty_value,'.')-1)) host
FROM sysman.mgmt$target_properties where property_name='MachineName' and
target_type in ('oracle_database','oracle_pdb')
and target_name in
('catdb.manash.hom',
'orcl1_linux1.manash.hom',
'orcl2_linux2.manash.hom'
)
)
--select * from target_list
,tbs_usage_pc
AS (SELECT entity_name,
COLLECTION_TIME,
metric_column_label,
METRIC_KEY_VALUE,
VALUE,
ROW_NUMBER ()
OVER (
PARTITION BY entity_name, METRIC_KEY_VALUE
ORDER BY
entity_name,
METRIC_KEY_VALUE,
COLLECTION_TIME DESC)
rn
FROM (SELECT entity_name,
collection_time,
metric_column_label,
entity_type,
METRIC_KEY_VALUE,
VALUE
FROM sysman.gc_metric_values,
target_list
WHERE entity_name = OEM_TARGET_NAME
AND entity_type IN
('oracle_database',
'oracle_pdb')
AND metric_column_label =
'Tablespace Space Used (%)'
AND TRUNC (collection_time) =
TRUNC (SYSDATE)))
--select * from tbs_usage_pc
,dfs_usage_pc
AS (SELECT
t.host,
collection_time,
metric_column_label,
entity_type,
METRIC_KEY_VALUE,
VALUE
FROM sysman.gc_metric_values gmv,target_list t
WHERE
decode(instr(gmv.entity_name,'.'),0,gmv.entity_name,substr(gmv.entity_name,1,instr(
gmv.entity_name,'.')-1))=t.host
and entity_type IN ('host')
AND metric_column_label =
 'Filesystem Space Available (%)'
AND TRUNC (collection_time) =
TRUNC (SYSDATE))
--select * from dfs_usage_pc
,dfs_avail_mb
AS (SELECT
t.host,
collection_time,
metric_column_label,
entity_type,
METRIC_KEY_VALUE,
VALUE
FROM sysman.gc_metric_values gmv,target_list t
WHERE
decode(instr(gmv.entity_name,'.'),0,gmv.entity_name,substr(gmv.entity_name,1,instr(
gmv.entity_name,'.')-1))=t.host
and entity_type IN ('host')
AND metric_column_label =
'Filesystem Space Available (MB)'
AND TRUNC (collection_time) =
TRUNC (SYSDATE))
--select * from dfs_avail_mb
,wait_ev_sec
AS (SELECT
entity_name,
collection_time,
metric_column_label,
entity_type,
METRIC_KEY_VALUE,
VALUE
FROM sysman.gc_metric_values
WHERE entity_type IN
('oracle_database',
'oracle_pdb')
AND metric_column_label =
'Total Foreground Wait Time (second)'
AND TRUNC (collection_time) =
TRUNC (SYSDATE))
--select * from wait_ev_sec
,cpu_pc
AS ( SELECT t.host,
metric_column_label,
ROUND (MIN (VALUE), 1) CPU_Min_PC,
ROUND (MAX (VALUE), 1) CPU_Max_PC,
ROUND (AVG (VALUE), 1) CPU_Avg_PC
FROM sysman.gc_metric_values gmv, target_list t
WHERE
decode(instr(gmv.entity_name,'.'),0,gmv.entity_name,substr(gmv.entity_name,1,instr(
gmv.entity_name,'.')-1))=t.host
and entity_type = 'host'
AND metric_column_label IN
('CPU Utilization (%)')
AND TRUNC (collection_time) = TRUNC (SYSDATE)
GROUP BY host, metric_column_label
ORDER BY host, metric_column_label)
--select * from cpu_pc
,swap_pc
AS ( SELECT t.host,
metric_column_label,
 ROUND (MIN (VALUE), 1) SWAP_Min_PC,
ROUND (MAX (VALUE), 1) SWAP_Max_PC,
ROUND (AVG (VALUE), 1) SWAP_Avg_PC
FROM sysman.gc_metric_values gmv, target_list t
WHERE
decode(instr(gmv.entity_name,'.'),0,gmv.entity_name,substr(gmv.entity_name,1,instr(
gmv.entity_name,'.')-1))=t.host
and entity_type = 'host'
AND metric_column_label IN
('Swap Utilization (%)')
AND TRUNC (collection_time) = TRUNC (SYSDATE)
GROUP BY host, metric_column_label
ORDER BY host, metric_column_label)
--select * from swap_pc
,iops_pc
AS ( SELECT t.host,
metric_column_label,
ROUND (MIN (VALUE), 1) IOPS_Min_PC,
ROUND (MAX (VALUE), 1) IOPS_Max_PC,
ROUND (AVG (VALUE), 1) IOPS_Avg_PC
FROM sysman.gc_metric_values gmv, target_list t
WHERE
decode(instr(gmv.entity_name,'.'),0,gmv.entity_name,substr(gmv.entity_name,1,instr(
gmv.entity_name,'.')-1))=t.host
and entity_type = 'host'
AND metric_column_label IN
('Total Disk I/O made across all disks (per
second)')
AND TRUNC (collection_time) = TRUNC (SYSDATE)
GROUP BY host, metric_column_label
ORDER BY host, metric_column_label)
--select * from iops_pc
,tbs_thresholds
AS (SELECT DISTINCT *
FROM (SELECT mt.target_name,
mt.target_type,
mmt.coll_name,
mmt.warning_threshold,
mmt.critical_threshold,
mmt.key_value
FROM sysman.mgmt_targets mt,
sysman.
mgmt_metric_thresholds mmt
WHERE mt.target_guid =
mmt.target_guid
AND mt.target_type IN
('oracle_database',
'oracle_pdb')
AND mmt.coll_name LIKE
'problemTbsp%')
WHERE TRIM (warning_threshold) IS NOT NULL
AND TRIM (critical_threshold) IS NOT NULL)
--select * from tbs_thresholds
,tbs_max_pc
AS ( SELECT entity_name,
ROUND (MAX (VALUE), 1) TBS_MAX_PC
FROM (SELECT NVL (entity_name, OEM_TARGET_NAME)
entity_name,
COLLECTION_TIME,
 metric_column_label,
METRIC_KEY_VALUE,
VALUE
FROM (SELECT entity_name,
COLLECTION_TIME,
metric_column_label,
METRIC_KEY_VALUE,
VALUE,
ROW_NUMBER ()
OVER (
PARTITION BY entity_name,
METRIC_KEY_VALUE
ORDER BY
entity_name,
METRIC_KEY_VALUE,
COLLECTION_TIME DESC)
rn
FROM (SELECT entity_name,
collection_time,

metric_column_label,
entity_type,
METRIC_KEY_VALUE,
VALUE
FROM sysman.
gc_metric_values
WHERE entity_type IN

('oracle_database',
'oracle_pdb')
AND
metric_column_label =
'Tablespace
Space Used (%)'
AND TRUNC (

collection_time) =
TRUNC (

SYSDATE))) tbs_usage_pc,
target_list
WHERE target_list.OEM_TARGET_NAME =
tbs_usage_pc.entity_name(+)
AND RN(+) = 1)
GROUP BY entity_name, entity_name)
--select * from tbs_max_pc
,tbs_usage_lvl
AS ( SELECT
entity_name,
MAX (
CASE
WHEN tbs_max_pc >= critical_threshold
THEN
2
WHEN tbs_max_pc >= warning_threshold
THEN
1
ELSE
0 /*Normal*/
 END)
usage_level
FROM ( SELECT entity_name,
metric_key_value,
tbs_max_pc,
MAX (warning_threshold)
warning_threshold,
MAX (critical_threshold)
critical_threshold
FROM ( SELECT entity_name,
metric_key_value,
ROUND (MAX (VALUE), 1)
tbs_max_pc,
warning_threshold,
critical_threshold
FROM (SELECT
entity_name,
COLLECTION_TIME,
metric_column_label,
METRIC_KEY_VALUE,
VALUE,
warning_threshold,
critical_threshold,
ROW_NUMBER ()
OVER (
PARTITION BY
entity_name,

METRIC_KEY_VALUE
ORDER BY
entity_name,
METRIC_KEY_VALUE,
COLLECTION_TIME
DESC)
rn
FROM tbs_usage_pc,
target_list,
tbs_thresholds
WHERE
target_list.OEM_TARGET_NAME =
tbs_thresholds.
target_name(+)
AND METRIC_KEY_VALUE =
tbs_thresholds.
key_value
AND target_list.
OEM_TARGET_NAME =

tbs_usage_pc.entity_name(+))
WHERE rn(+) = 1
GROUP BY
entity_name,
METRIC_KEY_VALUE,
warning_threshold,
critical_threshold
UNION
SELECT
entity_name,
METRIC_KEY_VALUE,
 ROUND (MAX (VALUE), 1)
TBS_MAX_PC,
warning_threshold,
critical_threshold
FROM (SELECT
entity_name,
COLLECTION_TIME,
metric_column_label,
METRIC_KEY_VALUE,
VALUE,
warning_threshold,
critical_threshold,
ROW_NUMBER ()
OVER (
PARTITION BY
entity_name,

METRIC_KEY_VALUE
ORDER BY
entity_name,
METRIC_KEY_VALUE,
COLLECTION_TIME
DESC)
rn
FROM tbs_usage_pc,
target_list,
tbs_thresholds
WHERE
target_list.OEM_TARGET_NAME =
tbs_thresholds.
target_name(+)
AND (key_value IS NULL
OR key_value = '
')
AND target_list.
OEM_TARGET_NAME =

tbs_usage_pc.entity_name(+))
WHERE rn(+) = 1
GROUP BY
entity_name,
metric_key_value,
warning_threshold,
critical_threshold)
GROUP BY
entity_name,
metric_key_value,
tbs_max_pc)
GROUP BY entity_name)
--select * from tbs_usage_lvl
,dfs_max_pc
AS ( SELECT target_name,
MAX (data_fs_max_pc) data_fs_max_pc
FROM (SELECT
df.host,
NVL (target_name, OEM_TARGET_NAME)
target_name,
data_fs_max_pc
FROM ( SELECT
 host,
target_name,
ROUND (MAX (VALUE), 1)
DATA_FS_MAX_PC
FROM (SELECT *
FROM (SELECT
host,
target_name,
collection_time,

metric_column_label,
metric_key_value,
(100 - VALUE)
VALUE,
ROW_NUMBER ()
OVER (
PARTITION BY
host,

metric_key_value
ORDER BY
host,

metric_key_value,

collection_time DESC)
rn
FROM dfs_usage_pc m,
(SELECT
host_name,

target_name,

file_name,
DECODE (
SUBSTR
(

file_name,
1,
1),
'/',
os_storage_entity,
SUBSTR
(

file_name,
1,
3))

os_storage_entity
FROM sysman.

mgmt$db_datafiles
WHERE
target_type IN

('oracle_database',
'oracle_pdb')) d
WHERE UPPER (
m.host) =
UPPER (
d.
host_name)
AND
metric_key_value <>
'/'
AND (d.

os_storage_entity =

metric_key_value

))
WHERE RN = 1)
GROUP BY host, target_name) df,
target_list
WHERE target_list.OEM_TARGET_NAME =
df.target_name(+)
UNION
SELECT df.HOST,
NVL (target_name, OEM_TARGET_NAME)
target_name,
data_fs_max_pc
FROM ( SELECT
host,
target_name,
ROUND (MAX (VALUE), 1)
DATA_FS_MAX_PC
FROM (SELECT *
FROM (SELECT
host,
target_name,
collection_time,

metric_column_label,
metric_key_value,
(100 - VALUE)
VALUE,
ROW_NUMBER ()
OVER (
PARTITION BY
host,

metric_key_value
ORDER BY
host,

metric_key_value,

collection_time DESC)
rn
FROM dfs_usage_pc m,
(SELECT
decode(instr(host_name,'.'),0,host_name,substr(host_name,1,instr(host_name,'.')-1))
host_name,
target_name,

file_name,
DECODE (
SUBSTR
(

file_name,
1,
1),
'/',
os_storage_entity,
SUBSTR
(

file_name,
1,
3))

os_storage_entity
FROM sysman.

mgmt$db_datafiles
WHERE
target_type IN

('oracle_database',

'oracle_pdb')) d
WHERE UPPER (
m.
host) =
UPPER (
d.
host_name)
AND
metric_key_value <>
'/'
AND d.

os_storage_entity =

metric_key_value)
WHERE RN = 1)
GROUP BY host, target_name) df,
target_list
WHERE target_list.OEM_TARGET_NAME =
df.target_name(+))
GROUP BY target_name)
--select * from dfs_max_pc
,afs_usage_pc
AS ( SELECT target_name,
MAX (arch_fra_fs_max_pc) arch_fra_fs_max_pc
FROM (SELECT NVL (target_name, OEM_TARGET_NAME)
target_name,
arch_fra_fs_max_pc
FROM (SELECT target_name,
arc_file_system,
ROUND (VALUE, 1)
 ARCH_FRA_FS_MAX_PC
FROM (SELECT target_name,
metric_key_value
arc_file_system,
VALUE,
ROW_NUMBER ()
OVER (
PARTITION BY target_name
ORDER BY
LENGTH (
metric_key_value)
DESC)
rn
FROM ( SELECT
target_name,

metric_column_label,
metric_key_value,
VALUE
FROM (SELECT *
FROM (SELECT
a.target_name,

collection_time,

metric_column_label,

metric_key_value,

(100

- VALUE)

VALUE,

ROW_NUMBER ()

OVER (

PARTITION BY host,

metric_key_value

ORDER BY

collection_time DESC)

rn
FROM
dfs_usage_pc p,

(SELECT host_name,

target_name,

DECODE (

INSTR (
arc_file_system,

'$ORACLE_SID'),

0, arc_file_system,

REPLACE (

arc_file_system,

'$ORACLE_SID',

SUBSTR (

target_name,

1,

INSTR (

target_name,

'_')

- 1)))

arc_file_system

FROM (SELECT
decode(instr(host_name,'.'),0,host_name,substr(host_name,1,instr(host_name,'.')-1))
host_name,

target_name,

SUBSTR (

VALUE,

DECODE (

INSTR (

UPPER (

VALUE),

'LOCATION=',

1),

0, 1,

10))

arc_file_system

FROM sysman.

mgmt$db_init_params
WHERE target_type IN

('oracle_database',

'oracle_pdb')

AND ( (name LIKE

'log_archive_dest%'

AND name NOT LIKE

'log_archive_dest_state%')

OR name =

'db_recovery_file_dest')

AND (VALUE

IS NOT NULL

OR LENGTH (

VALUE) >

0)

AND UPPER (

VALUE) NOT LIKE

'SERVICE=%'

AND UPPER (

VALUE) NOT LIKE

'%USE_DB_RECOVERY_FILE_DEST%')) a
WHERE
UPPER (

p.host) =

UPPER (

a.

host_name)

AND metric_key_value <>

'/'

AND INSTR (

arc_file_system,
metric_key_value,

1) >

0)
WHERE RN = 1)
ORDER BY LENGTH (

metric_key_value) DESC))
WHERE RN = 1) af,
target_list
WHERE target_list.OEM_TARGET_NAME =
af.target_name(+)
UNION
SELECT NVL (target_name, OEM_TARGET_NAME)
target_name,
arch_fra_fs_max_pc
FROM (SELECT target_name,
arc_file_system,
ROUND (VALUE, 1)
ARCH_FRA_FS_MAX_PC
FROM (SELECT target_name,
metric_key_value
arc_file_system,
VALUE,
ROW_NUMBER ()
OVER (
PARTITION BY target_name
ORDER BY
LENGTH (
metric_key_value)
DESC)
rn
FROM ( SELECT target_name,

metric_column_label,
metric_key_value,
VALUE
FROM (SELECT *
FROM (SELECT
a.target_name,

collection_time,

metric_column_label,

metric_key_value,

(100

- VALUE)

VALUE,

ROW_NUMBER ()

OVER (

PARTITION BY metric_key_value
ORDER BY

collection_time DESC)

rn
FROM
dfs_usage_pc p,

(SELECT host_name,

target_name,

DECODE (

INSTR (

arc_file_system,

'$ORACLE_SID'),

0, arc_file_system,

REPLACE (

arc_file_system,

'$ORACLE_SID',

SUBSTR (

target_name,

1,

INSTR (

target_name,

'_')

- 1)))

arc_file_system

FROM (SELECT
decode(instr(host_name,'.'),0,host_name,substr(host_name,1,instr(host_name,'.')-1))
host_name,

target_name,

SUBSTR (

VALUE,

DECODE (

INSTR (
UPPER (

VALUE),

'LOCATION=',

1),

0, 1,

10))

arc_file_system

FROM sysman.

mgmt$db_init_params

WHERE target_type IN

('oracle_database',

'oracle_pdb')

AND ( (name LIKE

'log_archive_dest%'

AND name NOT LIKE

'log_archive_dest_state%')

OR name =

'db_recovery_file_dest')

AND (VALUE

IS NOT NULL

OR LENGTH (

VALUE) >

0)

AND UPPER (

VALUE) NOT LIKE

'SERVICE=%'

AND UPPER (

VALUE) NOT LIKE

'%USE_DB_RECOVERY_FILE_DEST%')) a
WHERE
UPPER (
p.host) =

UPPER (

a.host_name)

AND metric_key_value <>

'/'

AND INSTR (

arc_file_system,

metric_key_value,

1) >

0)
WHERE RN = 1)
ORDER BY LENGTH (

metric_key_value) DESC))
WHERE RN = 1) af,
target_list
WHERE target_list.OEM_TARGET_NAME =
af.target_name(+)
)
GROUP BY target_name
)
--select * from afs_usage_pc
,db_wait_class
AS (SELECT top_wait_event.target target_name,
top_wait_event.metric_key_value
|| ' ('
|| top_wait_class.metric_key_value
|| ')'
top_db_wait
FROM (SELECT *
FROM (SELECT target,
metric_key_value,
Avg_PC,
ROW_NUMBER ()
OVER (PARTITION BY target
ORDER BY AVG_PC DESC)
rn
FROM ( SELECT
entity_name target,
metric_key_value,
ROUND (MIN (VALUE), 1)
Min_PC,
ROUND (MAX (VALUE), 1)
Max_PC,
ROUND (AVG (VALUE), 1)
AVG_PC
FROM wait_ev_sec, target_list
WHERE target_list.OEM_TARGET_NAME
=
 wait_ev_sec.entity_name
GROUP BY
entity_name,
metric_key_value))
WHERE rn = 1) top_wait_event,
(SELECT *
FROM (SELECT target,
metric_key_value,
AVG_PC,
ROW_NUMBER ()
OVER (
PARTITION BY target
ORDER BY
AVG_PC DESC)
rn
FROM ( SELECT entity_name
target,
metric_column_label,
METRIC_KEY_VALUE,
ROUND (
MIN (
VALUE),
1)
Min_PC,
ROUND (
MAX (
VALUE),
1)
Max_PC,
ROUND (
AVG (
VALUE),
1)
AVG_PC
FROM sysman.
gc_metric_values
tbs_usage_pc,
target_list
WHERE target_list.
OEM_TARGET_NAME =
tbs_usage_pc.
entity_name
AND entity_type IN
('oracle_database',
'oracle_pdb')
AND metric_column_label IN
('Database Time
Spent Waiting (%)')
AND TRUNC (
collection_time) =
TRUNC (
SYSDATE)
GROUP BY entity_name,
metric_column_label,
METRIC_KEY_VALUE
ORDER BY entity_name,
AVG_PC DESC,
METRIC_KEY_VALUE))
WHERE rn = 1) top_wait_class
 WHERE top_wait_class.target = top_wait_event.target)
--select * from db_wait_class
,stby_lag
AS (
select
target_name
, MAX( case when column_label = 'Apply Lag (seconds)' then
to_number(value) end ) as apply_lag_sec
, MAX( case when column_label = 'Transport Lag (seconds)' then
to_number(value) end ) as transport_lag_sec
--, collection_timestamp
from sysman.mgmt$metric_current,
target_list
WHERE target_name = OEM_TARGET_NAME
and metric_name like '%dataguard%'
and metric_label = 'Data Guard Performance'
and column_label in ('Apply Lag (seconds)', 'Transport Lag (seconds)' )
group by target_name--, collection_timestamp
)
--select * from stby_lag
SELECT oem_target_name,
LOWER (target_list.HOST) HOST,
CPU_Max_PC,
CPU_Avg_PC,
SWAP_Max_PC,
SWAP_Avg_PC,
IOPS_Max_PC,
IOPS_Avg_PC,
TBS_MAX_PC,
USAGE_LEVEL,
DATA_FS_MAX_PC,
ARCH_FRA_FS_MAX_PC,
APPLY_LAG_SEC,
TRANSPORT_LAG_SEC,
TOP_DB_WAIT
FROM target_list,
cpu_pc,
swap_pc,
iops_pc,
tbs_max_pc,
tbs_usage_lvl,
dfs_max_pc,
afs_usage_pc,
db_wait_class,
stby_lag
WHERE target_list.HOST = cpu_pc.host
AND target_list.HOST = swap_pc.host
AND target_list.HOST = iops_pc.host
AND target_list.OEM_TARGET_NAME = tbs_max_pc.entity_name(+)
AND target_list.OEM_TARGET_NAME = tbs_usage_lvl.entity_name(+)
AND target_list.OEM_TARGET_NAME = dfs_max_pc.target_name(+)
AND target_list.OEM_TARGET_NAME = afs_usage_pc.target_name(+)
AND target_list.OEM_TARGET_NAME = db_wait_class.target_name(+)
AND target_list.OEM_TARGET_NAME = stby_lag.target_name(+)
ORDER BY 1