Oracle -----redo---- 相关查询

--查询归档模式
  select name ,open_mode,log_mode from v$database;
  archive log list 

---检查日志切换频率
  select sequence#,
         to_char(first_time, 'yyyymmdd_hh24:mi:ss') firsttime,
         round((first_time - lag(first_time) over(order by first_time)) * 24 * 60,2) minutes
    from v$log_history
  where 1=1
  -- and first_time > sysdate - 1
  order by first_time, minutes;

---检查lgwr i/o性能 (time_waited/total_waits：表示平均lgwr写入完成时间 若>1(百分之一秒)表示写入过慢)
  select total_waits,
         time_waited,
         average_wait,
         time_waited / total_waits as avg_time
    from v$system_event
  where event = 'log file parallel write';

---检查与redo相关性能指标
    select name,value from v$sysstat where name like '%redo%';
  
---查询redo block size
     select max(lebsz) from x$kccle;
   
---查看redo allocation latch

  col name for a30
  select name, gets, misses, misses / gets
    from v$latch
  where name = 'redo allocation';

  col name for a30
  select name, gets, misses, misses / gets
    from v$latch_children
  where name = 'redo allocation';

---查看与redo相关等待事件 
  col event format a40
  select event,total_waits,time_waited ,total_timeouts,average_wait
     from v$system_event
    where upper(event) like'%REDO%';

---查看user commit次数
        select to_number(value,99999999999) from v$sysstat where name='user commits';

---查看系统运行时间
  select (sysdate - startup_time)*24*60*60 as seconds from v$instance；

---计算出每秒用户提交次数
  select  user_commit次数/系统运行时间  from dual;

---计算出每个事务平均处理多少个redo block
  select a.redoblocks / b.trancount
    from (select value redoblocks
            from v$sysstat
           where name = 'redo blocks written') a,
         (select value trancount from v$sysstat where name = 'user commits') b；