Oracle iops升高查看,遇到这种IOPS超高的情况，大家如何着手解决问题

比较一下数据库记录的iops与操作系统记录的iops有无较大差异吧。

如果差异很大，说明很多io不是数据库产生的。

以下是数据库查过去时段io的sql

WITH sysstat_io AS (

select sn.begin_interval_time begin_interval_time,

sn.end_interval_time end_interval_time,

ss.stat_name stat_name,

ss.value e_value,

lag(ss.value, 1) over(partition by stat_name order by ss.snap_id) b_value

from dba_hist_sysstat ss, dba_hist_snapshot sn

where trunc(sn.begin_interval_time) >= sysdate - &rollday

and ss.snap_id = sn.snap_id

and ss.dbid = sn.dbid

and ss.instance_number = sn.instance_number

and ss.dbid = (select dbid from v$database)

and ss.instance_number = (select instance_number from v$instance)

and UPPER(ss.stat_name) in

('USER I/O WAIT TIME',

'PHYSICAL READ TOTAL BYTES',

'PHYSICAL READ TOTAL IO REQUESTS',

'PHYSICAL WRITE TOTAL BYTES',

'PHYSICAL WRITE TOTAL IO REQUESTS',

'SESSION LOGICAL READS',

'BYTES SENT VIA SQL*NET TO CLIENT',

'BYTES RECEIVED VIA SQL*NET FROM CLIENT')

)

select to_char(BEGIN_INTERVAL_TIME, 'yymmdd hh24-') ||to_char(END_INTERVAL_TIME, 'hh24') date_time,

round(SUM(CASE WHEN UPPER(STAT_NAME) IN ('PHYSICAL READ TOTAL BYTES','PHYSICAL WRITE TOTAL BYTES') THEN round((e_value - nvl(b_value, 0)))/3600/1024/1024 ELSE 0 END),1) MBPS,

round(SUM(CASE WHEN UPPER(STAT_NAME) IN ('PHYSICAL READ TOTAL IO REQUESTS','PHYSICAL WRITE TOTAL IO REQUESTS') THEN round((e_value - nvl(b_value, 0)))/3600 ELSE 0 END)) IOPS,

round(SUM(CASE WHEN UPPER(STAT_NAME) IN ('PHYSICAL READ TOTAL IO REQUESTS') THEN round((e_value - nvl(b_value, 0)))/3600 ELSE 0 END)) PHY_READ_IOPS,

round(SUM(CASE WHEN UPPER(STAT_NAME) IN ('SESSION LOGICAL READS') THEN round((e_value - nvl(b_value, 0)))/3600 ELSE 0 END)) LOG_READ_IOPS,

round(SUM(CASE WHEN UPPER(STAT_NAME)='USER I/O WAIT TIME' THEN round((e_value - nvl(b_value, 0))) ELSE 0 END)*1000/1000000/3600,2) IOWAIT,

round(SUM(CASE WHEN UPPER(STAT_NAME)='BYTES RECEIVED VIA SQL*NET FROM CLIENT' THEN (e_value - nvl(b_value, 0))/3600/1024/1024 ELSE 0 END)) NET_RECV,

round(SUM(CASE WHEN UPPER(STAT_NAME)='BYTES SENT VIA SQL*NET TO CLIENT' THEN (e_value - nvl(b_value, 0))/3600/1024/1024 ELSE 0 END)) NET_SENT

from sysstat_io

where  (e_value - nvl(b_value, 0)) > 0   and nvl(b_value, 0) > 0

GROUP BY to_char(BEGIN_INTERVAL_TIME, 'yymmdd hh24-')||to_char(END_INTERVAL_TIME, 'hh24')