awr报告中的参数:物理读、逻辑读
awr报告中的参数都可以从数据库中获取,比如说load profile中的物理读
Logical read (blocks):逻辑读块数,对应v$statname 中的session logical reads
Physical read (blocks):物理读块的块数,对应v$statname 中的physical reads
Physical write (blocks):物理写块的块数,对应v$statname 中的physical writes
Read IO requests:物理读请求次数,对应v$statname 中的physical read IO requests
Write IO requests:物理写请求次数,对应v$statname 中的physical write IO requests
Read IO (MB):物理读,对应v$statname 中的physical read bytes
Write IO (MB):物理写,对应v$statname 中的physical write bytes
这些参数的意思很清楚,但是在某些情况下,不太可能生成awr报告来完成需求。
比如,客户需要找到最高物理读的时间段和物理读是多少。把每个时段的awr都生成一次然后做对比就比较low了,所以还是要查询数据库中的视图。
--查找某时段的逻辑读
set lin 200
col begin_date for a30
col end_date for a30
select * from
(select instance_number,
to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') begin_date,
to_char(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') end_date,
nvl(round((a.value - lag(a.value, 1) over(order by a.snap_id)) / 30 / 60 /1024/1024,2),-1) iops_mb --30mins,60seconds,1024*1024 mb。-1代表value为null
from (select snap_id, value
from dba_hist_sysstat
where stat_name = 'physical read bytes' --代表Read IO
and instance_number = 1) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.snap_id between 29250 and 29280
and instance_number=1
-- order by iops_mb desc)
order by end_interval_time desc)
where rownum<=20;
...
1 2018-03-06 11:00:02 2018-03-06 11:30:07 21.06
1 2018-03-06 10:30:19 2018-03-06 11:00:02 .65
1 2018-03-06 10:00:09 2018-03-06 10:30:19 6.4
1 2018-03-06 09:30:04 2018-03-06 10:00:09 .67
1 2018-03-06 09:00:23 2018-03-06 09:30:04 9.81
...
其中10点的时候逻辑读为0.67mb,跟awr报告中的0.7mb很接近了。
但是为什么是不相等呢?
原因很简单,我在sql里写的是 30 / 60,也就是把那段时间的snap当成整30min的snap,其实30.08 (mins),这就是误差所在
然后回答我们开始的问题,查看数据库的最高物理读的时间段
set lin 200
col begin_date for a30
col end_date for a30
select * from
(select instance_number,
to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') begin_date,
to_char(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') end_date,
nvl(round((a.value - lag(a.value, 1) over(order by a.snap_id)) / 30 / 60 /1024/1024,2),-1) iops_mb --30mins,60seconds,1024*1024 mb。-1代表value为null
from (select snap_id, value
from dba_hist_sysstat
where stat_name = 'physical write bytes'
and instance_number = 1) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and instance_number=1
order by iops_mb desc)
where rownum<=20;
INSTANCE_NUMBER BEGIN_DATE END_DATE IOPS_MB
--------------- ------------------------------ ------------------------------ ----------
1 2018-03-01 06:00:14 2018-03-01 06:30:02 47.24
1 2018-03-01 04:00:25 2018-03-01 04:30:38 47.15
1 2018-02-05 05:30:48 2018-02-05 06:00:21 46.34
1 2018-03-06 04:00:05 2018-03-06 04:30:28 46.33
1 2018-02-21 04:00:02 2018-02-21 04:30:27 46.3
1 2018-02-27 04:00:03 2018-02-27 04:30:14 45.66
1 2018-03-01 03:30:06 2018-03-01 04:00:25 45.66
1 2018-03-08 04:00:03 2018-03-08 04:30:20 45.63
1 2018-02-27 06:00:27 2018-02-27 06:30:44 45.36
1 2018-02-26 03:30:41 2018-02-26 04:00:01 45.3
1 2018-02-07 05:30:08 2018-02-07 06:00:11 45.14
很明显3月1日5点半到6点的物理读是最高的,约47mb每秒。
还应注意的是,库的snap生成时间默认是1小时,我这个库其实是半小时的,所以我用的是 / 30 / 60 ,一般情况下是/ 60/ 60
--snap为60min生成时,查找库的top逻辑读
set lin 200
col begin_date for a30
col end_date for a30
select * from
(select instance_number,
to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') begin_date,
to_char(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') end_date,
nvl(round((a.value - lag(a.value, 1) over(order by a.snap_id)) / 60/60 /1024/1024,2),-1) iops_mb --60mins,60seconds,1024*1024 mb。-1代表value为null
from (select snap_id, value
from dba_hist_sysstat
where stat_name = 'logical read bytes from cache'
and instance_number = 1) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and instance_number=1
order by iops_mb desc)
where rownum<=20;
每个实例的逻辑读物理度等都不一样,如果要计算数据库的总逻辑读,应该把两个实例的逻辑读的值相加
blocks块数不需要除以1024了
--查看awr报告中的top logical reads(blocks)
set lin 200
col begin_date for a30
col end_date for a30
select * from
(select instance_number,
to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') begin_date,
to_char(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') end_date,
nvl(round((a.value - lag(a.value, 1) over(order by a.snap_id)) / 60 / 60 ,2),-1) iops_mb --30mins,60seconds,。-1代表value为null
from (select snap_id, value
from dba_hist_sysstat
where stat_name = 'session logical reads'
and instance_number = 1) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and instance_number=1
order by iops_mb desc)
where rownum<=10;
在11.2.0.3以前,awr报告中信息没有那么清晰,不过跟视图中的描述倒是很一致