关于Oracle数据库IO等待相关指标的较为系统的分析方法



我们在IO问题发生时间段：
1.可以查dba_hist_system_event 来看该IO相关等待时间的平均响应时间的变化趋势，
2.直接查dba_hist_event_histogram或者sys.WRH$_EVENT_HISTOGRAM ，来看该IO相关等待事件的等待时间直方图分布。
3.我们也可以从整体数据库的IO变化量上（dba_hist_sysstat），看出问题时段的总体IO量，比正常时候的IO量到底是否有增加。
4.我们也可以从某个IO function 即某个进程上面（dba_hist_iostat_function），看问题时间段某个进程的IO request次数、IO 的量。
比如说LGWR进程的写request次数、写的量。

说明：
1.如上视图均可关联dba_hist_snapshot
2.所有dba_hist 相关视图都是由对应的v$ 动态性能视图汇聚而来， 当时的数据可以查对应的v$视图。
后附sql。
--- 查等待事件的等待时间直方图分布：
select b.end_interval_time,a.*
from
(select snap_id,
       decode(wait_time_milli,
              1,
              '<1ms',
              2,
              '<2ms',
              4,
              '<4ms',
              8,
             '<8ms',
             16,
             '<16ms',
             32,
             '<32ms',
             64,
             '<64ms',
             128,
             '<128ms',
             256,
             '<256ms',
             512,
             '<512ms',
             1024,
             '<1s',
             2048,
             '<2s',
             4096,
             '<4s',
             8192,
             '>=4s',
             wait_time_milli) time_histg,
      total_wait,
      round(delta_wait_count / total_wait * 100, 2) as per_of_waits
from (select snap_id,
              wait_time_milli,
              delta_wait_count,
              sum(delta_wait_count) over(partition by snap_id order by snap_id) as total_wait
         from (select xx.snap_id,
                      xx.wait_time_milli,
                      xx.wait_count - yy.wait_count as delta_wait_count
                 from dba_hist_event_histogram xx,
                      dba_hist_event_histogram yy
                where xx.event_name = 'log file parallel write'
                  and xx.event_id = yy.event_id
                  and xx.snap_id = yy.snap_id + 1
                  and xx.instance_number = yy.instance_number
                  and xx.wait_time_milli = yy.wait_time_milli
                  and xx.snap_id in (20540)))
order by snap_id, wait_time_milli) a , dba_hist_snapshot b
where a.snap_id=b.snap_id;

--- 查等待事件的平均等待时间的变化趋势：
with t as (
  select s.snap_id, s.instance_number, s.end_interval_time ,
         total_waits - lag(total_waits, 1) over (partition by s.instance_number order by s.snap_id) waits,
         (time_waited_micro - lag(time_waited_micro, 1) over (partition by s.instance_number order by s.snap_id))/1000 twt
    from dba_hist_system_event ev, dba_hist_snapshot s
   where ev.instance_number = s.instance_number and ev.snap_id = s.snap_id
     and event_name = 'log file parallel write'
     and s.end_interval_time BETWEEN TO_DATE('20140401 10:00:00', 'yyyymmdd hh24:mi:ss') AND  TO_DATE('20140414 11:00:00', 'yyyymmdd hh24:mi:ss') )
select to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number, sum(waits), sum(twt), round(sum(twt)/sum(waits), 2) wt
from t
where (to_char(end_interval_time, 'hh24') between 9 and 12 or to_char(end_interval_time, 'hh24') between 14 and 17)
group by to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number
order by 1, instance_number;

--- 查整库IO相关指标：

--get_stat_10g.sql
with s as (
select sn.instance_number, sn.snap_id, sn.end_interval_time stime, (s.value - lag(s.value, 1) over (partition by sn.instance_number order by sn.snap_id)) value
--(sn.end_interval_time - lag(sn.end_interval_time, 1) over (order by sn.snap_id))*24*60*60 elap_time,
--round((s.value - lag(s.value, 1) over (order by sn.snap_id))/((sn.end_interval_time - lag(sn.end_interval_time, 1) over (order by sn.snap_id))*24*60*60)) value_s
from dba_hist_snapshot sn, dba_hist_sysstat s
where s.snap_id = sn.snap_id and s.stat_name = '&1' and s.instance_number = sn.instance_number
)
select instance_number , snap_id, stime, value ,
rank() over (partition by instance_number order by value desc) ranking, min(value) over (partition by instance_number) min_phy,
max(value) over (partition by instance_number) max_phy, round(avg(value) over (partition by instance_number)) avg_phy
from s
where (to_char(stime, 'hh24') between 9 and 12 or to_char(stime, 'hh24') between 14 and 17)
and to_char(stime, 'D') not in ('1', '7')
and stime between to_date(&2, 'yyyymmdd') and to_date(&3, 'yyyymmdd')
order by instance_number, snap_id
/

