Database health metrics, Oracle

SELECT 'SYS::' || METRIC_NAME AS METRIC, ROUND(VALUE,3) as VALUE FROM V$SYSMETRIC WHERE GROUP_ID = 2;
SELECT 'SYSPARAM::' || INITCAP(NAME) AS METRIC, to_number(VALUE) FROM V$SYSTEM_PARAMETER WHERE NAME IN ('sessions', 'processes', 'db_files');
SELECT 'SESSION::Long time locked' ,count(*) FROM V$SESSION s WHERE s.BLOCKING_SESSION IS NOT NULL AND s.BLOCKING_SESSION_STATUS='VALID' AND s.SECONDS_IN_WAIT > 600;
SELECT 'SESSION::Lock rate' ,(cnt_block / cnt_all)* 100 pct FROM ( SELECT COUNT(*) cnt_block FROM v$session WHERE blocking_session IS NOT NULL), ( SELECT COUNT(*) cnt_all FROM v$session);
SELECT 'SESSION::Long time locked' ,count(*) FROM V$SESSION s WHERE s.BLOCKING_SESSION IS NOT NULL AND s.BLOCKING_SESSION_STATUS='VALID' AND s.SECONDS_IN_WAIT > 600;
SELECT 'SESSION::Total', COUNT(*) AS VALUE FROM V$SESSION;
SELECT 'SESSION::Concurrency rate', NVL(ROUND(SUM(duty_act.cnt*100 / num_cores.val)), 0) FROM ( SELECT DECODE(session_state, 'ON CPU', 'CPU', wait_class) wait_class, ROUND(COUNT(*)/(60 * 15), 1) cnt FROM v$active_session_history sh WHERE sh.sample_time >= SYSDATE - 15 / 1440 AND DECODE(session_state, 'ON CPU', 'CPU', wait_class) IN('Concurrency') GROUP BY DECODE(session_state, 'ON CPU', 'CPU', wait_class)) duty_act, ( SELECT SUM(value) val FROM v$osstat WHERE stat_name = 'NUM_CPU_CORES') num_cores;
SELECT 'PGA::' || INITCAP(NAME), VALUE FROM V$PGASTAT;
SELECT 'FRA::Space Limit' AS METRIC, space_limit AS VALUE FROM V$RECOVERY_FILE_DEST;
SELECT 'FRA::Space Used', space_used AS VALUE FROM V$RECOVERY_FILE_DEST;
SELECT 'FRA::Space Reclaimable', space_reclaimable AS VALUE FROM V$RECOVERY_FILE_DEST;
SELECT 'FRA::Number Of Files', number_of_files AS VALUE FROM V$RECOVERY_FILE_DEST;
SELECT 'FRA::Usable Pct', DECODE(space_limit, 0, 0,(100-(100 *(space_used-space_reclaimable)/ space_limit))) AS VALUE FROM V$RECOVERY_FILE_DEST;
SELECT 'FRA::Restore Point', COUNT(*) AS VALUE FROM V$RESTORE_POINT;
SELECT 'PROC::Procnum', COUNT(*) FROM v$process;
SELECT 'DATAFILE::Count', COUNT(*) FROM v$datafile;
SELECT 'SGA::' || INITCAP(pool), SUM(bytes) FROM V$SGASTAT WHERE pool IN ( 'java pool', 'large pool' ) GROUP BY pool;
SELECT 'SGA::Shared Pool', SUM(bytes) FROM V$SGASTAT WHERE pool = 'shared pool' AND name NOT IN ('library cache', 'dictionary cache', 'free memory', 'sql area');
SELECT 'SGA::' || INITCAP(name), bytes FROM V$SGASTAT WHERE pool IS NULL AND name IN ('log_buffer', 'fixed_sga');
SELECT 'SGA::Buffer_Cache', SUM(bytes) FROM V$SGASTAT WHERE pool IS NULL AND name IN ('buffer_cache', 'db_block_buffers');
SELECT 'REDO::Available', count(*) from v$log t where t.status in ('INACTIVE', 'UNUSED');
SELECT 'USER::Expire password', ROUND(DECODE(SIGN(NVL(u.expiry_date, SYSDATE + 999) - SYSDATE),-1, 0, NVL(u.expiry_date, SYSDATE + 999) - SYSDATE)) exp_passwd_days_before FROM dba_users u WHERE username = UPPER('zabbix');