Thursday, December 7, 2017

13) # "HIT RATIO THE LASTS 30 DAYS"

13) # "HIT RATIO THE LASTS 30 DAYS"

echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} HIT RATIO THE LASTS 30 DAYS ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col name for a40
col instance_name for a30
exec dbms_application_info.set_action('latches');
SELECT instance_name
   , name
   , case when hit_ratio < 95 then 'Warning' when hit_ratio < 99 then 'Critical' end as hit_ratio
   , sleep_miss
from (select i.instance_name, l.name, round((gets-misses)/decode(gets,0,1,gets),3)*100 hit_ratio, round(sleeps/decode(misses,0,1,misses),3) sleep_miss
from gv\$latch l, gv\$instance i
where l.gets != 0
and l.inst_id = i.inst_id)
where hit_ratio < 100
order by hit_ratio;
quit;
EOF

No comments:

Post a Comment