---eg: @get_stat_10g.sql 'parse count (hard)' 20100716 20100726

---physical read total IO requests
---physical read total bytes
---physical write total IO requests
---physical write total bytes
---以上四个指标即IOPS（requests）、IO吞吐量（bytes）

--- 查整库IO相关指标2：

select e.snap_id,
       to_char(e.startup_time, 'yyyy-mm-dd hh24:mi:ss') instance_startup_time,
       to_char(e.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') snapshot_end_time,
       b.value - a.value IO_requests,
       round((d.value - c.value)/1024/1024/1024) IO_GB
  from dba_hist_sysstat  a,
       dba_hist_sysstat  b,
       dba_hist_sysstat  c,
       dba_hist_sysstat  d,
       dba_hist_snapshot e
where a.stat_name = 'physical read total IO requests'
   and b.stat_name = 'physical read total IO requests'
   and c.stat_name = 'physical read total bytes'
   and d.stat_name = 'physical read total bytes'
   and a.snap_id = e.snap_id - 1
   and b.snap_id = e.snap_id
   and c.snap_id = e.snap_id - 1
   and d.snap_id = e.snap_id
   and e.end_interval_time BETWEEN
       TO_DATE('20141021 09:00:00', 'yyyymmdd hh24:mi:ss') AND
       TO_DATE('20141023 18:00:00', 'yyyymmdd hh24:mi:ss')
order by e.begin_interval_time;

--- 查某个IO 进程的读写指标， 例如LGWR：

SQL>  select * from dba_hist_iostat_function_name;

      DBID FUNCTION_ID FUNCTION_NAME
---------- ----------- ------------------------------
1154030563           0 RMAN
1154030563           1 DBWR
1154030563           2 LGWR
1154030563           3 ARCH
1154030563           4 XDB
1154030563           5 Streams AQ
1154030563           6 Data Pump
1154030563           7 Recovery
1154030563           8 Buffer Cache Reads
1154030563           9 Direct Reads
1154030563          10 Direct Writes
1154030563          11 Smart Scan
1154030563          12 Archive Manager
1154030563          13 Others

14 rows selected

SQL>

--- 查某个IO进程例如LGWR的IO requset和IO bytes   dba_hist_iostat_function：

select e.snap_id,      
       to_char(e.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') snapshot_end_time,
       b.SMALL_READ_MEGABYTES  - a.SMALL_READ_MEGABYTES     SMALL_READ_MEGABYTES   ,                                                                                               
       b.SMALL_WRITE_MEGABYTES - a.SMALL_WRITE_MEGABYTES    SMALL_WRITE_MEGABYTES  ,                                                                                             
       b.LARGE_READ_MEGABYTES  - a.LARGE_READ_MEGABYTES     LARGE_READ_MEGABYTES   ,                                                                                            
       b.LARGE_WRITE_MEGABYTES - a.LARGE_WRITE_MEGABYTES    LARGE_WRITE_MEGABYTES  ,                                                                                           
       b.SMALL_READ_REQS       - a.SMALL_READ_REQS          SMALL_READ_REQS        ,                                                                                          
       b.SMALL_WRITE_REQS      - a.SMALL_WRITE_REQS         SMALL_WRITE_REQS       ,                                                                                         
       b.LARGE_READ_REQS       - a.LARGE_READ_REQS          LARGE_READ_REQS        ,                                                                                        
       b.LARGE_WRITE_REQS      - a.LARGE_WRITE_REQS         LARGE_WRITE_REQS       ,                                                                                       
       b.NUMBER_OF_WAITS       - a.NUMBER_OF_WAITS          NUMBER_OF_WAITS        ,                                                                                      
       b.WAIT_TIME             - a.WAIT_TIME                WAIT_TIME                                                                       
  from dba_hist_iostat_function  a,
       dba_hist_iostat_function  b,
       dba_hist_snapshot e
where a.FUNCTION_NAME = 'LGWR'
   and b.FUNCTION_NAME = 'LGWR'
   and a.snap_id = e.snap_id - 1
   and b.snap_id = e.snap_id
   and e.end_interval_time BETWEEN
       TO_DATE('20141021 09:00:00', 'yyyymmdd hh24:mi:ss') AND
       TO_DATE('20141023 18:00:00', 'yyyymmdd hh24:mi:ss')
order by e.begin_interval_time;