Current Metrics and Load



col event for  a38
    select
       n.name event,
       m.wait_count  cnt,
       round(((10*m.time_waited)/60),2) total_ms_per_sec,
       nvl(round(10*m.time_waited/nullif(m.wait_count,0),3) ,0) avg_ms
     from v$eventmetric m,
          v$event_name n
     where m.event_id=n.event_id
       and m.wait_count > 0
       and wait_class_id != 2723168908
union all
select
         'cpu_per_second'  event,
          null cmt,
          round((value*10),2) total_ms_per_sec,
          null avg_ms
     from     v$sysmetric
     where    metric_name in ( 'CPU Usage Per Sec')
       and group_id=2
order by 3
/

select s.sid,
       s. process,
       s.sql_id,
      tmp.segtype,
     ((tmp.blocks*8)/1024)  MB,
      tmp.tablespace
from
      v$tempseg_usage tmp,
      v$session s
where tmp.session_num=s.serial#
    and segtype in ('HASH','SORT')
order by blocks desc
/


select
         round((sum(decode(metric_name, 'Physical Read Bytes Per Sec' , value,0)))/1024,0)  rkbps,
         round((sum(decode(metric_name, 'Physical Read Total Bytes Per Sec' , value,0)))/1024,0) rtkbps,
         round((sum(decode(metric_name, 'Physical Read Total IO Requests Per Sec' , value,0 ))),2) rtops,
         round((sum(decode(metric_name, 'Physical Write Bytes Per Sec' , value,0 )))/1024,0)  wbps,
         round((sum(decode(metric_name, 'Physical Write Total Bytes Per Sec' , value,0 )))/1024,0) wtbps,
         round((sum(decode(metric_name, 'Physical Write Total IO Requests Per Sec', value,0 ))),2) wtops
     from     v$sysmetric
     where    metric_name in (
                    'Physical Read Total Bytes Per Sec' ,
                    'Physical Read Bytes Per Sec' ,
                    'Physical Write Bytes Per Sec' ,
                    'Physical Write Total Bytes Per Sec' ,
                    'Physical Write Total IO Requests Per Sec',
                    'Physical Read Total IO Requests Per Sec'
                    )
       and group_id=2
/

select sid, event, WAIT_TIME ,p1,p2,p3 from v$session where status='ACTIVE'
/







Comments