Oracle数据库IO问题分析利器（一）

 工欲善其事，必先利其器。

很多时候数据库性能问题的分析优化，到最后都变成了如何定位或者解决IO问题。诚然，衡量一个数据库的IO子系统效率的高低，在OS层面、存储层面都有很多命令和监控工具可以看到相关指标，在数据库层面也有AWR可以分析对比，更有一些开源或商业的DB图形界面的监控工具也能轻松实现此目的。

但是，做为一个有态度、有信仰滴技术人员，当你分析紧急生产问题，且面临一个无工具和图形界面可以使用的环境时，如何能够通过命令行的方式迅速定位问题根源并解决呢？如何能够在老板面前装逼成为一名救火英雄，并展现一个技术高手的本色呢？即便您不是一个愿意“装”的人，那么去弄懂Oracle本身所采集数据，有效地加以利用，也更有利于去理解其原理，从本源去定位和分析问题。

本文就如何从数据库层面去抓取和分析这些指标给出一些简单方法，供各位读者参考。

闲话少说，步入正题，基于Oracle DB 12c版本。

 

场景：

存储IO又报警了，可能是响应缓慢，也可能是IOPS冲高。

那么问题来了：

到底是存储响应缓慢引起的，还是数据库IO需求增加导致的？

数据库IO需求到底有无增加，是IO次数增加，还是IO size 增加？

数据库的IO需求的增加是系统引起的，还是应用引起的？

增加的幅度对比历史如何？

。。。。。。

 

一、分析整库的IO

 

先大致看下v$statname里面都有哪些与IO相关的统计指标：

11g：

SQL> select count(1) from v$statname where upper(name) like  'PHYSICAL%';

  COUNT(1)

----------

         28

12c:

SQL> select count(1) from v$statname where upper(name) like  'PHYSICAL%';

  COUNT(1)

----------

         39

可以看到12c比11g多了11个IO相关的统计指标，比如：

physical read partialrequests

physical read snap IOrequests base

physical reads cache forsecurefile flashback block new

等。

但是在oracle 12c online doc里面还未看到对其的描述。

我们主要关注下面这几个指标：

1

physical  read IO requests

应用发起的IO的请求次数，含单块读和多块读。

2

physical  read bytes

同1，只是单位是字节。

3

physical  read total IO requests

数据库实例层面总的IO的请求次数，含单块读和多块读。不但包含应用发起的，也包含系统发起的例如备份恢复、工具调用等。

4

physical  read total bytes

同3，只是单位是字节。

5

physical  read total multi block requests

数据库实例层面总的IO的请求次数，只含多块读。

 

6

physical  reads

数据库从磁盘读的block的总数

7

physical  reads cache

数据库从磁盘读到buffer  cache的block个数

8

physical  reads direct

直接从磁盘读，bypassing  the buffer cache的block个数

。。。

physical  write *

同physical read的指标一一对应，只是一个读，一个写，此处不再赘述。

 

从上面的指标解释可以看出：

如果只分析应用的IO，只看IO requests即可，

如果要分析所有的IO，就看total IO requests，

如果只分析系统的IO，就将3-1。

如果只分析单块读，就将3-5.

。。。。。。

 

知道了都有哪些指标，然后就是取指标的值了：

使用V$SYSSTAT来查看当前的指标值（系统启动以来的累积值），

使用DBA_HIST_SYSSTAT来查看历史的指标值（将两次snapshot的值相减取deleta）。

 

with t as (
 select snap.instance_number, 
        snap.snap_id, 
        snap.end_interval_time  snap_time, 
        (stat.value - lag(stat.value,  1) over (partition by  snap.instance_number order by snap.snap_id)) value
 from dba_hist_snapshot snap, dba_hist_sysstat stat
 where stat.snap_id = snap.snap_id 
 and stat.stat_name = '&stat_name' 
 and stat.instance_number = snap.instance_number
 )
 select instance_number , snap_id, snap_time, value
 from t
 where  
 snap_time between to_date('&begin_date', 'yyyymmdd') and to_date('&end_date', 'yyyymmdd')
 order by instance_number, snap_id
 /

 

可以利用plsql developer对查询结果画图，轻易看出某段时间内的某个IO指标的变化：

 

总结几点经验，供参考：

1，要记得区分IOPS与IO throughput的不同。IO requset的次数增长，不一定必然带来IO的繁忙，还要看IO的大小的变化，也就是IO bytes的指标。

2，读写有时候会互相影响，大量的写请求，会占用IO子系统的通道，通道达到瓶颈后，反过来会影响到读请求的响应时间。

3，将逻辑读（logical reads）的指标值也拿出来，结合物理读的指标值，进而可以计算出这个数据库的buffer cache hit ratio的变化趋势。

4，所以上述统计方法可以推而广之，所有v$statname里面采集的指标都可以这样对比趋势分析，不仅限于IO指标，例如硬解析率、TPS变化情况等等。

5，对做AWR报告的会话开启一个10046 trace，你会发现更多有趣的sql，以及Oracle对AWR报告里各个指标值的计算逻辑。

 

好，我们已经知道整库的IO情况了，

那么如何进一步抽丝剥茧往下分析呢？

到底罪魁祸首是谁呢？