Re: IOPS vs Physica Block Reads statistics

From: louis liu <ylouis83_at_gmail.com>
Date: Tue, 29 Jul 2014 09:29:53 +0800
Message-ID: <CAARjh8mkJJaKxApAHRV+Lud_f8YUEc1Y-=O-1W+_yVKu-fsB2Q_at_mail.gmail.com>



I checked these two statistics and I don't think physical read total IO requests should equal to physical reads in your case

2014-07-28 18:29 GMT+08:00 Ls Cheng <exriscer_at_gmail.com>:

> 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
>
>

-- 
Phone: +86 18666668061
Email & Gtalk:  ylouis83_at_gmail.com
Personal Blog: http://www.vmcd.org

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 29 2014 - 03:29:53 CEST

Original text of this message