Re: IOPS vs Physica Block Reads statistics

From: louis liu <ylouis83_at_gmail.com>
Date: Tue, 29 Jul 2014 13:48:52 +0800
Message-ID: <CAARjh8kaWBc9CE8YGVhY5v9j4EZA8hpQezGmR-soLa7atviFTg_at_mail.gmail.com>

OK but you can't actually know what's oracle doing while long time running

physical reads

Total number of data blocks read from disk. This value can be greater than the value of "physical reads direct" plus "physical reads cache" as reads into process private buffers also included in this statistic.

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

> Hi
>
> Can you try a index range scan in a session (no scatter reads o direct
> path reads) and check for your session statistics?
>
> In fact if you look my query output you can see most session have similar
> valur for both statistics.
>
>
> Thanks!
>
>
> On Tue, Jul 29, 2014 at 3:29 AM, louis liu <ylouis83_at_gmail.com> wrote:
>
>> 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
>>
>
>

-- 
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 - 07:48:52 CEST