IOPS vs Physica Block Reads statistics
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 28 Jul 2014 12:29:27 +0200
Hi All
I am monitoring a Siebel Database and noticed that some sessions have much
higher physical read total IO requests than physical reads, it i rare
because in Siebel all queries are indexed therefore data are accessed using
index range scans, in this case physical read total IO requests should
equal to physical reads. In fact it is true for most sessions except a few
with long running queries (over 2, 3 hours) which shows this behaviour:
select sid, iops, preads, iops - preads diff_iops
from (select sid,
max(case
when name = 'physical read total IO requests' then value
else null
end) IOPS,
max(case
when name = 'physical reads' then value
else null
end) PREADS
from (select a.sid, name, value
from v$sesstat a, v$statname b, v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and b.name in ('physical read total IO requests',
'physical reads')
and c.username = 'LDAPUSER'
)
group by sid)
where IOPS != PREADS
order by 1;
SID IOPS PREADS DIFF_IOPS
------ ---------- ---------- ----------
481 21961 21965 -4
514 16797 16801 -4
542 54678 54719 -41
550 9669 9673 -4
552 2672 2673 -1
561 4945 4949 -4
588 77506 77614 -108
591 7963 7967 -4
595 25758 25783 -25
616 2907 2911 -4
653 13490 13494 -4
* 672 1682079 1792498 -110419*
689 208179 208199 -20
711 4132 4136 -4
Session 672 has a long running query, around 7200 seconds already.
Anyone know what ca cause such behaviour? It is running Siebel 7.8 in
Oracle 10.2.0.3 in AIX.
Thanks
Other related posts:
