2012-11-12

how to monitor OS usage in RAC


A query the authors find useful to monitor OS resources usage in oracle and especially in RAC via v$views  ( from storage testing rac 11g ukoug lc dw )

select to_char(min(begin_time),'hh24:mi:ss')||' /'||round(avg(intsize_csec/100),0)||'s' "Time+Delta",
      metric_name||' - '||metric_unit "Metric",
      sum(value_inst1) inst1, sum(value_inst2) inst2, sum(value_inst3) inst3, sum(value_inst4) inst4,
      sum(value_inst5) inst5, sum(value_inst6) inst6
from
 ( select begin_time,intsize_csec,metric_name,metric_unit,metric_id,group_id,
      case inst_id when 1 then round(value,1) end value_inst1,
      case inst_id when 2 then round(value,1) end value_inst2,
      case inst_id when 3 then round(value,1) end value_inst3,
      case inst_id when 4 then round(value,1) end value_inst4,
      case inst_id when 5 then round(value,1) end value_inst5,
      case inst_id when 6 then round(value,1) end value_inst6
 from gv$sysmetric
 where metric_name in ('Host CPU Utilization (%)','Current OS Load', 'Physical Write Total IO Requests Per Sec',
       'Physical Write Total Bytes Per Sec', 'Global Cache Average Current Get Time', 'Global Cache Average CR Get Time',
       'Physical Read Total Bytes Per Sec', 'Physical Read Total IO Requests Per Sec',
       'CPU Usage Per Sec','Network Traffic Volume Per Sec','Logons Per Sec','Redo Generated Per Sec',
       'User Transaction Per Sec','Database CPU Time Ratio','Database Wait Time Ratio','Database Time Per Sec')
 )
group by metric_id,group_id,metric_name,metric_unit
order by metric_name;

Niciun comentariu:

Trimiteți un comentariu