Re: IOPS vs Physica Block Reads statistics
- From: Sidney <huanshengchen@xxxxxxxxx>
- To: "exriscer@xxxxxxxxx" <exriscer@xxxxxxxxx>
- Date: Tue, 29 Jul 2014 19:57:16 +0800
is it due to consistent read building? check the undo related statistics.
--
Sidney
> 在 2014年7月29日，下午2:38，Ls Cheng <exriscer@xxxxxxxxx> 写道：
>
> Hi
>
> I am talking abut IOPS is higher than the number of blocks read.
>
> It looks strange because usually to read a 8KB block 1 I/O should be enough
> but it seems that it requieres many IOPS to read a block and that is why my
> confusion.
>
>
> Regards
>
>
>
>> On Tue, Jul 29, 2014 at 7:48 AM, louis liu <ylouis83@xxxxxxxxx> wrote:
>> 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@xxxxxxxxx>:
>>
>>> 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@xxxxxxxxx> 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@xxxxxxxxx>:
>>>>
>>>>> 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@xxxxxxxxx
>>>> Personal Blog: http://www.vmcd.org
>>
>>
>>
>>
>> --
>> Phone: +86 18666668061
>> Email & Gtalk: ylouis83@xxxxxxxxx
>> Personal Blog: http://www.vmcd.org
>
Other related posts:
