Oracle日常监控（二）

1.parse_exec_ratio(Execute to Parse %使用率占比(解析执行比))

select  to_char((1-(a.value/b.value))*100,'999.99') as  parse_exec_ratio
from v$sysstat a,v$sysstat b where a.name='parse count (total)' and b.name='execute count';

SQL> select  to_char((1-(a.value/b.value))*100,'999.99') as  parse_exec_ratio
from v$sysstat a,v$sysstat b where a.name='parse count (total)' and b.name='execute count';  2  

PARSE_EXEC_RATIO
---------------------
  90.97

2.cursor_per_session(PGA平均会话游标数量)

select round((a.VALUE/b.VALUE)) as cur_per_session from 
v$sysstat a,v$sysstat b
where a.NAME='opened cursors current'
and b.NAME='logons current';

SQL> select round((a.VALUE/b.VALUE)) as cur_per_session from 
v$sysstat a,v$sysstat b
where a.NAME='opened cursors current'
and b.NAME='logons current';  2    3    4  

CUR_PER_SESSION
---------------
              1

3.session_cached_cursor_ratio

select
  'session_cached_cursors'  parameter,
  lpad(value, 5)  value,
  decode(value, 0, '  n/a', to_char(100 * used / value, '990') )  usage
from
  ( select
      max(s.value)  used
    from
      sys.v_$statname  n,
      sys.v_$sesstat  s
    where
      n.name = 'session cursor cache count' and
      s.statistic# = n.statistic#
  ),
  ( select
      value
    from
      sys.v_$parameter
    where
      name = 'session_cached_cursors'
  )
union all
select
  'open_cursors',
  lpad(value, 5),
  to_char(100 * used / value,  '990') 
from
  ( select
      max(sum(s.value))  used
    from
      sys.v_$statname  n,
      sys.v_$sesstat  s
    where
      n.name in ('opened cursors current', 'session cursor cache count') and
      s.statistic# = n.statistic#
    group by
      s.sid
  ),
  ( select
      value
    from
      sys.v_$parameter
    where
      name = 'open_cursors'
  )
/

PARAMETER                                VALUE                                                        USAGE
---------------------------------------- ------------------------------------------------------------ ---------------
session_cached_cursors                      50                                                          98
open_cursors                               300                                                          22

4.rollback_transaction_ratio(事务回滚率)

select a.VALUE,b.value,to_char(100*(a.VALUE/b.value),'fm990.90') rollback_ratio 
from v$sysstat a,v$sysstat b
where a.NAME='user rollbacks' 
and b.name='user commits';

SQL> select a.VALUE,b.value,to_char(100*(a.VALUE/b.value),'fm990.90') rollback_ratio 
from v$sysstat a,v$sysstat b
where a.NAME='user rollbacks' 
and b.name='user commits';
  2    3    4  
     VALUE      VALUE ROLLBACK_RATIO
---------- ---------- ---------------------
         2        150 1.33

5.undo_seg_get_ratio

select to_char((1-sum(waits)/sum(gets))*100,'999.99') as  undo_seg_get_ratio
from v$rollstat a, v$rollname b
where a.usn = b.usn;

SQL> select to_char((1-sum(waits)/sum(gets))*100,'999.99') as  undo_seg_get_ratio
from v$rollstat a, v$rollname b
where a.usn = b.usn;  2    3  

UNDO_SEG_GET_RATIO
---------------------
 100.00

6.redo_size_gen_rate

select snap_id,snap_time,round(value_offset/es_time,2) as redo_per_second_bytes
from(
select snap_id,snap_time,value,lag_value,value-lag_value value_offset,END_INTERVAL_TIME,lag_time,
EXTRACT(DAY FROM
                   END_INTERVAL_TIME - lag_time) *
           86400 + EXTRACT(HOUR FROM END_INTERVAL_TIME -
                           lag_time) * 3600 +
           EXTRACT(MINUTE FROM
                   END_INTERVAL_TIME - lag_time) * 60 +
           EXTRACT(SECOND FROM
                   END_INTERVAL_TIME - lag_time) as es_time

 from
(
select snap_id,snap_time ,stat_name,value,lag(value,1,null) over(order by value)  lag_value,
END_INTERVAL_TIME,    
lag(END_INTERVAL_TIME,1,null) over(order by END_INTERVAL_TIME) lag_time
from(
select h.snap_id,h.stat_name,h.value,s.END_INTERVAL_TIME,
to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') as snap_time
from dba_hist_sysstat h,dba_hist_snapshot s,v$instance i
where h.stat_name='redo size'
and h.snap_id=s.snap_id
and to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')>=i.STARTUP_TIME
and trunc(to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd'),'yyyy-mm-dd'))>=trunc(sysdate)-2
)order by snap_id desc
)
)

   SNAP_ID SNAP_TIME                                          REDO_PER_SECOND_BYTES
---------- -------------------------------------------------- ---------------------
       770 2024/6/27 21:37:14                                             261690.75
       769 2024/6/27 21:37:10